Using system;
Using system. Data;
Using system. Data. oledb;
Using system. Windows. forms;
Using Microsoft. Office. InterOP. Excel;
Namespace ds_excel
{
/// <Summary>
/// Abstract description of importexporttoexcel.
/// </Summary>
Public class importexporttoexcel
{
Private string strconn;
Private system. Windows. Forms. openfiledialog openfiledlg = new system. Windows. Forms. openfiledialog ();
Private system. Windows. Forms. savefiledialog savefiledlg = new system. Windows. Forms. savefiledialog ();
Public importexporttoexcel ()
{
//
// Todo: add the constructor logic here
//
This. openfiledlg. defaultext = "xls ";
This. openfiledlg. Filter = "Excel file (*. xls) | *. xls ";
This. savefiledlg. defaultext = "xls ";
This. savefiledlg. Filter = "Excel file (*. xls) | *. xls ";
}
# Region import from an Excel file to Dataset
/// <Summary>
/// Import files from Excel
/// </Summary>
/// <Param name = "strexcelfilename"> Excel file name </param>
/// <Returns> return dataset </returns>
Public dataset importfromexcel ()
{
Dataset DS = new dataset ();
If (openfiledlg. showdialog () = system. Windows. Forms. dialogresult. OK)
DS = doimport (openfiledlg. filename );
Else DS = NULL;
Return Ds;
}
Public dataset importfromexcel (string strfilename)
{
Dataset DS = new dataset ();
DS = doimport (strfilename );
Return Ds;
}
private dataset doimport (string strfilename)
{< br> If (strfilename = "") return NULL;
strconn = "provider = Microsoft. jet. oledb.4.0; "+
" Data Source = "+ strfilename +"; "+
" extended properties = Excel 8.0 ;";
oledbdataadapter excelda = new oledbdataadapter ("select * from [sheet1 $]", strconn);
dataset excelds = new dataset ();
try
{< br> excelda. fill (excelds, "excelinfo");
}< br> catch (exception ERR)
{< br> system. console. writeline (err. tostring ();
}< br> return excelds;
}< br> # endregion
# Region from dataset to excel
/** // <Summary>
/// Export the specified Excel File
/// </Summary>
/// <Param name = "ds"> dataset to be exported </param>
/// <Param name = "strexcelfilename"> name of the Excel file to be exported </param>
Public void exporttoexcel (Dataset ds, string strexcelfilename)
{
If (Ds. Tables. Count = 0 | strexcelfilename = "") return;
Doexport (DS, strexcelfilename );
}
/** // <Summary>
/// Export the selected Excel File
/// </Summary>
/// <Param name = "ds"> dataset </param>
Public void exporttoexcel (Dataset DS)
{
If (savefiledlg. showdialog () = system. Windows. Forms. dialogresult. OK)
Doexport (DS, savefiledlg. filename );
}
/** // <Summary>
/// Execute Export
/// </Summary>
/// <Param name = "ds"> dataset to be exported </param>
/// <Param name = "strexcelfilename"> name of the file to be exported </param>
Private void doexport (Dataset ds, string strexcelfilename)
{
Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();
// Excel. Workbook OBJ = new excel. workbookclass ();
// Obj. saveas ("C: \ zn.xls", Excel. xlfileformat. xlexcel9795, null, null, false, false, Excel. xlsaveasaccessmode. xlnochange, null, null );
Int rowindex = 1;
Int colindex = 0;
excel. application. workbooks. add (true);
system. data. datatable table = Ds. tables [0];
foreach (datacolumn Col in table. columns)
{< br> colindex ++;
excel. cells [1, colindex] = Col. columnname;
}
foreach (datarow row in table. rows)
{< br> rowindex ++;
colindex = 0;
foreach (datacolumn Col in table. columns)
{< br> colindex ++;
excel. cells [rowindex, colindex] = row [col. columnname]. tostring ();
}< BR >}< br> excel. visible = false;
// excel. sheet [0] = "Sss";
excel. activeworkbook. _ saveas (strexcelfilename, Microsoft. office. interOP. excel. xlfileformat. xlexcel9795, null, null, false, false, Microsoft. office. interOP. excel. xlsaveasaccessmode. xlnochange, null);
MessageBox. show ("the Excel table has been exported to" + strexcelfilename, "prompt");
// wkbnew. saveas strbookname
// Excel. Save (strexcelfilename );
Excel. Quit ();
Excel = NULL;
GC. Collect (); // garbage collection
}
# Endregion
}
}