/// <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 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); 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> /// 读取所有工作表名 /// </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++) { 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); //在添加的图片上加文字 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(); }
原标题：csharp: Export DataSet into Excel and import all the Excel sheets to DataSet
关键词：DataSet
我知道了 