C# 读写Excel的一些方法,Aspose.Cells.dll
需求：现有2个Excel，一个7000，一个20W，7000在20W是完全存在的。现要分离20W的，拆分成19W3和7000。
条件：两个Excel都有“登录名”，然后用“登录名”去关联2个Excel
public void Excel() { //获取第一个Excel,20W string filePath = AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/测试20W.xlsx"; System.Data.DataTable table = GetTableFromExcel("sheet1", filePath); //克隆 System.Data.DataTable table20W_new = table.Clone(); System.Data.DataTable table7000_new = table.Clone(); //获取第二个Excel,7000 string filePath_7000 = AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/测试7000.xls"; System.Data.DataTable table_7000 = GetTableFromExcel("sheet1", filePath_7000); //循环20W人中的挑出来 for (int i = 0; i < table.Rows.Count; ++i) { //20W DataRow dateRow = table.Rows[i]; string login_name = dateRow["登录名"].ToString(); //7000 DataRow[] drss = table_7000.Select("登录名 = '" + login_name + "'"); if (drss.Length > 0) { table7000_new.ImportRow(dateRow); } else { table20W_new.ImportRow(dateRow); } } //导出Excel DataTableExport(table7000_new, AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/7000.xlsx"); DataTableExport(table20W_new, AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/22W.xlsx");
}
获取Excel内容，转成DataTable。
/// <summary> /// 获取Excel内容。 /// </summary> /// <param name="sheetName">工作表名称，例：sheet1</param> /// <param name="filePath">Excel路径</param> /// <returns></returns> private DataTable GetTableFromExcel(string sheetName, string filePath) { const string connStrTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;\""; DataTable dt = null; if (!System.IO.File.Exists(filePath)) { // don't find file return null; } OleDbConnection conn = new OleDbConnection(string.Format(connStrTemplate, filePath)); try { conn.Open(); if (sheetName == null || sheetName.Trim().Length == 0) { DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim(); } else { sheetName += "$"; } string strSQL = "Select * From [" + sheetName + "]"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, conn); DataSet ds = new DataSet(); da.Fill(ds); dt = ds.Tables[0]; } catch (Exception ex) { throw ex; } finally { conn.Close(); } return dt; }
将DataTable的数据写进Excel里(用的Aspose.Cells.dll)
/// <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(); //创建工作簿 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; //单元格内容的水平对齐方式文字居中 style.Font.Name = "宋体"; //字体 //style1.Font.IsBold = true; //设置粗体 style.Font.Size = 11; //设置字体大小 //style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //背景色 //style.Pattern = Aspose.Cells.BackgroundType.Solid; 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); //添加样式 } //生成数据行 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); //添加样式 } } sheet.AutoFitColumns(); //自适应宽 book.Save(filepath); //保存 GC.Collect(); } catch (Exception e) { logger.Error("生成excel出错：" + e.Message); } }
将List的数据写进Excel里(用的Aspose.Cells.dll)
/// <summary> /// 导出excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="data">Ilist集合</param> /// <param name="filepath">保存的地址</param> public static void Export<T>(IList<T> data, string filepath) { try { Workbook workbook = new Workbook(); Worksheet sheet = (Worksheet)workbook.Worksheets[0]; PropertyInfo[] ps = typeof(T).GetProperties(); var colIndex = "A"; foreach (var p in ps) { // sheet.Cells[colIndex + 1].PutValue(p.Name);//设置表头名称 要求表头为中文所以不用 p.name 为字段名称 可在list第一条数据为表头名称 int i = 1; foreach (var d in data) { sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null)); i++; } colIndex = getxls_top(colIndex); //((char)(colIndex[0] + 1)).ToString();//表头 A1/A2/ } //workbook.Shared = true; workbook.Save(filepath); GC.Collect(); } catch (Exception e) { logger.Error("生成excel出错：" + e.Message); } } /// <summary> /// 生成新的对应的列 A-Z AA-ZZ /// </summary> /// <param name="top">当前列</param> /// <returns></returns> private static string getxls_top(string top) { char[] toplist = top.ToArray(); var itemtop = top.Last(); string topstr = string.Empty; if ((char)itemtop == 90)//最后一个是Z { if (toplist.Count() == 1) { topstr = "AA"; } else { toplist[0] = (char)(toplist[0] + 1); toplist[toplist.Count() - 1] = 'A'; foreach (var item in toplist) { topstr += item.ToString(); } } } else//最后一个不是Z 包括top为两个字符 { itemtop = (char)(itemtop + 1); toplist[toplist.Count() - 1] = itemtop; foreach (var item in toplist) { topstr += item.ToString(); } } return topstr; }
将DataTable的数据写进Excel里(用的Microsoft.Office.Interop.Excel.dll)
此方法在大量数据的时候很慢，例如22W条数据，建议使用Aspose.Cells.dll，速度快很多
/// <summary> /// 将DataTable的数据写进Excel里 /// </summary> /// <param name="tmpDataTable">DataTable数据</param> /// <param name="strFileName">Excel路径</param> public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName) { if (tmpDataTable == null) { return; } int rowNum = tmpDataTable.Rows.Count; int columnNum = tmpDataTable.Columns.Count; int rowIndex = 1; int columnIndex = 0; //需要引用Microsoft.Office.Interop.Excel.dll Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlApp.DefaultFilePath = ""; xlApp.DisplayAlerts = true; xlApp.SheetsInNewWorkbook = 1; Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); //将DataTable的列名导入Excel表第一行 foreach (DataColumn dc in tmpDataTable.Columns) { columnIndex++; xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName; } //将DataTable中的数据导入Excel中 for (int i = 0; i < rowNum; i++) { rowIndex++; columnIndex = 0; for (int j = 0; j < columnNum; j++) { columnIndex++; xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString(); } }
//xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8)); xlBook.SaveCopyAs(strFileName); }
原生的DataTable生成Excel(无需引用第三方dll)
/// <summary> /// 将DataTable的数据写进Excel里 /// </summary> /// <param name="tdKeChengZhuanJiaTongJi">DataTable</param> /// <param name="sheet">sheet自定义名称</param> /// <param name="fileName">Excel路径</param> public static void DataTabletoExcel(DataTable dt, string sheet, string fileName) { String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0 Xml;\""; //string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;'"; //String sConnectionString = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + fileName + ";Extended Properties=\"Excel 14.0 Xml;\""; //String sConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" + fileName + ";Extended Properties=\"Excel 16.0 Xml;HDR=YES;\""; OleDbConnection cn = new OleDbConnection(sConnectionString); int rowNum = dt.Rows.Count;//获取行数 int colNum = dt.Columns.Count;//获取列数 string sqlText = "";//带类型的列名 string sqlValues = "";//值 string colCaption = "";//列名 for (int i = 0; i < colNum; i++) { if (i != 0) { sqlText += " , "; colCaption += " , "; } sqlText += "[" + dt.Columns[i].Caption.ToString() + "] VarChar";//生成带VarChar列的标题 colCaption += "[" + dt.Columns[i].Caption.ToString() + "]";//生成列的标题 } try { //打开连接 cn.Open(); string sqlCreate = "CREATE TABLE [" + sheet.ToString() + "] (" + sqlText + ")"; OleDbCommand cmd = new OleDbCommand(sqlCreate, cn); //创建Excel文件 cmd.ExecuteNonQuery(); for (int srow = 0; srow < rowNum; srow++) { sqlValues = ""; for (int col = 0; col < colNum; col++) { if (col != 0) { sqlValues += " , "; } sqlValues += "'" + dt.Rows[srow][col].ToString() + "'";//拼接Value语句 } String queryString = "INSERT INTO [" + sheet.ToString() + "] (" + colCaption + ") VALUES (" + sqlValues + ")"; cmd.CommandText = queryString; cmd.ExecuteNonQuery();//插入数据 } } catch { //生成日志 } finally { cn.Close(); } }
