简单DataTable导出Excel

合集下载

C#实现将DataTable内容输出到Excel表格的方法

C#实现将DataTable内容输出到Excel表格的⽅法本⽂实例讲述了C#实现将DataTable内容输出到Excel表格的⽅法。

分享给⼤家供⼤家参考。

具体如下：1.关于本⽂本⽂描述了⼀个函数（SaveToExcel），该函数可以将DataTable数据内的数据输出到Excel表格中2.相关说明1）本⽂中使⽤这个函数将⼀个DataTable中的内容输出到路径名为addr的⽬录下：复制代码代码如下:public void SaveToExcel(string addr, System.Data.DataTable dt)2）这个函数需要引⽤Microsoft.Office.Interop.Excel命名空间需要添加引⽤：Microsoft Excel 14.0 Object Library并在程序的最上⽅添加：复制代码代码如下:using Excel = Microsoft.Office.Interop.Excel;3）⽂程序在窗体的保存按钮（btnSave）单击事件中调⽤了函数SaveToExcel：//按钮：将数据保存到Excel表格中private void btnSave_Click(object sender, EventArgs e){SaveToExcel(txtAddress.Text, (DataTable)dgvTable4Output.DataSource);}3.程序界⾯4.程序代码Load函数部分：设置数据源private void FormMain_Load(object sender, EventArgs e){System.Data.DataTable dt;string s1 = "甲⼄丙丁戊⼰庚⾟壬癸";string s2 = "⼦丑寅卯⾠巳午未申⾣戌亥";dt = new System.Data.DataTable("天⼲地⽀表");//⾏表格dt.Columns.Add("列0"); dt.Columns.Add("列1");dt.Columns.Add("列2"); dt.Columns.Add("列3");dt.Columns.Add("列4"); dt.Columns.Add("列5");dt.Columns.Add("列6"); dt.Columns.Add("列7");dt.Columns.Add("列8"); dt.Columns.Add("列9");dt.Rows.Add("", "", "", "", "", "", "", "", "", "");dt.Rows.Add("", "", "", "", "", "", "", "", "", "");dt.Rows.Add("", "", "", "", "", "", "", "", "", "");dt.Rows.Add("", "", "", "", "", "", "", "", "", "");dt.Rows.Add("", "", "", "", "", "", "", "", "", "");dt.Rows.Add("", "", "", "", "", "", "", "", "", "");//⽣成⼲⽀表int x = 0, y = 0;for (int i = 0; i < 6; i++){for (int j = 0; j < 10; j++){dt.Rows[i][j] = s1[x].ToString() + s2[y].ToString();x++; x = (x < s1.Length ? x : 0);y++; y = (y < s2.Length ? y : 0);}}dgvTable4Output.DataSource = dt;btnSave单击事件部分：调⽤函数，将数据源存储到Excel表格中//按钮：将数据保存到Excel表格中private void btnSave_Click(object sender, EventArgs e){SaveToExcel(txtAddress.Text, (DataTable)dgvTable4Output.DataSource);}函数SaveToExcel部分：/// <summary>/// 将数据表保存到Excel表格中/// </summary>/// <param name="addr">Excel表格存放地址（程序运⾏⽬录后⾯的部分）</param> /// <param name="dt">要输出的DataTable</param>public void SaveToExcel(string addr, System.Data.DataTable dt){//0.注意：// * Excel中形如Cells[x][y]的写法，前⾯的数字是列，后⾯的数字是⾏!// * Excel中的⾏、列都是从1开始的，⽽不是0//1.制作⼀个新的Excel⽂档实例Excel::Application xlsApp = new Excel::Application();xlsApp.Workbooks.Add(true);/* ⽰例输⼊：需要注意Excel⾥数组以1为起始（⽽不是0）* for (int i = 1; i < 10; i++)* {* for (int j = 1; j < 10; j++)* {* xlsApp.Cells[i][j] = "-";* }* }*///2.设置Excel分页卡标题 = dt.TableName;//3.合并第⼀⾏的单元格string temp = "";if (dt.Columns.Count < 26){temp = ((char)('A' + dt.Columns.Count)).ToString();}else if (dt.Columns.Count <= 26 + 26 * 26){temp = ((char)('A' + (dt.Columns.Count - 26) / 26)).ToString()+ ((char)('A' + (dt.Columns.Count - 26) % 26)).ToString();}else throw new Exception("列数过多");Excel::Range range = xlsApp.get_Range("A1", temp + "1");range.ClearContents(); //清空要合并的区域range.MergeCells = true; //合并单元格//4.填写第⼀⾏：表名，对应DataTable的TableNamexlsApp.Cells[1][1] = dt.TableName;xlsApp.Cells[1][1] = "⿊体";xlsApp.Cells[1][1].Font.Size = 25;xlsApp.Cells[1][1].Font.Bold = true;xlsApp.Cells[1][1].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//居中xlsApp.Rows[1].RowHeight = 60; //第⼀⾏⾏⾼为60（单位：磅）//5.合并第⼆⾏单元格，⽤于书写表格⽣成⽇期range = xlsApp.get_Range("A2", temp + "2");range.ClearContents(); //清空要合并的区域range.MergeCells = true; //合并单元格//6.填写第⼆⾏：⽣成时间xlsApp.Cells[1][2] = "报表⽣成于：" + DateTime.Now.ToString();xlsApp.Cells[1][2] = "宋体";xlsApp.Cells[1][2].Font.Size = 15;//xlsApp.Cells[1][2].HorizontalAlignment = 4;//右对齐xlsApp.Cells[1][2].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//居中xlsApp.Rows[2].RowHeight = 30; //第⼀⾏⾏⾼为60（单位：磅）//7.填写各列的标题⾏xlsApp.Cells[1][3] = "序号";for (int i = 0; i < dt.Columns.Count; i++){xlsApp.Cells[i + 2][3] = dt.Columns[i].ColumnName;xlsApp.Rows[3] = "宋体";xlsApp.Rows[3].Font.Size = 15;xlsApp.Rows[3].Font.Bold = true;xlsApp.Rows[3].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//居中 //设置颜⾊range = xlsApp.get_Range("A3", temp + "3");range.Interior.ColorIndex = 33;//8.填写DataTable中的数据for (int i = 0; i < dt.Rows.Count; i++){xlsApp.Cells[1][i + 4] = i.ToString();for (int j = 0; j < dt.Columns.Count; j++){xlsApp.Cells[j + 2][i + 4] = dt.Rows[i][j];}}range = xlsApp.get_Range("A4", temp + (dt.Rows.Count + 3).ToString());range.Interior.ColorIndex = 37;range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//9.描绘边框range = xlsApp.get_Range("A1", temp + (dt.Rows.Count + 3).ToString());range.Borders.LineStyle = 1;range.Borders.Weight = 3;//10.打开制作完毕的表格//xlsApp.Visible = true;//11.保存表格到根⽬录下指定名称的⽂件中xlsApp.ActiveWorkbook.SaveAs(Application.StartupPath + "/" + addr);xlsApp.Quit();xlsApp = null;GC.Collect();}希望本⽂所述对⼤家的C#程序设计有所帮助。

react导出table数据生成excel

标题：使用React导出Table数据生成Excel表格在当今信息爆炸的时代，数据的重要性愈发凸显。

对于企业或个人而言，数据的整理、分析和展示都是至关重要的。

