csharp: Export DataSet into Excel and import all the Excel sheets to DataSet

DataTable employeeTable = new DataTable("Employee"); employeeTable.Columns.Add("Employee ID"); employeeTable.Columns.Add("Employee Name"); employeeTable.Rows.Add("1", "涂聚文"); employeeTable.Rows.Add("2", "geovindu"); employeeTable.Rows.Add("3", "李蘢怡"); employeeTable.Rows.Add("4", "ноппчц"); employeeTable.Rows.Add("5", "ニヌネハヒフキカォноппчц"); //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组件接口 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, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss); excelWorkBook.Close(false, miss, miss); //excelWorkBook.Save(); books.Close(); excelApp.Quit(); }

/// summary 

 /// EXCEL表的所有工作表导入到DataSet

 /// 涂聚文 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);


 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 

 /// 读取所有工作表名

 /// /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 

 /// 添加图片

 /// 涂聚文

 /// /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++)

 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);

 //在添加的图片上加文字

 worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "涂聚文写上", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse,Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);

 xlApp.Visible = true;

 workbook.SaveAs(FilePath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);

 workbook.Close(false, miss, miss);

 //excelWorkBook.Save();

 workbooks.Close();

 xlApp.Quit();

 }

       平时的数据分析中，总是需要对excel表格中不同的数据进行汇总、合并、切分，因为excel是面向对象的图形操作，简单，但是当进行大规模的数据汇总、合并，切分处理的时候，就比较劳心劳力，且因为数据操作过程中，人工操作的高度介入导致数据处理过程中容易出错，刚好最近在各种深度学习pandas，今天就专门来深度学习下pandas中的      1、excel读取函数   read_
geovindu 读者是,读之者,者之读.一沙一世界! to be is to do举世皆清我独浊,众人皆醒我独醉.俺是农民工,程序员.