DataSet exported to excel in C #

DataSet exported to excel in C #

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 the name of all sheet
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 ()

Above is to get the sheet name of the selected Excel

The error is because your Excel sheet name has been changed, and the default is sheet1$

All right, here we go. Let's look at a complete dataset to export to Excel code.

<summary>
Export the data from the DataTable to Excel.
</summary>
<param name= "DT" >datatable</param>
<param name= "Xlsfiledir" > Exported Excel File storage directory (absolute path, last with "") </param>
<param name= "NameList" >datatable the Chinese counterpart of the list of names </param>
<param name= "strtitle" >excel table title </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 workbooks = excel.workbooks;
Microsoft.office.interop.excel.workbook Workbook = Workbooks.Add ( Microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet);
microsoft.office.interop.excel.worksheet 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;
}