大猫眼石
上万级⼤批量数据导出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.clo(); //关闭⽂件操作
多页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; chart=\"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; chart=\"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>Fal</x:ProtectContents>\r\n" +
"<x:ProtectObjects>Fal</x:ProtectObjects>\r\n" +
"<x:ProtectScenarios>Fal</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.Respon.Clear();
HttpContext.Current.Respon.ClearContent();
HttpContext.Current.Respon.ClearHeaders();
HttpContext.Current.Respon.Buffer = true;
HttpContext.Current.Respon.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
装修合同怎么写HttpContext.Current.Respon.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Respon.ContentEncoding = System.Text.Encoding.GetEncoding("gbk");
HttpContext.Current.Respon.Write(sb.ToString());
HttpContext.Current.Respon.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>
Apo.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 iBaCell = 1;
Aspo.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;
Aspo.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() + iBaCell);
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 = iBaCell;
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.TryPar(strDateTimeRecord, out dtNewTime))
关于看的词语{
drData["DataTime"] = dtNewTime.ToString("yyyy-MM-dd HH:mm");
dtData.Rows.Add(drData);
}
drData = dtData.NewRow();
养老金交15年能领多少
strDateTimeRecord = item["DataTime"].ToString();
}
drData[item["LHCodeID"].ToString()] = item["DataValue"].ToString() + (string.IsNullOrEmpty(item["Dat
aFlag"].ToString()) ? "" : item["DataFlag"].ToString().ToUpper().Replace("N", "")); }
if (DateTime.TryPar(strDateTimeRecord, out dtNewTime))
{
drData["DataTime"] = dtNewTime.ToString("yyyy-MM-dd HH:mm");
dtData.Rows.Add(drData);
}
//批量插⼊
int iCount = cells.ImportDataTable(dtData, fal, "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 iBaCell = 3;
Aspo.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;
Aspo.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() + iBaCell);
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 = iBaCell;
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();
