上万级大批量数据导出Excel（多方案）
大批量数据Excel
比如数据量一大（比如上万条以上的记录），用传统的方式和插件等导出excel速度都很慢，甚至最终导致内存益出；往文本文件直接插入记录的方式(速度快，占内存也少)，然后用\t割开代表一列，产生的文件，直接用excel打开就可以，单只支持单个sheet页，office2003(xls)限制一页65536行，需要分割为多个文件，具体如下：
String path="c:/test.xls";//数据存放的位置 BufferedWriter buff = new BufferedWriter(new FileWriter(path));//生成文件 //插入标题 buff.write("部门名称\t用户\t电话");//代表3列 buff.write("\r\n");//换行 //插入5万条记录 for (int i = 0; i < 50000; i++) { buff .write("部门\t小吴\t123456"); buff.write("\r\n"); } buff.close(); //关闭文件操作
多页sheet导出
/// <summary> ///可导出多个sheet表 /// </summary> /// <param name="Author">作者</param> /// <param name="Company">公司</param> /// <param name="dt">多个DataTable</param> /// <param name="fileName">文件名</param> public static void PushExcelToClientEx(string Author, string Company, DataTable[] dt, string fileName) { if (!fileName.Contains(".xls")) { fileName += ".xls"; } StringBuilder sbBody = new StringBuilder(); StringBuilder sbSheet = new StringBuilder(); sbBody.AppendFormat( "MIME-Version: 1.0\r\n" + "X-Document-Type: Workbook\r\n" + "Content-Type: multipart/related; boundary=\"-=BOUNDARY_EXCEL\"\r\n\r\n" + "---=BOUNDARY_EXCEL\r\n" + "Content-Type: text/html; charset=\"gbk\"\r\n\r\n" + "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" + "xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n" + "<head>\r\n" + "<xml>\r\n" + "<o:DocumentProperties>\r\n" + "<o:Author>{0}</o:Author>\r\n" + "<o:LastAuthor>{0}</o:LastAuthor>\r\n" + "<o:Created>{1}</o:Created>\r\n" + "<o:LastSaved>{1}</o:LastSaved>\r\n" + "<o:Company>{2}</o:Company>\r\n" + "<o:Version>11.5606</o:Version>\r\n" + "</o:DocumentProperties>\r\n" + "</xml>\r\n" + "<xml>\r\n" + "<x:ExcelWorkbook>\r\n" + "<x:ExcelWorksheets>\r\n" , Author , DateTime.Now.ToString() , Company); foreach (var d in dt) { string gid = Guid.NewGuid().ToString(); sbBody.AppendFormat("<x:ExcelWorksheet>\r\n" + "<x:Name>{0}</x:Name>\r\n" + "<x:WorksheetSource HRef=\"cid:{1}\"/>\r\n" + "</x:ExcelWorksheet>\r\n" , d.TableName.Replace(":", "").Replace("\\", "").Replace("/", "").Replace("?", "").Replace("*", "").Replace("[", "").Replace("]", "").Trim() , gid); sbSheet.AppendFormat( "---=BOUNDARY_EXCEL\r\n" + "Content-ID: {0}\r\n" + "Content-Type: text/html; charset=\"gbk\"\r\n\r\n" + "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" + "xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n" + "<head>\r\n" + "<xml>\r\n" + "<x:WorksheetOptions>\r\n" + "<x:ProtectContents>False</x:ProtectContents>\r\n" + "<x:ProtectObjects>False</x:ProtectObjects>\r\n" + "<x:ProtectScenarios>False</x:ProtectScenarios>\r\n" + "</x:WorksheetOptions>\r\n" + "</xml>\r\n" + "</head>\r\n" + "<body>\r\n" , gid); sbSheet.Append("<table border='1'>"); sbSheet.Append("<tr style='background-color: #CCC;'>"); for (int i = 0; i < d.Columns.Count; i++) { sbSheet.AppendFormat("<td style='vnd.ms-excel.numberformat: @;font-weight:bold'>{0}</td>", d.Columns[i].ColumnName); } sbSheet.Append("</tr>"); for (int j = 0; j < d.Rows.Count; j++) { sbSheet.Append("<tr>"); for (int k = 0; k < d.Columns.Count; k++) { sbSheet.AppendFormat("<td style='vnd.ms-excel.numberformat: @;'>{0}</td>", Convert.ToString(d.Rows[j][k])); } sbSheet.Append("</tr>"); } sbSheet.Append("</table>"); sbSheet.Append("</body>\r\n" + "</html>\r\n\r\n"); } StringBuilder sb = new StringBuilder(sbBody.ToString()); sb.Append("</x:ExcelWorksheets>\r\n" + "</x:ExcelWorkbook>\r\n" + "</xml>\r\n" + "</head>\r\n" + "</html>\r\n\r\n"); sb.Append(sbSheet.ToString()); sb.Append("---=BOUNDARY_EXCEL--"); HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gbk"); HttpContext.Current.Response.Write(sb.ToString()); HttpContext.Current.Response.End(); }
多页sheet--可拼接文本导出xls格式
<?xml version="1.0" ?> <?mso-application progid="Excel.Sheet" ?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author> hy </Author> <Created> hy31337 </Created> </DocumentProperties> <Styles> <Style ss:ID="Currency"> <NumberFormat ss:Format="Currency"> </NumberFormat> </Style> <Style ss:ID="Date"> <NumberFormat ss:Format="Medium Date"> </NumberFormat> </Style> </Styles> <Worksheet ss:Name="sheet1"> <Table> <Row> <Cell> <Data ss:Type="String"> ID </Data> </Cell> <Cell> <Data ss:Type="String"> 姓名 </Data> </Cell> <Cell> <Data ss:Type="String"> 年龄 </Data> </Cell> </Row> <Row> <Cell> <Data ss:Type="String"> 0001 </Data> </Cell> <Cell> <Data ss:Type="String"> 张三 </Data> </Cell> <Cell> <Data ss:Type="String"> 24 </Data> </Cell> </Row> </Table> </Worksheet> <Worksheet ss:Name="sheet2"> <Table> <Row> <Cell> <Data ss:Type="String"> ID </Data> </Cell> <Cell> <Data ss:Type="String"> 姓名 </Data> </Cell> <Cell> <Data ss:Type="String"> 年龄 </Data> </Cell> </Row> <Row> <Cell> <Data ss:Type="String"> 0002 </Data> </Cell> <Cell> <Data ss:Type="String"> 李四 </Data> </Cell> <Cell> <Data ss:Type="String"> 24 </Data> </Cell> </Row> </Table> </Worksheet> </Workbook>
Apose.Cells批量插入导出--使用 sheet.ImportDataTable
1.多线程读不同类型数据并插入各个Sheet
public Workbook CreateTempletExcel_MNSheets(string vMN, string vSTime, string vETime) { List<string> listMN = vMN.ToSplitList(','); //Excel对象 Workbook workbook = new Workbook(); DateTime dtStartTime = Convert.ToDateTime(vSTime); DateTime dtEndTime = Convert.ToDateTime(vETime); List<T_ProjectExtInfo> pExtInfo = new T_ProjectDAL().GetMNProjectInfoList(listMN, null); if (pExtInfo == null || pExtInfo.Count < 1) return workbook; int totalThread = Environment.ProcessorCount - 2; Parallel.ForEach(listMN, new ParallelOptions() { MaxDegreeOfParallelism = totalThread > 6 ? 6 : totalThread }, (MN, loopState) => { var listProject = pExtInfo.Where(p => p.MN == MN); if (listProject == null || listProject.Count() < 1) return; List<string> listParamID = listProject.Select(s => s.LHCodeID).ToList(); DataTable dtHisData = new SQLServerDAL.MN_8051_MNDAL().Get805DataTable(MN, listParamID, dtStartTime, dtEndTime); DataTable dtData = new DataTable(); Worksheet sheet = workbook.Worksheets.Add(listProject.First().MNName + "_" + MN); #region 表头 sheet.FreezePanes(2, 1, 2, 1); int rowNum = 0; int iBaseCell = 1; Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.IsBold = true; style.Font.Name = "宋体"; style.Font.Size = 12; //固定模板头 //居中、画边框、粗体、背景色为浅蓝 style = workbook.Styles[workbook.Styles.Add()]; style.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.ForegroundColor = System.Drawing.Color.FromArgb(191, 191, 191); style.Pattern = BackgroundType.Solid; style.Font.IsBold = true; //设置行高 //cells.SetRowHeight(0, 30); Cells cells = sheet.Cells; Aspose.Cells.Style Sstyle = workbook.Styles[workbook.Styles.Add()]; //列头设置统一样式 style.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0); Range dateDeatailRange = sheet.Cells.CreateRange(0, 0, 2, listProject.Count() + iBaseCell); StyleFlag dateDeatailFlg = new StyleFlag(); dateDeatailFlg.All = true; dateDeatailRange.ApplyStyle(style, dateDeatailFlg); sheet.Cells.Merge(rowNum, 0, 2, 1);//合并行 sheet.Cells[rowNum, 0].PutValue("监测时间"); dtData.Columns.Add(new DataColumn("DataTime", typeof(string))); int ik = iBaseCell; foreach (var pObj in listProject) { sheet.Cells[rowNum, ik].PutValue(pObj.ParamName + "(" + pObj.ParamUnit + ")"); sheet.Cells[rowNum + 1, ik].PutValue(GetParamUnitAndLevelA(pObj)); sheet.Cells[rowNum, ik].SetStyle(style); sheet.Cells[rowNum + 1, ik].SetStyle(style); dtData.Columns.Add(new DataColumn(pObj.LHCodeID, typeof(string))); ik++; } sheet.Cells[rowNum, 0].SetStyle(style); sheet.Cells[rowNum + 1, 0].SetStyle(style); sheet.Cells[rowNum, 1].SetStyle(style); sheet.Cells[rowNum + 1, 1].SetStyle(style); sheet.Cells[rowNum, 2].SetStyle(style); sheet.Cells[rowNum + 1, 2].SetStyle(style); #endregion 表头 string strDateTimeRecord = ""; DateTime dtNewTime; /*组装数据*/ DataRow drData = dtData.NewRow(); foreach (DataRow item in dtHisData.Rows) { if (strDateTimeRecord != item["DataTime"].ToString()) { if (DateTime.TryParse(strDateTimeRecord, out dtNewTime)) { drData["DataTime"] = dtNewTime.ToString("yyyy-MM-dd HH:mm"); dtData.Rows.Add(drData); } drData = dtData.NewRow(); strDateTimeRecord = item["DataTime"].ToString(); } drData[item["LHCodeID"].ToString()] = item["DataValue"].ToString() + (string.IsNullOrEmpty(item["DataFlag"].ToString()) ? "" : item["DataFlag"].ToString().ToUpper().Replace("N", "")); } if (DateTime.TryParse(strDateTimeRecord, out dtNewTime)) { drData["DataTime"] = dtNewTime.ToString("yyyy-MM-dd HH:mm"); dtData.Rows.Add(drData); } //批量插入 int iCount = cells.ImportDataTable(dtData, false, "A3"); dtHisData.Clear(); dtData.Clear(); sheet.AutoFitColumns(); //自适应列宽 }); workbook.Worksheets.RemoveAt(0); return workbook; }
2.多线程读不同类型数据组装为一个DataTable集合，将总数据再插入（分页）
object _objectGD = new object(); public Workbook CreateTempletExcel_MNsSheet(string vMN, string vSTime, string vETime) { //Excel对象 Workbook workbook = new Workbook(); List<string> listMN = vMN.ToSplitList(','); List<T_ProjectExtInfo> pExtInfo = new T_ProjectDAL().GetMNProjectInfoList(listMN, null); if (pExtInfo == null || pExtInfo.Count < 1) return workbook; List<string> listParamIDAll = pExtInfo.Select(s => s.LHCodeID).Distinct().ToList(); DateTime dtStartTime = Convert.ToDateTime(vSTime); DateTime dtEndTime = Convert.ToDateTime(vETime); DataTable dtDataAll = new DataTable(); #region 表头 Worksheet sheet = workbook.Worksheets[0]; int rowNum = 0; int iBaseCell = 3; Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.IsBold = true; style.Font.Name = "宋体"; style.Font.Size = 12; //固定模板头 //居中、画边框、粗体、背景色为浅蓝 style = workbook.Styles[workbook.Styles.Add()]; style.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.ForegroundColor = System.Drawing.Color.FromArgb(191, 191, 191); style.Pattern = BackgroundType.Solid; style.Font.IsBold = true; //设置行高 //cells.SetRowHeight(0, 30); Cells cells = sheet.Cells; Aspose.Cells.Style Sstyle = workbook.Styles[workbook.Styles.Add()]; //列头设置统一样式 style.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0); Range dateDeatailRange = sheet.Cells.CreateRange(0, 0, 2, listParamIDAll.Count() + iBaseCell); StyleFlag dateDeatailFlg = new StyleFlag(); dateDeatailFlg.All = true; dateDeatailRange.ApplyStyle(style, dateDeatailFlg); sheet.Cells.Merge(rowNum, 0, 2, 1);//合并行 sheet.Cells[rowNum, 0].PutValue("站点名称"); sheet.Cells.Merge(rowNum, 1, 2, 1);//合并行 sheet.Cells[rowNum, 1].PutValue("MN"); sheet.Cells.Merge(rowNum, 2, 2, 1);//合并行 sheet.Cells[rowNum, 2].PutValue("监测时间"); dtDataAll.Columns.Add(new DataColumn("MNName", typeof(string))); dtDataAll.Columns.Add(new DataColumn("MN", typeof(string))); dtDataAll.Columns.Add(new DataColumn("DataTime", typeof(string))); int ik = iBaseCell; foreach (var pID in listParamIDAll) { var pObj = pExtInfo.FirstOrDefault(f => f.LHCodeID == pID); sheet.Cells[rowNum, ik].PutValue(pObj.ParamName + "(" + pObj.ParamUnit + ")"); sheet.Cells[rowNum + 1, ik].PutValue(GetParamUnitAndLevelA(pObj)); sheet.Cells[rowNum, ik].SetStyle(style); sheet.Cells[rowNum + 1, ik].SetStyle(style); dtDataAll.Columns.Add(new DataColumn(pObj.LHCodeID, typeof(string))); ik++; } sheet.Cells[rowNum, 0].SetStyle(style); sheet.Cells[rowNum + 1, 0].SetStyle(style); sheet.Cells[rowNum, 1].SetStyle(style); sheet.Cells[rowNum + 1, 1].SetStyle(style); sheet.Cells[rowNum, 2].SetStyle(style); sheet.Cells[rowNum + 1, 2].SetStyle(style); #endregion 表头 //组装数据 int totalThread = Environment.ProcessorCount - 2; Parallel.ForEach(listMN, new ParallelOptions() { MaxDegreeOfParallelism = totalThread > 6 ? 6 : totalThread }, (MN, loopState) => { var listProject = pExtInfo.Where(p => p.MN == MN); if (listProject == null || listProject.Count() < 1) return; List<string> listParamID = listProject.Select(s => s.LHCodeID).ToList(); DataTable dtHisData = new SQLServerDAL.MN_8051_MNDAL().Get805DataTable(MN, listParamID, dtStartTime, dtEndTime); DataTable dtData = dtDataAll.Clone(); string strDateTimeRecord = ""; DateTime dtNewTime; /*组装数据*/ DataRow drData = dtData.NewRow(); foreach (DataRow item in dtHisData.Rows) { if (strDateTimeRecord != item["DataTime"].ToString()) { if (DateTime.TryParse(strDateTimeRecord, out dtNewTime)) { drData["DataTime"] = dtNewTime.ToString("yyyy-MM-dd HH:mm"); drData["MN"] = MN; drData["MNName"] = listProject.First().MNName; dtData.Rows.Add(drData); } drData = dtData.NewRow(); strDateTimeRecord = item["DataTime"].ToString(); } drData[item["LHCodeID"].ToString()] = item["DataValue"].ToString() + (string.IsNullOrEmpty(item["DataFlag"].ToString()) ? "" : item["DataFlag"].ToString().ToUpper().Replace("N", "")); } dtHisData.Clear(); if (DateTime.TryParse(strDateTimeRecord, out dtNewTime)) { drData["DataTime"] = dtNewTime.ToString("yyyy-MM-dd HH:mm"); drData["MN"] = MN; drData["MNName"] = listProject.First().MNName; dtData.Rows.Add(drData); } lock (_objectGD) { dtDataAll.Merge(dtData); dtData.Clear(); } }); /*分页插入数据*/ int PageSize = 65500; int totalCount = Convert.ToInt32(dtDataAll.Rows.Count); int totalPage = Convert.ToInt32(Math.Ceiling((double)totalCount / PageSize)); var dtDataAllTemp = dtDataAll.AsEnumerable(); for (var i = 0; i < totalPage; i++) { Worksheet sheetMN = workbook.Worksheets.Add("数据列表" + (i + 1)); //复制表头 sheetMN.Cells.CopyRows(sheet.Cells, 0, 0, 2); sheetMN.FreezePanes(2, 3, 2, 3); DataTable dtNew = dtDataAllTemp.Skip(i * PageSize).Take(PageSize).CopyToDataTable(); //批量插入 int iCount = sheetMN.Cells.ImportDataTable(dtNew, false, "A3"); sheetMN.AutoFitColumns(); //自适应列宽 } dtDataAllTemp = null; dtDataAll.Clear(); workbook.Worksheets.RemoveAt(0); return workbook; }