在Web开发中，React作为一种流行的前端开发框架，它的组件化和易用性为数据展示提供了便利。

本文将探讨如何利用React来导出Table数据并生成Excel表格，以便更加方便、清晰地展示数据。

1. React中Table数据的展示在Web开发中，常常需要在页面中展示数据表格，以便用户能够快速、直观地了解数据。

而在React中，可以使用Ant Design、Material-UI等组件库，或自行编写Table组件来展示数据。

通过简单的代码和配置，即可轻松实现数据的展示和分页，为用户提供优秀的数据体验。

2. 导出Table数据为Excel在实际开发过程中，用户可能需要将页面中的数据导出为Excel表格以便进一步处理或共享。

为了满足这一需求，可以借助第三方库如react-data-export等，通过简单的配置和代码，即可实现将Table数据导出为Excel表格的功能。

这为用户提供了极大的便利，使得数据的共享和处理更加灵活和高效。

3. 如何实现在实际开发中，要实现将React中的Table数据导出为Excel表格，首先需要安装并引入相应的第三方库。

接着在Table组件中进行相应的配置和事件绑定，以便用户在需要时进行数据导出操作。

最后在导出功能中，可以根据用户的选择将数据导出为不同格式的Excel表格，从而满足不同场景下的需求。

4. 个人观点和理解在现代化的Web开发中，数据的展示和处理是至关重要的。

而React 作为一种流行的前端框架，其组件化和易用性为数据的展示和导出提供了非常大的便利。

通过合理的配置和代码编写，我们可以轻松地实现将Table数据导出为Excel表格的功能，为用户提供更佳的数据体验和便利性。

这无疑为Web开发带来了更大的价值和用户体验。

C#如何将DataTable导出到Excel解决方案

C#如何将DataTable导出到Excel解决⽅案最近，由于公司项⽬中需要将系统内⽤户操作的所有⽇志进⾏转存备份，考虑到以后可能还需要还原，所以最后决定将⽇志数据备份到Excel中。

下⾯是我项⽬当中Excel.cs这个类的全部代码，通过这个类可以很容易地将DataTable中的数据导⼊到Excel⽅法中。

