I am using excel component for the development.
The purpose is to connect to excel through the odbc
connection string. Then through the connection extract
data into a dataset and then save the data into a client
machine (Intranet) as excel page using the excel
component (using Excel = Microsoft.Office.Interop.Excel;)
in the code.
Please help me. I have wasted so many days on this.
I have 2 problems here
1. When I establish connection to the excel page I am
getting hell of problems like unspecified errors and dies
public void btnConnect_Click(object
sender, System.EventArgs e)
{
if
(this.ddlFileName.SelectedValue != "None")
{
try
{
sFile =
Server.MapPath(Request.ApplicationPath) + "\\Data\\" +
this.ddlFileName.SelectedItem;
DataTable dt =
new DataTable("TablesList");
if
(this.ddlFileName.SelectedValue.EndsWith(".xml"))
{
dsTables.ReadXmlSchema(sFile);
dsTables.ReadXml(sFile);
for (int
iTab = 0;iTab < dsTables.Tables.Count; iTab++)
{
if (lbxTableList.Items.FindByText(dsTables.Tables
[iTab].TableName.ToString()) == null)
{
this.lbxTableList.Items.Add(dsTables.Tables
[iTab].TableName.ToString());
}
}
strFFlag
= "xml";
}
else if
(this.ddlFileName.SelectedValue.EndsWith(".xls"))
{
<<Dies here: unspecified error>>
string[] SheetName =
LoadSchemaFromFile(sFile);
for (int
i=0;i<SheetName.Length;i++)
{
this.lbxTableList.Items.Add(SheetName[i].ToString
());
}
strFFlag
= "xls";
}
else if
(this.ddlFileName.SelectedValue.EndsWith(".mdb"))
{
string[]
SheetName = LoadSchemaFromFile(sFile);
for (int
i=0;i< SheetName.Length;i++)
{
this.lbxTableList.Items.Add(SheetName[i].ToString
());
}
strFFlag
= "mdb";
}
else
{
this.Msg.Text = "Error getting tables list :-(";
return;
}
if (Cache
["strFFlagCc"] != null)
{
Cache.Remove("strFFlagCc");
}
Cache.Add
("strFFlagCc", strFFlag,null,DateTime.Now.AddSeconds(90),
TimeSpan.Zero ,
System.Web.Caching.CacheItemPriority.High, null);
}
catch
(ApplicationException sqlExc)
{
this.Msg.Text =
sqlExc.ToString() + "\n" + "SQL Exception Error!";
return;
}
}
else
{
this.Msg.Text = "Please
select source for data access";
return;
}
}
private string[] LoadSchemaFromFile
(string fileName)
{
string[] SheetNames = null;
OleDbConnection conn = new
OleDbConnection();
conn.ConnectionString
= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + "; Jet OLEDB:Engine
Type=5;";//this.ReturnConnection(fileName);
try
{
conn.Open();
DataTable SchemaTable =
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables ,new
object[]{null});
//conn.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables,new object[] {null, null,
null, "TABLE"});
if
(SchemaTable.Rows.Count > 0)
{
SheetNames = new
string[SchemaTable.Rows.Count];
int i=0;
foreach(DataRow
TmpRow in SchemaTable.Rows)
{
SheetNames
[i] = TmpRow["TABLE_NAME"].ToString(); // string.Format
("{0}",myRow["TABLE_NAME"]);
i++;
}
}
conn.Close();
}
catch
(System.Data.OleDb.OleDbException e)
{
Msg.Text = "Error code= "
+ e.ErrorCode.ToString("X")+"\nError Description=" +
e.Message +"\n Error Source=" + e.InnerException + "\n
Error Method=" + e.TargetSite + "\n =" +
e.Errors;//,TitleText);
Trace.WriteLine
(e.StackTrace);
}
finally
{
if ( conn!= null &&
conn.State == ConnectionState.Open)
{
conn.Close ();
conn.Dispose();
}
}
return SheetNames;
}
private OleDbConnection ReturnConnection
(string fileName)
{
//if(checkBox2.Checked) //First
row as column name
//{
if (fileName.EndsWith(".xls"))
{
return new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" +
fileName + ";" +
"Extended
Properties=\"Excel 8.0;\"");// Jet OLEDB:Engine Type=5;"
}
else if (fileName.EndsWith
(".mdb"))
{
//"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\\BIRoot\\WebClient\\Data\\Northwind.mdb"
return new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + "; Jet OLEDB:Engine Type=5;");
}
else
{
return new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" +
fileName + "; Jet OLEDB:Engine Type=5;");
}
//}
//else
//{
// return new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;" +
// "Data Source=" +
fileName + "; Jet OLEDB:Engine Type=5;"+
// "Extended
Properties=\"Excel 8.0;HDR=NO\"");
//}
}
2. I have data from a dataset and want to pump into an
excel sheet and the component activates some time and
works but doesn't work some time. (from some application
projects it works some doesn't).
I am using windows XP for development.
I have already gone to START>>SETTINGS>CONTROL
PANEL>>ADMIN TOOLS>>COMPONENT SERVICES>>
COMPONENT SERVICES MENU>>MY COMPUTER>> DCOM
CONFIG>>MICROSOFT EXCEL APPLICATION>>SECURITY TAB>>ADDED
ALL THREE (lAUNCH,ACCESS,CONFIGURATION PERMISSIONS)
ADDED MY LOGIN AND .NET PERMITTED TO ACCESS
still when I tried to open the application it just hangs
in there!!!
Below is a templated excel file creation from dataset.
Below code worked 3 days ago now it doesn't work.
Hangs at the code line of creation of component instance
public void btnSaveXl_Click(object
sender, System.EventArgs e)
{
<<hangs here>> Excel.Application oExcel
= new Excel.ApplicationClass();
string sFileXl = Server.MapPath
(Request.ApplicationPath) + "\\Data\\MyExcel2.xls";
string sTemplate = Server.MapPath
(Request.ApplicationPath) + "\\Data\\MyTemplate.xls";
oExcel.Visible = false;
oExcel.DisplayAlerts = false;
//Start a new workbook
Excel.Workbooks oBooks =
oExcel.Workbooks;
//Change the language culture
info to match the office culture else you get error
oBooks.Open
(sTemplate,0,true,5,"","",true,Excel.XlPlatform.xl Windows,
"\t",false,false,0,true,false,false);
//Load colorful template with
chart
Excel.Workbook oBook =
oBooks.get_Item(1);
Excel.Sheets oSheets =
oBook.Worksheets;
Excel.Worksheet oSheet =
(Excel.Worksheet)(oSheets.get_Item(1));
oSheet.Name = "First Sheet";
Excel.Range oCells = oSheet.Cells;
DataTable dt = ds.Tables[0];
DumpData(dt, oCells); //Fill in
the data
oSheet.SaveAs(sFileXl,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value,Missing.Value,
Missing.Value,
Missing.Value, Missing.Value, Missing.Value); //Save in
a temporary file
//oBook.Close
(true,"MyExcel.xls",Missing.Value);
//Quit Excel and thoroughly
deallocate everything
oExcel.Quit();
System.GC.Collect();
Response.Redirect
(sFileXl); //Send the user to the file
}
//Outputs a DataTable to an Excel
Worksheet
private string DumpData
(System.Data.DataTable dt, Excel.Range oCells)
{
int iRow;
int iCol;
//Output Column Headers
for (iCol=0; iCol <
dt.Columns.Count;iCol++)
{
oCells.Cells.set_Item(2,
iCol + 1,dt.Columns[iCol].ToString());
}
//Output Data
for (iRow=0;iRow <
dt.Rows.Count;iRow++)
{
DataRow row;
row = dt.Rows[iRow];
for (iCol = 0; iCol <
dt.Columns.Count; iCol++)
{
DataColumn col;
col = dt.Columns
[iCol];
{
oCells.Cells.set_Item(iRow + 3, iCol + 1,row
[col].ToString());
Response.Write(oCells.Cells.get_Item(iRow + 3,
iCol + 1).ToString() + "\t");
}
}
}
return "";
//byte[] info = new UTF8Encoding
(true).GetBytes(value);
//fs.Write(info, 0, info.Length);
}
