C # export dataset to excel

C # export dataset to excel

Cn = new adodb. connection
Str = "provider = microsoft. jet. oledb.4.0; data source =" & me. ofdselectexcel. filename & "; extended properties =" "excel 8.0; hdr = yes """
Cn. open (str)
'Get all sheet names
Cbsheet. properties. items. clear ()
Dim rs1 as new adodb. recordset
Rs1 = cn. openschema (adodb. schemaenum. adschematables)
While not rs1.eof
Cbsheet. properties. items. add (rs1.fields ("table_name"). value)
Rs1.movenext ()
End while
Cn. close ()

 

//// Obtain the sheet Name of the selected excel file.

An error occurred because your sheet name in excel was changed. The default value is sheet1 $

Let's take a look at the complete dataset export code to excel.

/// <Summary>
/// Export the datatable data to excel.
/// </Summary>
/// <Param name = "dt"> datatable </param>
/// <Param name = "xlsfiledir"> export the excel file storage directory (absolute path, followed by "") </param>
/// <Param name = "namelist"> name of a column in the datatable table </param>
/// <Param name = "strtitle"> title of an excel table </param>
/// <Returns> excel file name </returns>
Public static string exportdatatoexcel (system. data. datatable dt, string xlsfiledir, hashtable namelist, string strtitle)
{
If (dt = null) return "";

Microsoft. office. interop. excel. applicationclass excel = new microsoft. office. interop. excel. applicationclass ();
Microsoft. office. interop. excel. workbooks = excel. workbooks;
Microsoft. office. interop. excel. workbook = workbooks. add (microsoft. office. interop. excel. xlwbatemplate. xlwbatworksheet );
Microsoft. office. interop. excel. worksheet = (microsoft. office. interop. excel. worksheet) workbook. worksheets [1];

Int titlerowscount = 0;
If (strtitle! = Null & strtitle. trim ()! = "")
{
Titlerowscount = 1;
Excel. get_range (excel. cells [1, 1], excel. cells [1, dt. columns. count]). font. bold = true;
Excel. get_range (excel. cells [1, 1], excel. cells [1, dt. columns. count]). font. size = 16;
Excel. get_range (excel. cells [1, 1], excel. cells [1, dt. columns. count]). mergecells = true;
Worksheet. cells [1, 1] = strtitle;
}
If (! System. io. directory. exists (xlsfiledir ))
{
System. io. directory. createdirectory (xlsfiledir );
}
String strfilename = datetime. now. tostring ("yyyymmddhhmmssff") + ". xls ";

String tempcolumnname = "";

For (int I = 0; I <dt. rows. count; I ++)
{
For (int j = 0; j <dt. columns. count; j ++)
{
If (I = 0)
{
Tempcolumnname = dt. columns [j]. columnname. trim ();
If (namelist! = Null)
{
Idictionaryenumerator enum = namelist. getenumerator ();
While (enum. movenext ())
{
If (enum. key. tostring (). trim () = tempcolumnname)
{
Tempcolumnname = enum. value. tostring ();
}
}
}
Worksheet. cells [titlerowscount + 1, j + 1] = tempcolumnname;
}
Worksheet. cells [I + titlerowscount + 2, j + 1] = dt. rows [I] [j]. tostring ();
}
}
Excel. get_range (excel. cells [titlerowscount + 1, 1], excel. cells [titlerowscount + 1, dt. columns. count]). font. bold = true;
Excel. get_range (excel. cells [1, 1], excel. cells [titlerowscount + 1 + dt. rows. count, dt. columns. count]). horizontalalignment = xlvalign. xlvaligncenter;
Excel. get_range (excel. cells [1, 1], excel. cells [titlerowscount + 1 + dt. rows. count, dt. columns. count]). entirecolumn. autofit ();

Workbook. saved = true;
Workbook. savecopyas (xlsfiledir + strfilename );
System. runtime. interops tutorial ervices. marshal. releasecomobject (worksheet );
Worksheet = null;
System. runtime. interopservices. marshal. releasecomobject (workbook );
Workbook = null;
Workbooks. close ();
System. runtime. interopservices. marshal. releasecomobject (workbooks );
Workbooks = null;
Excel. quit ();
System. runtime. interopservices. marshal. releasecomobject (excel );
Excel = null;
Return strfilename;
}