⾸先，必须要下载NPOI.dll这个程序集，类代码如下：复制代码代码如下:using System;using NPOI.HSSF;using NPOI.HPSF;using erModel;using NPOI.HSSF.Util;using erModel;using System.Collections;using System.IO;using System.Data;namespace BackupAttach{public class Excel{private HSSFWorkbook _workBook;private ISheet _wbSheet = null;private DataColumnCollection _columns = null;private int _col = 0; //total columnsprivate int _row = 0; //total rowsprivate int _sheet = 0; //total sheetsprivate int _sheetRowNum = 65536; //each sheet allow rowspublic Excel(){InstanceWorkBook();}/// <summary>/// 实例⽅法/// </summary>/// <param name="sheetRowNum">单个表单允许的最⼤⾏数</param>public Excel(int sheetRowNum){_sheetRowNum = sheetRowNum;InstanceWorkBook();}/// <summary>/// 实例⽅法/// </summary>/// <param name="columns">表头</param>public Excel(DataColumnCollection columns){_columns = columns;InstanceWorkBook();}private void InstanceWorkBook(){/////cretate WorkBook_workBook = new HSSFWorkbook();var dsi = PropertySetFactory.CreateDocumentSummaryInformation();pany = "BaiyiTimes";_workBook.DocumentSummaryInformation = dsi;////create a entry of SummaryInformationvar si = PropertySetFactory.CreateSummaryInformation();si.Subject = "Etimes Secure Document System Log Backup";_workBook.SummaryInformation = si;}private DataColumnCollection GetColumns(DataColumnCollection columns) {return columns == null || columns.Count == 0 ? _columns : columns;}private ISheet GetSheet(ISheet sheet){return sheet == null ? _wbSheet : sheet;}private void CreateHeader(ISheet sheet, DataColumnCollection columns) {_columns = GetColumns(columns);/////create row of columnvar oRow = sheet.CreateRow(0);foreach (DataColumn column in _columns){var oCell = oRow.CreateCell(_col);var style1 = _workBook.CreateCellStyle();style1.FillForegroundColor = HSSFColor.BLUE.index2;style1.FillPattern = FillPatternType.SOLID_FOREGROUND;style1.Alignment = HorizontalAlignment.CENTER;style1.VerticalAlignment = VerticalAlignment.CENTER;var font = _workBook.CreateFont();font.Color = HSSFColor.WHITE.index;style1.SetFont(font);oCell.CellStyle = style1;var name = column.ColumnName;oCell.SetCellValue(name.ToString());_col++;}///// header belong to rows_row++;}private void CreateHeader(ISheet sheet){CreateHeader(sheet, null);}public ISheet CreateSheet(){return CreateSheet(null);}public ISheet CreateSheet(DataColumnCollection columns){_wbSheet = _workBook.CreateSheet((_sheet + 1).ToString()); CreateHeader(_wbSheet, columns);_sheet++;return _wbSheet;}public void SetRowValue(DataRowCollection rows, ISheet sheet){_wbSheet = GetSheet(sheet);foreach (DataRow row in rows){SetRowValue(row);}}public void SetRowValue(DataRowCollection rows){SetRowValue(rows, null);}public void SetRowValue(DataRow row){// create a new sheetif (_row % _sheetRowNum == 0){CreateSheet();}var oRow = _wbSheet.CreateRow(_row % _sheetRowNum);var obj = string.Empty;var cell = 0;foreach (DataColumn column in _columns){obj = row[column.ColumnName].ToString();oRow.CreateCell(cell).SetCellValue(obj);cell++;}_row++;}public void SetProtectPassword(string password, string username){_workBook.WriteProtectWorkbook(password, username);}public void SaveAs(string filePath){if (File.Exists(filePath)) File.Delete(filePath);var file = new FileStream(filePath, FileMode.Create);_workBook.Write(file);file.Close();}}}下⾯给出⼩Demo共参考：复制代码代码如下:public void DataTableToExcel(DataTable dt,string path){//instance excel object//Excel excel = new Excel(65536);Excel excel = new Excel();//create a sheetexcel.CreateSheet(dt.Columns);//write value into rows//excel.SetRowValue(dt.Rows);foreach (DataRow row in dt.Rows){excel.SetRowValue(row);}// set excel protectedexcel.SetProtectPassword("etimes2011@", "baiyi");// save excel file to localexcel.SaveAs(path);}：如果要导⼊到Excel中的数据量较多时（⼏⼗万或者⼏百万⾏），全部⼀次性放到DataTable中可能会对内存消耗很⼤，建议每次导⼊的数据最好不要超过1000条，可采取分页查询的⽅式将数据导⼊Excel中。

C#DataTable导出Excel

C#DataTable导出Excel⾸先下载NPOI.dll，下载完成后解压添加引⽤；调⽤⽰例：页⾯按钮<asp:Button ID="btnExecl" runat="server" Width="90px" Height="39px" Text="导出全部名单" BackColor="#DD99FF" OnClick="btnExecl_Click" />页⾯要添加这两个引⽤调⽤⽅法⽰例：//点击导出按钮protected void btnExecl_Click(object sender, EventArgs e){//绑定数据源DataSet ds = bll.GetList("Result=25");if (ds.Tables[0].Rows.Count > 0){DataTable dt = new DataTable();dt.Columns.Add("RTime", typeof(System.String));dt.Columns.Add("Result", typeof(System.String));dt.Columns.Add("OName", typeof(System.String));dt.Columns.Add("OSex", typeof(System.String));dt.Columns.Add("OPhone", typeof(System.String));dt.Columns.Add("OXueLi", typeof(System.String));dt.Columns.Add("OAge", typeof(System.String));dt.Columns.Add("OAddress", typeof(System.String));for (int i = 0; i < ds.Tables[0].Rows.Count; i++){DataRow dr = dt.NewRow();dr["RTime"] = ds.Tables[0].Rows[i]["RTime"].ToString();dr["Result"] = ds.Tables[0].Rows[i]["Result"].ToString();dr["OName"] = ds.Tables[0].Rows[i]["OName"].ToString();dr["OSex"] = ds.Tables[0].Rows[i]["OSex"].ToString();dr["OPhone"] = ds.Tables[0].Rows[i]["OPhone"].ToString();dr["OXueLi"] = ds.Tables[0].Rows[i]["OXueLi"].ToString();dr["OAge"] = ds.Tables[0].Rows[i]["OAge"].ToString();dr["OAddress"] = ds.Tables[0].Rows[i]["OAddress"].ToString();dt.Rows.Add(dr);}//调⽤导出ExportExcel(dt, "导出数据：全对答题名单", "全对答题名单");}else{ScriptManager.RegisterClientScriptBlock(UpdatePanel1, this.GetType(), "", "yz('没有数据，⽆法导出',1);", true);return;}}#region导出数据///<summary>/// DataTable导出Excel///</summary>///<param name="dt">datatable数据源</param>///<param name="strFileName">⽂件名</param>///<param name="strSheetName">⼯作簿名</param>public void ExportExcel(DataTable dt, string strFileName, string strSheetName){HSSFWorkbook book = new HSSFWorkbook();ISheet sheet = book.CreateSheet(strSheetName);IRow headerrow = sheet.CreateRow(0);ICellStyle style = book.CreateCellStyle();style.Alignment = HorizontalAlignment.Center;style.VerticalAlignment = VerticalAlignment.Center;HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);string strColumns = "答题时间,答对,姓名,性别,⼿机,职业,年龄,区域";string[] strArry = strColumns.Split(',');for (int i = 0; i < strArry.Length; i++){dataRow.CreateCell(i).SetCellValue(strArry[i]);dataRow.GetCell(i).CellStyle = style;}for (int i = 0; i < dt.Rows.Count; i++){dataRow = (HSSFRow)sheet.CreateRow(i + 1);for (int j = 0; j < dt.Columns.Count; j++){string ValueType = "";string Value = "";if (dt.Rows[i][j].ToString() != null){ValueType = dt.Rows[i][j].GetType().ToString();Value = dt.Rows[i][j].ToString();}switch (ValueType){case"System.String"://字符串类型dataRow.CreateCell(j).SetCellValue(Value);break;case"System.DateTime"://⽇期类型System.DateTime dateV;System.DateTime.TryParse(Value, out dateV);dataRow.CreateCell(j).SetCellValue(dateV);break;case"System.Boolean"://布尔型bool boolV = false;bool.TryParse(Value, out boolV);dataRow.CreateCell(j).SetCellValue(boolV);break;case"System.Int16"://整型case"System.Int32":case"System.Int64":case"System.Byte":int intV = 0;int.TryParse(Value, out intV);dataRow.CreateCell(j).SetCellValue(intV);break;case"System.Decimal"://浮点型case"System.Double":double doubV = 0;double.TryParse(Value, out doubV);dataRow.CreateCell(j).SetCellValue(doubV);break;case"System.DBNull"://空值处理dataRow.CreateCell(j).SetCellValue("");break;default:dataRow.CreateCell(j).SetCellValue("");break;}dataRow.GetCell(j).CellStyle = style;//设置宽度sheet.SetColumnWidth(j, (Value.Length + 10) * 256);}}MemoryStream ms = new MemoryStream();book.Write(ms);Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray());Response.End();book = null;ms.Close();ms.Dispose(); }#endregion View Code。

.net把datatable导出到excel

.net把datatable导出到exceldsResult = BaseClass.Query(sql);#region 用NPOI方式导出,数据量过多则抛出内存溢出异常//using (System.IO.MemoryStream ms = Util.DataSetToExcel(dsResult))//{// string pathtmp = folder + name + ".xlsx";// using (System.IO.FileStream fs = new System.IO.FileStream(pathtmp, System.IO.FileMode.Create, System.IO.FileAccess.Write))// {// byte[] data = ms.ToArray();// fs.Write(data, 0, data.Length);// fs.Flush();// }//}#endregion//用数据流的方式导出//string path = folder + name + ".xlsx";//ExcelUtil.ExportT oExcel(dsResult.Tables[0], path);//用EPPlus.dll导出Excel(xlsx)string path = folder + name + ".xlsx";ExcelExport.ExportByEPPlus(table,dsResult.Tables[0], path);using System.Data;using System.IO;using erModel;using erModel;using erModel;using NPOI;using NPOI.HPSF;using NPOI.HSSF;using NPOI.HSSF.Util;using NPOI.POIFS;using NPOI.SS.Formula.Eval;using NPOI.Util;using NPOI.SS;using NPOI.DDF;using NPOI.SS.Util;using System.Collections;using System.Text.RegularExpressions;using NPOI.XSSF;using System.Text;using OfficeOpenXml;using OfficeOpenXml.Style;namespace Cis.DRGsSystem.Dal{/// <summary>/// 用NPOI插件导出Excel，如果数据量过大则抛内存溢出错误，改用数据流的方式导出Excel/// </summary>public static class Util{/// <summary>/// 拆分字符串1,2,3,4,5/// </summary>/// <param name="strid">1,2,3,4,5</param>/// <returns></returns>public static string StrArr(this string strid){string StrValue = "";if (!string.IsNullOrEmpty(strid)){string[] strarr = strid.Split(',');foreach (string item in strarr){StrValue += "'" + item.Trim() + "',";}StrValue = StrValue.Substring(0, StrValue.Length - 1);}return StrValue;}#region DataSet导出到Excel/// <summary>/// DataSet导出到Excel的MemoryStream/// </summary>/// <param name="dtSource">源DataSet</param> public static MemoryStream DataSetToExcel(DataSet ds) {XSSFWorkbook workbook = new XSSFWorkbook();for (int k = 0; k < ds.Tables.Count; k++){// HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[k].TableName.ToStr ing());#region 右击文件属性信息{DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();pany = "NPOI";// workbook.DocumentSummaryInformation = dsi;SummaryInformation si = PropertySetFactory.CreateSummaryInformation();si.Author = "文件作者信息"; //填加xls文件作者信息si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息stAuthor = "最后保存者信息"; //填加xls文件最后保存者信息ments = "作者信息"; //填加xls文件作者信息si.Title = "标题信息"; //填加xls文件标题信息si.Subject = "主题信息";//填加文件主题信息si.CreateDateTime = System.DateTime.Now;}#endregionXSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");//取得列宽int rowIndex = 0;foreach (DataRow row in ds.Tables[k].Rows){#region 新建表，填充表头，填充列头，样式if (rowIndex == 0){#region 列头及样式{XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();//headStyle.Alignment = CellHorizontalAlignment.CENTER;XSSFFont font = (XSSFFont)workbook.CreateFont();font.FontHeightInPoints = 10;font.Boldweight = 700;headStyle.SetFont(font);foreach (DataColumn column in ds.T ables[k].Columns){headerRow.CreateCell(column.Ordinal).SetCellValue(column. ColumnName);headerRow.GetCell(column.Ordinal).CellStyle = headStyle;//设置列宽//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);}}#endregionrowIndex = 1;}#endregion#region 填充内容XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);foreach (DataColumn column in ds.T ables[k].Columns){XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);newCell.SetCellType(CellType.String);string drValue = row[column].ToString();switch (column.DataType.ToString()){case "System.String"://字符串类型newCell.SetCellValue(drValue);break;case "System.DateTime"://日期类型System.DateTime dateV;System.DateTime.TryParse(drValue, out dateV);newCell.SetCellValue(dateV);newCell.CellStyle = dateStyle;//格式化显示break;case "System.Boolean"://布尔型bool boolV = false;bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break;case "System.Int16"://整型case "System.Int32":case "System.Int64":case "System.Byte":int intV = 0;int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break;case "System.Decimal"://浮点型case "System.Double":double doubV = 0;double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break;case "System.DBNull"://空值处理newCell.SetCellValue("");break;default:newCell.SetCellValue("");break;}}#endregionrowIndex++;}}using (MemoryStream ms = new MemoryStream()){workbook.Write(ms);ms.Flush();return ms;}}#endregion}/// <summary>/// 用数据流方式导出Excel/// </summary>public class ExcelUtil{/// <summary>/// 导出文件，使用文件流。

如何在Excel中使用DataTable进行数据表分析

如何在Excel中使用DataTable进行数据表分析在Excel中使用DataTable进行数据表分析Excel是一款功能强大的电子表格软件，广泛应用于数据分析和处理领域。

作为Excel的一个重要功能，DataTable可以帮助用户更有效地进行数据表分析。

本文将介绍如何在Excel中使用DataTable进行数据表分析的步骤和技巧。

1. 创建DataTable在Excel中，可以通过多种方式创建一个DataTable。

一种常见的方式是直接从已有的数据表中创建DataTable。

在Excel中选择所需的数据范围，点击“插入”选项卡中的“表格”按钮，Excel将自动识别选中的数据范围并创建一个DataTable。

2. 数据筛选与排序使用DataTable可以轻松进行数据筛选和排序。

在数据表的标题行上点击鼠标右键，选择“筛选”菜单，即可打开筛选功能。

用户可以根据需要选择筛选的条件，Excel将根据条件自动筛选出符合条件的数据。

此外，还可以通过点击标题行的排序图标，对数据进行升序或降序排列。

3. 数据透视表Excel中的数据透视表可以帮助用户更加直观地理解和分析数据表中的信息。

在创建DataTable后，点击“插入”选项卡中的“数据透视表”按钮，选择所需的数据和汇总方式，Excel将自动生成一个数据透视表。

用户可以根据需要灵活调整数据透视表的布局和样式，以获取更清晰的数据分析结果。

4. 条件格式条件格式是Excel的一个重要功能，可以通过设置不同的条件，自动对数据表中的数据进行格式化。

使用DataTable进行数据表分析时，可以基于数据表中的数值大小、数值范围、文本内容等设置条件格式，以便更好地显示和分析数据。

在Excel中选择所需的数据范围，点击“开始”选项卡中的“条件格式”按钮，选择适合的条件格式。

5. 公式计算Excel中的公式计算功能可以对数据表中的数据进行复杂的计算和分析。

使用DataTable进行数据表分析时，可以根据具体需求，在数据表中添加各种公式，以实现不同的数据计算和分析。

C#中datatable导出excel（三种方法）

C#中datatable导出excel（三种⽅法）⽅法⼀：(拷贝直接可以使⽤，适合⼤批量资料, 上万笔)Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();SaveFileDialog savefiledialog = new SaveFileDialog();System.Reflection.Missing miss = System.Reflection.Missing.Value;appexcel = new Microsoft.Office.Interop.Excel.Application();Microsoft.Office.Interop.Excel.Workbook workbookdata;Microsoft.Office.Interop.Excel.Worksheet worksheetdata;Microsoft.Office.Interop.Excel.Range rangedata;//设置对象不可见appexcel.Visible = false;System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us"); workbookdata = appexcel.Workbooks.Add(miss);worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);//给⼯作表赋名称 = "saved";for (int i = 0; i < dt.Columns.Count; i++){worksheetdata.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();}//因为第⼀⾏已经写了表头，所以所有数据都应该从a2开始rangedata = worksheetdata.get_Range("a2", miss);Microsoft.Office.Interop.Excel.Range xlrang = null;//irowcount为实际⾏数，最⼤⾏int irowcount = dt.Rows.Count;int iparstedrow = 0, icurrsize = 0;//ieachsize为每次写⾏的数值，可以⾃⼰设置int ieachsize = 1000;//icolumnaccount为实际列数，最⼤列数int icolumnaccount = dt.Columns.Count;//在内存中声明⼀个ieachsize×icolumnaccount的数组，ieachsize是每次最⼤存储的⾏数，icolumnaccount就是存储的实际列数object[,] objval = new object[ieachsize, icolumnaccount];icurrsize = ieachsize;while (iparstedrow < irowcount){if ((irowcount - iparstedrow) < ieachsize)icurrsize = irowcount - iparstedrow;//⽤for循环给数组赋值for (int i = 0; i < icurrsize; i++){for (int j = 0; j < icolumnaccount; j++)objval[i, j] = dt.Rows[i + iparstedrow][j].ToString();System.Windows.Forms.Application.DoEvents();}string X = "A" + ((int)(iparstedrow + 2)).ToString();string col = "";if (icolumnaccount <= 26){col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();}else{col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();}xlrang = worksheetdata.get_Range(X, col);// 调⽤range的value2属性，把内存中的值赋给excelxlrang.Value2 = objval;iparstedrow = iparstedrow + icurrsize;}//保存⼯作表System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang);xlrang = null;//调⽤⽅法关闭excel进程appexcel.Visible = true;⽅法⼆：(⾃⼰建函数，适合⼤批量资料, 上万笔)using System.IO;private void dataTableToCsv(DataTable table, string file){string title = "";FileStream fs = new FileStream(file, FileMode.OpenOrCreate);//FileStream fs1 = File.Open(file, FileMode.Open, FileAccess.Read);StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default); for (int i = 0; i < table.Columns.Count; i++){title += table.Columns[i].ColumnName + "\t"; //栏位：⾃动跳到下⼀单元格}title = title.Substring(0, title.Length - 1) + "\n";sw.Write(title);foreach (DataRow row in table.Rows){string line = "";for (int i = 0; i < table.Columns.Count; i++){line += row[i].ToString().Trim() + "\t"; //内容：⾃动跳到下⼀单元格}line = line.Substring(0, line.Length - 1) + "\n";sw.Write(line);}sw.Close();fs.Close();}dataTableToCsv(dt, @"c:\1.xls"); //调⽤函数System.Diagnostics.Process.Start(@"c:\1.xls"); //打开excel⽂件⽅法三:(可以⾃⼰调整单元格的格式,适合⼩批量的数量)//没有数据的话就不往下执⾏if (dataGridView1.Rows.Count == 0)return;//实例化⼀个Excel.Application对象Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//让后台执⾏设置为不可见，为true的话会看到打开⼀个Excel，然后数据在往⾥写//excel.Visible = false;excel.Visible = true;//新增加⼀个⼯作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错 excel.Application.Workbooks.Add(true);//⽣成Excel中列头名称for (int i = 0; i < dataGridView1.Columns.Count; i++){excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;}//把DataGridView当前页的数据保存在Excel中for (int i = 0; i < dataGridView1.Rows.Count - 1; i++){for (int j = 0; j < dataGridView1.Columns.Count; j++){if (dataGridView1[j, i].ValueType == typeof(string)){excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();}else{excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();}}}//设置禁⽌弹出保存和覆盖的询问提⽰框excel.DisplayAlerts = false;excel.AlertBeforeOverwriting = false;////保存⼯作簿//excel.Application.Workbooks.Add(true).Save();////保存excel⽂件//excel.Save("D:" + "\\KKHMD.xls");////确保Excel进程关闭//excel.Quit(); //可以直接打开⽂件//excel = null;}catch (Exception ex){MessageBox.Show(ex.Message, "错误提⽰");}Excel.output((DataTable)dataGridView1.DataSource);。

datatable用execl导出2

ing Excel = Microsoft.Office.Interop.Excel;2.3.4.public Excel.Application m_xlApp = null;5.6.7./// <summary>8./// 将DataTable数据导出到Excel表9./// </summary>10./// <param name="tmpDataTable">要导出的DataTable</param>11./// <param name="strFileName">Excel的保存路径及名称</param>12.public void DataTabletoExcel(System.Data.DataTable tmpDataTable, stringstrFileName)13.{14.if (tmpDataTable == null)15.{16.return;17.}18.long rowNum = tmpDataTable.Rows.Count;//行数19.int columnNum = tmpDataTable.Columns.Count;//列数20.Excel.Application m_xlApp = new Excel.Application();21.m_xlApp.DisplayAlerts = false;//不显示更改提示22.m_xlApp.Visible = false;23.24.Excel.Workbooks workbooks = m_xlApp.Workbooks;25.Excel.Workbook workbook =workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);26.Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet127.28.try29.{30.if (rowNum > 65536)//单张Excel表格最大行数31.{32.long pageRows = 65535;//定义每页显示的行数,行数必须小于6553633.int scount = (int)(rowNum / pageRows);//导出数据生成的表单数34.if (scount * pageRows < rowNum)//当总行数不被pageRows整除时，经过四舍五入可能页数不准35.{36.scount = scount + 1;37.}38.for (int sc = 1; sc <= scount; sc++)39.{40.if (sc > 1)41.{42.object missing = System.Reflection.Missing.Value;43.worksheet = (Excel.Worksheet)workbook.Worksheets.Add(44.missing, missing, missing, missing);//添加一个sheet45.}46.else47.{48.worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet149.}50.string[,] datas = new string[pageRows + 1, columnNum];51.52.for (int i = 0; i < columnNum; i++) //写入字段53.{54.datas[0, i] = tmpDataTable.Columns[i].Caption;//表头信息55.}56.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1],worksheet.Cells[1, columnNum]);57.range.Interior.ColorIndex = 15;//15代表灰色58.range.Font.Bold = true;59.range.Font.Size = 9;60.61.int init = int.Parse(((sc - 1) * pageRows).ToString());62.int r = 0;63.int index = 0;64.int result;65.if (pageRows * sc >= rowNum)66.{67.result = (int)rowNum;68.}69.else70.{71.result = int.Parse((pageRows * sc).ToString());72.}73.74.for (r = init; r < result; r++)75.{76.index = index + 1;77.for (int i = 0; i < columnNum; i++)78.{79.object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];80.datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式81.}82.System.Windows.Forms.Application.DoEvents();83.//添加进度条84.}85.86.Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1],worksheet.Cells[index + 1, columnNum]);87.fchR.Value2 = datas;88.worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

DataTable导出到ExcelCSV

StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
DataGrid excel = new DataGrid();
{
sw.WriteLine(dr["F_ID"] + "\t" + dr["F_Psnid"] + "\t" + dr["F_Name"] + "\t" + dr["F_Tel"]
+ "\t" + dr["F_Address"] + "\t" + dr["F_TradeMoney"] + "\t" + dr["F_TradeTime"]);
sw.WriteLine("编号\t帐号\t姓名\t电话\t地址\t金额\t开通时间");
foreach (DataRow dr in thisTable.Rows)
{
sw.WriteLine(dr["F_ID"] + "\t" + dr["F_Psnid"] + "\t" + dr["F_Name"] + "\t" + dr["F_Tel"]
#endregion

DataTable导出Excel的三种方式

DataTable导出Excel的三种方式新加一种，使用使用NPOI导入导出标准Excel一、使用Microsoft.Office.Interop.Excel.DLL需要安装Office代码如下：复制代码2 public static bool ExportExcel(System.Data.DataTable dt, string path)3 {4 bool succeed = false;5 if (dt != null)6 {7 Microsoft.Office.Interop.Excel.Application xlApp = null;8 try9 {10 xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();11 }12 catch (Exception ex)13 {14 throw ex;15 }1617 if (xlApp != null)18 {19 try20 {21 Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);22 object oMissing = System.Reflection.Missing.Value;23 Microsoft.Office.Interop.Excel.Worksheet xlSheet = null;2425 xlSheet = (Worksheet)xlBook.Worksheets[1];26 = dt.TableName;2728 int rowIndex = 1;29 int colIndex = 1;30 int colCount = dt.Columns.Count;31 int rowCount = dt.Rows.Count;3233 //列名的处理34 for (int i = 0; i < colCount; i++)35 {36 xlSheet.Cells[rowIndex, colIndex] = dt.Columns[i].ColumnName;37 colIndex++;38 }39 //列名加粗显示40 xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, colCount]).Font.Bold = true;41 xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]) = "Arial";42 xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Size = "10";43 rowIndex++;4445 for (int i = 0; i < rowCount; i++)46 {47 colIndex = 1;48 for (int j = 0; j < colCount; j++)49 {50 xlSheet.Cells[rowIndex, colIndex] = dt.Rows[i][j].ToString();51 colIndex++;52 }53 rowIndex++;54 }55 xlSheet.Cells.EntireColumn.AutoFit();5657 xlApp.DisplayAlerts = false;58 path = Path.GetFullPath(path);59 xlBook.SaveCopyAs(path);60 xlBook.Close(false, null, null);61 xlApp.Workbooks.Close();62 Marshal.ReleaseComObject(xlSheet);63 Marshal.ReleaseComObject(xlBook);64 xlBook = null;65 succeed = true;66 }67 catch (Exception ex)68 {69 succeed = false;70 }71 finally72 {73 xlApp.Quit();74 Marshal.ReleaseComObject(xlApp);75 int generation = System.GC.GetGeneration(xlApp);76 xlApp = null;77 System.GC.Collect(generation);78 }79 }80 }81 return succeed;82 }复制代码二、使用Aspose.Cells.dllAspose.Cells 是Aspose公司推出的导出Excel的控件，不依赖Office，商业软件，网上有破解（下载见附件）。

C#把DataTable导出为Excel文件

C#把DataTable导出为Excel⽂件实现DataTable转为Excel⽂件，和上次分享的互为反操作。

DataTable转化为Excel⽂件是通过传⼊⼀个DataTable类型的参数，然后将传⼊的DataTable转化为Excel⽂件。

实现关键代码如下：⽅法⼀：/// <summary>/// DataTable数据导出Excel/// </summary>/// <param name="data"></param>/// <param name="filepath"></param>public static void DataTableExport(DataTable data, string filepath){try{//Workbook book = new Workbook("E:\\test.xlsx"); //打开⼯作簿Workbook book = new Workbook(); //创建⼯作簿Worksheet sheet = book.Worksheets[0]; //创建⼯作表Cells cells = sheet.Cells; //单元格//创建样式Aspose.Cells.Style style = book.Styles[book.Styles.Add()];style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应⽤边界线左边界线style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应⽤边界线右边界线style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应⽤边界线上边界线style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应⽤边界线下边界线style.HorizontalAlignment = TextAlignmentType.Center; //单元格内容的⽔平对齐⽅式⽂字居中 = "宋体"; //字体style1.Font.IsBold = true; //设置粗体style.Font.Size = 11; //设置字体⼤⼩//style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //背景⾊//style.Pattern = Aspose.Cells.BackgroundType.Solid; //背景样式//style.IsTextWrapped = true; //单元格内容⾃动换⾏//表格填充数据int Colnum = data.Columns.Count;//表格列数int Rownum = data.Rows.Count;//表格⾏数//⽣成⾏列名⾏for (int i = 0; i < Colnum; i++){cells[0, i].PutValue(data.Columns[i].ColumnName); //添加表头cells[0, i].SetStyle(style); //添加样式//cells.SetColumnWidth(i, data.Columns[i].ColumnName.Length * 2 + 1.5); //⾃定义列宽//cells.SetRowHeight(0, 30); //⾃定义⾼}//⽣成数据⾏for (int i = 0; i < Rownum; i++){for (int k = 0; k < Colnum; k++){cells[1 + i, k].PutValue(data.Rows[i][k].ToString()); //添加数据cells[1 + i, k].SetStyle(style); //添加样式}cells[1 + i, 5].Formula = "=B" + (2 + i) + "+C" + (2 + i);//给单元格设置计算公式，计算班级总⼈数}sheet.AutoFitColumns(); //⾃适应宽book.Save(filepath); //保存GC.Collect();}catch (Exception e){logger.Error("⽣成excel出错：" + e.Message);}}⽅法⼆：public void ToExcel(DataTable dt){#region 3svar FilePath = @"D:\ToExcel\";//产⽣Excel⽂件路径DeleteFile(FilePath);//创建全新的Workbookvar workbook = new HSSFWorkbook();//⼀個sheet最多65536⾏var count = 0;for (double i = 0; i < Convert.ToDouble(dt.Rows.Count) / Convert.ToDouble(65534); i++)//每个Excel⽂件的⼀个页签只能存放65536⾏数据{var row_index = 0;//创建Sheetworkbook.CreateSheet("Sheet" + i);//根据Sheet名字获得Sheet对象var sheet = workbook.GetSheet("Sheet" + i);IRow row;row = sheet.CreateRow(row_index);//写⼊标题for (int j = 0; j < dt.Columns.Count; j++){row.CreateCell(j).SetCellValue(dt.Columns[j].Caption.ToString());}row = sheet.CreateRow(++row_index);//写⼊数据for (int j = 0; j < (dt.Rows.Count - count > 65534 ? 65534 : dt.Rows.Count - count); j++){var r = dt.Rows[j + count];for (int k = 0; k <dt.Columns.Count; k++){row.CreateCell(k).SetCellValue(r[k].ToString());//如果是数字,判断是否需要转换为数字//if (IsNumeric(r[k].ToString()))//{// row.CreateCell(k).SetCellValue(Convert.ToDouble(r[k].ToString()));//}//else//{// row.CreateCell(k).SetCellValue(r[k].ToString());//}}row = sheet.CreateRow(++row_index);}count += row_index - 2;}//保存Workbook⽅式⼀：以⽂件形式保存到服务器中（每次导出都会⽣成⼀个⽂件，慎重使⽤）var FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";var sw = File.Create(FilePath + FileName);workbook.Write(sw);sw.Close();var EC = new ExcelConverter();EC.WebopenExcel(Response, FilePath + FileName);#endregion}到此这篇关于C#把DataTable导出为Excel⽂件的⽂章就介绍到这了。

将DataGridView或DataTable导出为Excel支持进度条

将DataGridView或DataT able导出为Excel支持进度条使用说明://编程语言:C# 主要用于Winforms应用程序的报表导出操作。

//使用时，请添加引用微软的Microsoft.Office.Interop.Excel组件//在以前的上传文档基础上增加两个重载方法，共四种参数的重载：//重载方法1:ExportToExcel(System.Data.DataTable dt)//重载方法2:ExportToExcel(DataGridView dgv)//重载方法3:ExportToExcel(System.Data.DataTable dt, ProgressBar pb)//重载方法4:ExportToExcel(DataGridView dgv, ProgressBar pb)//最后两个重载方法支持进度条控件源代码如下:using System;using System.Collections.Generic;using System.Text;using Microsoft.Office.Interop.Excel;using System.Windows.Forms;namespace DoorControl{/// <summary>/// 静态方法ExportToExcel提供四种参数的重载/// DataGridView或DataTable导出到Excel,可以选择是否添加进度条来查看导出excel 进度/// 需要使用进度条时,请在窗体中加入一个ProgressBar控件progressBar1,并设置progressBar1.Visible=false/// </summary>public class DataTableToExcel{/// <summary>/// 将DataGridV iew转化为DataTable/// </summary>/// <param name="dv"></param>/// <returns></returns>public static System.Data.DataTable dvtodt(DataGridView dv){System.Data.DataTable dt = new System.Data.DataTable();System.Data.DataColumn dc;for (int i = 0; i < dv.Columns.Count; i++){dc = new System.Data.DataColumn();dc.ColumnName = dv.Columns[i].HeaderText.ToString();dt.Columns.Add(dc);}for (int j = 0; j < dv.Rows.Count; j++){System.Data.DataRow dr = dt.NewRow();for (int x = 0; x < dv.Columns.Count; x++){dr[x] = dv.Rows[j].Cells[x].V alue;}dt.Rows.Add(dr);}return dt;}/// <summary>/// 将DataGridV iew导出为Excel文件(.xls)/// </summary>/// <param name="dgv">要导出的DatGridView</param>public static void ExportToExcel(DataGridView dgv){//获得DataTableSystem.Data.DataTable dt = dvtodt(dgv);ExportToExcel(dt);}/// <summary>/// 将DataGridV iew导出为excel文件,导出过程中显示进度条/// </summary>/// <param name="dgv">要导出的DatGridView</param>/// <param name="pb">与dgv在同一窗体的ProgressBar控件,pb初始化时Visible=false</param>public static void ExportToExcel(DataGridView dgv, ProgressBar pb){//获得DataTableSystem.Data.DataTable dt = dvtodt(dgv);ExportToExcel(dt, pb);}/// <summary>/// 将DataTable导出为Excel文件(.xls)/// </summary>/// <param name="dt">要导出的DataTable</param>public static void ExportToExcel(System.Data.DataTable dt){if (dt == null) return;Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();if (xlApp == null){MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");return;}System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();saveDia.Filter = "Excel|*.xls";saveDia.Title = "导出为Excel文件";saveDia.FileName = string.Format("{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK&& !string.Empty.Equals(saveDia.FileName)){Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBA Template.xlWBA TWorksheet);Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1Microsoft.Office.Interop.Excel.Range range = null;long totalCount = dt.Rows.Count;long rowRead = 0;float percent = 0;string fileName = saveDia.FileName;//写入标题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;//背景颜色range.Font.Bold = true;//粗体range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);//range.ColumnWidth = 4.63;//设置列宽//range.EntireColumn.AutoFit();//自动调整列宽//r1.EntireRow.AutoFit();//自动调整行高}//写入内容for (int r = 0; r < dt.DefaultView.Count; r++){for (int i = 0; i < dt.Columns.Count; i++){worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];range.Font.Size = 9;//字体大小//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);range.EntireColumn.AutoFit();//自动调整列宽}rowRead++;percent = ((float)(100 * rowRead)) / totalCount;////进度条//pb.Maximum = (int)totalCount;//pb.V alue = (int)rowRead;System.Windows.Forms.Application.DoEvents();}range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;if (dt.Columns.Count > 1){range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideV ertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;}try{workbook.Saved = true;workbook.SaveCopyAs(fileName);}catch (Exception ex){MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);return;}workbooks.Close();if (xlApp != null){xlApp.Workbooks.Close();xlApp.Quit();int generation = System.GC.GetGeneration(xlApp);System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);xlApp = null;System.GC.Collect(generation);}GC.Collect();//强行销毁#region 强行杀死最近打开的Excel进程System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");System.DateTime startTime = new DateTime();int m, killId = 0;for (m = 0; m < excelProc.Length; m++){if (startTime < excelProc[m].StartTime){startTime = excelProc[m].StartTime;killId = m;}}if (excelProc[killId].HasExited == false){excelProc[killId].Kill();}#endregionMessageBox.Show("导出成功!");}}/// <summary>/// 将DataTable导出为excel文件,导出过程中显示进度条/// </summary>/// <param name="dt">要导出的DataTable对象</param>/// <param name="pb">窗体中的ProgressBar控件,pb初始化时Visible=false</param>public static void ExportToExcel(System.Data.DataTable dt, ProgressBar pb){if (dt == null) return;Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();if (xlApp == null){MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");return;}System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();saveDia.Filter = "Excel|*.xls";saveDia.Title = "导出为Excel文件";saveDia.FileName = string.Format("{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK&& !string.Empty.Equals(saveDia.FileName)){Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBA Template.xlWBA TWorksheet);Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1Microsoft.Office.Interop.Excel.Range range = null;long totalCount = dt.Rows.Count;pb.V isible = true;//将进度条控件显示pb.Maximum = (int)totalCount;//设置进度条控件的最大值long rowRead = 0;float percent = 0;string fileName = saveDia.FileName;//写入标题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;//背景颜色range.Font.Bold = true;//粗体range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);//range.ColumnWidth = 4.63;//设置列宽//range.EntireColumn.AutoFit();//自动调整列宽//r1.EntireRow.AutoFit();//自动调整行高}//写入内容for (int r = 0; r < dt.DefaultView.Count; r++){for (int i = 0; i < dt.Columns.Count; i++){worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];range.Font.Size = 9;//字体大小//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);range.EntireColumn.AutoFit();//自动调整列宽}rowRead++;percent = ((float)(100 * rowRead)) / totalCount;//进度条控件需要用百分号表示进度时,使用该行代码pb.V alue = (int)rowRead;//设置进度条控件的当前值System.Windows.Forms.Application.DoEvents();}range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;if (dt.Columns.Count > 1){range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideV ertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;}try{workbook.Saved = true;workbook.SaveCopyAs(fileName);}catch (Exception ex){MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);return;}workbooks.Close();if (xlApp != null){xlApp.Workbooks.Close();xlApp.Quit();int generation = System.GC.GetGeneration(xlApp);System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);xlApp = null;System.GC.Collect(generation);}GC.Collect();//强行销毁#region 强行杀死最近打开的Excel进程System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");System.DateTime startTime = new DateTime();int m, killId = 0;for (m = 0; m < excelProc.Length; m++){if (startTime < excelProc[m].StartTime){startTime = excelProc[m].StartTime;killId = m;}}if (excelProc[killId].HasExited == false){excelProc[killId].Kill();}#endregionpb.V isible = false;//再次将进度条隐藏MessageBox.Show("导出成功!");}}}}。

asp.net 将DataTable中的数据导出到Excel并下载方法

将DataTable中的数据导出到Excel并下载方法我上一篇文章介绍了Excel导入到DataTable的方法，总觉得少些什么，这篇我就将DataTable中的数据导出到Excel并提供下载的方法记录下来。

调用如下：CreateExcel(dtexcel, "application/ms-excel", excel);方法如下：/// <summary>/// DataTable中的数据导出到Excel并下载/// </summary>/// <param name="dt">要导出的DataTable</param>/// <param name="FileType">类型</param>/// <param name="FileName">Excel的文件名</param>public void CreateExcel(DataTable dt, string FileType, string FileName){Response.Clear();Response.Charset = "UTF-8";Response.Buffer = true;Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");Response.AppendHeader("Content-Disposition", "attachment;filename=\"" +System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");Response.ContentType = FileType;string colHeaders = string.Empty;string ls_item = string.Empty;DataRow[] myRow = dt.Select();int i = 0;int cl = dt.Columns.Count;foreach (DataRow row in myRow){for (i = 0; i < cl; i++){if (i == (cl - 1)){ls_item += row[i].ToString() + "\n";}else{ls_item += row[i].ToString() + "\t";}}Response.Output.Write(ls_item);ls_item = string.Empty;}Response.Output.Flush();Response.End();}将Excel中某个工作簿的数据导入到DataTable方法最近在网上看了几篇将Excel中某个工作簿的数据导入到DataTable的文章，自己总结了一套最实用的方法。

.NET中用C#将DataTable中的数据集导出为EXCEL或CSV文件

.NET中用C#将DataTable中的数据集导出为EXCEL 或CSV文件有的时候客户需要将筛选后的数据从服务器上以EXCEL或CSV文件格式保存到本地，比如：用户需要将服务器存储的客户邮箱信息下载到本地，然后导入到126邮箱。

进行处理：CSVDataTable dt = DBHelper.GetTable(CommandType.Text, cmdText);if (dt.Rows.Count != 0){int rows = dt.Rows.Count;Response.ClearHeaders();Response.ContentType = "application/vnd.ms-excel";Response.Charset = "";Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode("数据导出文件" + DateTime.Now.Ticks + ".csv"));System.IO.StreamWriter objWriter = newSystem.IO.StreamWriter(Response.OutputStream,System.Text.Encoding.UTF8);StringBuilder result = new StringBuilder();result.Append("联系组,");result.Append("姓名,");result.Append("邮件地址,");result.Append("联系地址,");result.Append("邮政编码,");result.Append("联系电话,");result.Append("移动电话,");result.Append("公司,");result.Append("部门,");result.Append("职位,");objWriter.WriteLine(result.ToString()); //输出标题行CSV文件中以“，”隔开，当拼凑到一行时，用WriteLine输出，而不是Writefor (int i = 0; i < rows; i++){result = new StringBuilder();result.Append("邮箱联系组,");result.Append(dt.Rows[i]["lianxiren"].ToString() + ",");result.Append(dt.Rows[i]["email"].ToString() + ","); result.Append(dt.Rows[i]["addr"].ToString() + ",");result.Append(dt.Rows[i]["postcode"].ToString() + ","); result.Append(dt.Rows[i]["tel"].ToString() + ",");result.Append(dt.Rows[i]["mobile"].ToString() + ","); result.Append(dt.Rows[i]["compname"].ToString() + ","); result.Append(dt.Rows[i]["dpt"].ToString() + ",");result.Append(dt.Rows[i]["position"].ToString() + ","); objWriter.WriteLine(result.ToString());}objWriter.Close();Response.End();}EXCELDataTable dt = DBHelper.GetTable(CommandType.Text, cmdText);if (dt.Rows.Count != 0){int rows = dt.Rows.Count;Response.ContentType = "application/vnd.ms-excel";Response.Charset = "";Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode("导出文件.xls"));System.IO.StreamWriter objWriter = newSystem.IO.StreamWriter(Response.OutputStream,System.Text.Encoding.UTF8);objWriter.WriteLine("<?xml version=\"1.0\"?>");objWriter.WriteLine("<?mso-applicationprogid=\"Excel.Sheet\"?>");objWriter.WriteLine("<Workbookxmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"xmlns:o=\"urn:schemas-microsoft-com:office:office\"xmlns:x=\"urn:schemas-microsoft-com:office:excel\"xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"xmlns:html=\"/TR/REC-html40\">");objWriter.WriteLine("<Worksheet ss:Name=\"Sheet1\">");objWriter.WriteLine("<Table>");objWriter.WriteLine("<Column ss:AutoFitWidth=\"0\"ss:Width=\"33\"/>");objWriter.WriteLine("<Column ss:AutoFitWidth=\"0\"ss:Width=\"250\"/>");objWriter.WriteLine("<Column ss:AutoFitWidth=\"0\"ss:Width=\"250\"/>");objWriter.WriteLine("<Column ss:AutoFitWidth=\"0\"ss:Width=\"250\"/>");objWriter.WriteLine("<Column ss:AutoFitWidth=\"0\"ss:Width=\"130\"/>");objWriter.WriteLine("<Row>");objWriter.WriteLine("<Cell><Data ss:Type=\"String\">编号</Data></Cell>");objWriter.WriteLine("<Cell><Data ss:Type=\"String\">第一列</Data></Cell>");objWriter.WriteLine("<Cell><Data ss:Type=\"String\">第二列</Data></Cell>");objWriter.WriteLine("<Cell><Data ss:Type=\"String\">第三列</Data></Cell>");objWriter.WriteLine("<Cell><Data ss:Type=\"String\">第四列</Data></Cell>");objWriter.WriteLine("</Row>");for (int i = 0; i < rows; i++) //注：一直用WriteLine进行输出，因为CSV文件是以“，”为分割的简单文件格式。

DataTable导出到Excel时身份证号信息自动转换为科学计数法的解决方案

DataTable导出到Excel时⾝份证号信息⾃动转换为科学计数法的
解决⽅案
做项⽬的时候是否遇到过这种问题，我今天遇到了就给⼤家分享下，在⽹上找的答案都是针对gridview控件的，还有就是该DataTable的⾝份证号列的类型为string，再有就是前⾯加单引号什么的，但是我都没有试验成功不知道是我⼈品的问题还是真不⾏，但是今天我整的这个⽅案完美避开了科学计数法呵呵废话少说上代码
1 DataTable dt = new DataTable();
2 dt.Columns.Add(new DataColumn("⾝份证号"));
3 DataRow dr = dt.NewRow();
4 StringBuilder sb = new StringBuilder("=\"");
5 sb.Append("1234567890123456789").Append("\"");
6 dr[0] = sb.ToString();
7 dt.Rows.Add(dr);
8 ImportAndExport.CreateExcel(dt, "1", "11.xls");
嘿嘿够简单吧！原理就是Excel会⾃动把="1234567890123456789"认为计算字符串的公式嘿嘿，不信的请新建个Exel⽂件试试。

C#DataTable导出Excel，并实现合并单元格

C#DataTable导出Excel，并实现合并单元格 webwofrm后台代码----------建议Framework4.0及以上，3.5试过出现好多莫名错误...⾸先导⼊两个程序集。

我的是 office2003，引⽤的COM⾥⾯的 Microsoft.office.Interop.Excel 14.0 和程序集⾥的Microsoft Excel 12.0 Object Library。

有多个版本，根据⾃⼰的office版本选择。

添加之后引⽤⾥⼒会出现两个DLL Microsoft.office.Core 和Microsoft.office.Interop.Excel⽣成⼀下项⽬，如果出现 Excel.ApplicationClass()⽆法互嵌套操作类型请改⽤适⽤的接⼝把引⽤的Microsoft.Office.Interop.Excel 右击属性嵌⼊互操作类型改为false即可DataTable dt = new DataTable();DataColumn dc1 = new DataColumn();dc1.ColumnName = "name";dc1.DataType = typeof(string);DataColumn dc2 = new DataColumn();dc2.ColumnName = "age";dc2.DataType = typeof(int);dt.Columns.Add(dc1);dt.Columns.Add(dc2);for (int i = 0; i < 1000; i++){DataRow dr = dt.NewRow();dr[0] = "茂茂" + i + "号";dr[1] = i;dt.Rows.Add(dr);}new Class1().DataTabletoExcel(dt, DateTime.Now.Ticks+".xlsx");先造些数据，然后将datatable和要导出⽂件名传递给⽅法。

【推荐下载】DataTable的行列转换及多表头HTML表格转Excel

DataTable 的行列转换及多表头HTML 表格转Excel 2017/05/27 0 需要解决的问题：1、根据数据库中多个不同字段名称的以行形式表现的数据转为以列形式展现的数据；2、HTML 多表头数据展示，做到数据“一个萝卜一个坑”，并求出多条数据的合计数据。

3、将HTML 表格转为EXCEL。

先看效果图：图1 HTML 页面表格图2 导出为Excel 文档图3 数据行不同，表头列数也不相同图4 行数据记录不同时，表格的表头也变得不同（对比图1），各相关行数据需要一一对应。

HTML 表格的结构：table id= TableStatics align= centertbody tr td rowspan= 2 工单编号/td td rowspan= 2 生产数量/td td colspan= 7 > OCV1 /td td colspan= 6 > OCV2 /td td colspan= 7 > OCV3 /td td colspan= 9 > OCV4 /td /tr tr td nowrap= A /td td nowrap= A1 /td td nowrap= 电压B /td td nowrap= 电压C /td td nowrap= 内阻D /td td nowrap= 扫描异常/td td nowrap= 数码A /td td nowrap= A /td td nowrap= C /td td nowrap= D1 /td td nowrap= D3 /td td nowrap= N2 /td td nowrap= 扫描异常/td td nowrap= A /td td nowrap= C /td .... td nowrap= 扫描异常/td /tr !-- 数据行开始-- tr td 20170221 /td td 100000 /td td 18 /td ... /tr tr..... /tbody。