Csharp: Export DataSet into Excel and import all the Excel sheets to DataSet, csharpdataset

Csharp: Export DataSet into Excel and import all the Excel sheets to DataSet, csharpdataset

/// <Summary> /// Export DataSet into Excel /// </summary> /// <param name = "sender"> </param> /// <param name = "e"> </param> private void Form3_Load (object sender, eventArgs e) {// Create an Emplyee DataTable employeeTable = new DataTable ("Employee"); employeeTable. columns. add ("Employee ID"); employeeTable. columns. add ("Employee Name"); employeeTable. rows. add ("1", "tu juwen"); employeeTable. rows. add ("2", "geovindu"); employeeTable. rows. add ("3", "Li Xiaoyi"); employeeTable. rows. add ("4", "zookeeper"); employeeTable. rows. add ("5", "zookeeper "); // Create a Department Table DataTable departmentTable = new DataTable ("Department"); departmentTable. columns. add ("Department ID"); departmentTable. columns. add ("Department Name"); departmentTable. rows. add ("1", "IT"); departmentTable. rows. add ("2", "HR"); departmentTable. rows. add ("3", "Finance"); // Create a DataSet with the existing DataTables DataSet ds = new DataSet ("Organization"); ds. tables. add (employeeTable); ds. tables. add (departmentTable); ExportDataSetToExcel (ds );} /// <summary> // This method takes DataSet as input paramenter and it exports the same to excel /// </summary> /// <param name = "ds"> </param> private void ExportDataSetToExcel (DataSet ds) {// Creae an Excel application instance // EXCEL Component Interface System. reflection. missing miss = System. reflection. missing. value; Excel. application excelApp = new Excel. application (); excelApp. application. workbooks. add (true); string timeMark = DateTime. now. toString ("yyyyMMddHHmmss"); string FilePath = Path. combine (Environment. getFolderPath (Environment. specialFolder. desktop), "geovindu" + timeMark + ". xlsx "); // Create an Excel workbook instance and open it from the predefined location // Excel. workbook excelWorkBook = excelApp. workbooks. open (FilePath); Excel. workbooks books = (Excel. workbooks) excelApp. workbooks; Excel. workbook excelWorkBook = (Excel. workbook) books. add (miss); foreach (DataTable table in ds. tables) {// Add a new worksheet to workbook with the Datatable name Excel. worksheet excelWorkSheet = excelWorkBook. sheets. add (); excelWorkSheet. name = table. tableName; for (int I = 1; I <table. columns. count + 1; I ++) {excelWorkSheet. cells [1, I] = table. columns [I-1]. columnName ;}for (int j = 0; j <table. rows. count; j ++) {for (int k = 0; k <table. columns. count; k ++) {excelWorkSheet. cells [j + 2, k + 1] = table. rows [j]. itemArray [k]. toString () ;}} excelWorkBook. saveAs (FilePath, miss, Excel. xlSaveAsAccessMode. xlNoChange, System. text. encoding. UTF8, miss, miss); excelWorkBook. close (false, miss, miss); // excelWorkBook. save (); books. close (); excelApp. quit ();}

　　

/// <Summary> /// import all worksheets of the EXCEL table to DataSet /// tu juwen Microsoft. ACE. OLEDB.12.0 // Geovin Du /// </summary> /// <param name = "fileName"> </param> /// <returns> </returns> static DataSet importExcelParse (string fileName) {string connectionString = string. format ("provider = Microsoft. jet. OLEDB.4.0; data source = {0}; Extended Properties = Excel 8.0; ", fileName); DataSet data = new DataSet (); foreach (var sheetName in GetExcelSheetNames (connectionString )) {using (OleDbConnection con = new OleDbConnection (connectionString) {var dataTable = new DataTable (); string query = string. format ("SELECT * FROM [{0}]", sheetName); con. open (); OleDbDataAdapter adapter = new OleDbDataAdapter (query, con); adapter. fill (dataTable); data. tables. add (dataTable) ;}} return data ;} /// <summary> /// read all worksheet names /// </summary> /// <param name = "connectionString"> </param> /// <returns> </returns> static string [] GetExcelSheetNames (string connectionString) {OleDbConnection con = null; DataTable dt = null; con = new OleDbConnection (connectionString); con. open (); dt = con. getOleDbSchemaTable (OleDbSchemaGuid. tables, null); if (dt = null) {return null;} String [] excelSheetNames = new String [dt. rows. count]; int I = 0; foreach (DataRow row in dt. rows) {excelSheetNames [I] = row ["TABLE_NAME"]. toString (); I ++;} return excelSheetNames ;}

　　

/// <Summary> /// add an image /// tu juwen /// </summary> /// <param name = "dt"> </param> protected void exportExcelImg (System. data. dataTable dt) {if (dt = null | dt. rows. count = 0) return; Microsoft. office. interop. excel. application xlApp = new Microsoft. office. interop. excel. application (); if (xlApp = null) {return;} xlApp. application. workbooks. add (true); string timeMark = DateTime. now. toString ("yyyyMMddHHmmss"); string FilePath = Path. combine (Environment. getFolderPath (Environment. specialFolder. desktop), "geovindu" + timeMark + ". xlsx "); System. globalization. cultureInfo CurrentCI = System. threading. thread. currentThread. currentCulture; System. threading. thread. currentThread. currentCulture = new System. globalization. cultureInfo ("en-US"); Microsoft. office. interop. excel. workbooks workbooks = xlApp. 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]; Microsoft. office. interop. excel. range range; System. reflection. missing miss = System. reflection. missing. value; long totalCount = dt. rows. count; long rowRead = 0; float percent = 0; for (int I = 0; I <dt. columns. count; I ++) {worksheet. cells [1, I + 1] = dt. columns [I]. columnName; range = (Microsoft. office. interop. excel. range) worksheet. cells [1, I + 1]; range. interior. colorIndex = 15 ;}for (int r = 0; r <dt. rows. count; r ++) {for (int I = 0; I <dt. columns. count; I ++) {try {worksheet. cells [r + 2, I + 1] = dt. rows [r] [I]. toString ();} catch {worksheet. cells [r + 2, I + 1] = dt. rows [r] [I]. toString (). replace ("=", "") ;}} rowRead ++; percent = (float) (100 * rowRead)/totalCount;} string strimg = Application. startupPath + @ "/IMG_6851.JPG"; worksheet. shapes. addPicture (strimg, Microsoft. office. core. msoTriState. msoFalse, Microsoft. office. core. msoTriState. msoCTrue, 100,200,200,300); // Add the text worksheet to the added image. shapes. addTextEffect (Microsoft. office. core. msoPresetTextEffect. msoTextEffect1, "tu juwen write", "Red", 15, Microsoft. office. core. msoTriState. msoFalse, Microsoft. office. core. msoTriState. msoTrue, 150,200); xlApp. visible = true; workbook. saveAs (FilePath, miss, Microsoft. office. interop. excel. xlSaveAsAccessMode. xlNoChange, System. text. encoding. UTF8, miss, miss); workbook. close (false, miss, miss); // excelWorkBook. save (); workbooks. close (); xlApp. quit ();}