• 上万级大批量数据导出Excel（多方案）

    大批量数据Excel

    比如数据量一大（比如上万条以上的记录），用传统的方式和插件等导出excel速度都很慢，甚至最终导致内存益出；往文本文件直接插入记录的方式(速度快，占内存也少)，然后用 割开代表一列，产生的文件，直接用excel打开就可以，单只支持单个sheet页，office2003(xls)限制一页65536行，需要分割为多个文件，具体如下：

    String path="c:/test.xls";//数据存放的位置
BufferedWriter buff = new BufferedWriter(new FileWriter(path));//生成文件
//插入标题
buff.write("部门名称	用户	电话");//代表3列
buff.write("
");//换行
//插入5万条记录
for (int i = 0; i < 50000; i++) {
   buff
     .write("部门	小吴	123456");
   buff.write("
");
  }
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
" +
                "X-Document-Type: Workbook
" +
                "Content-Type: multipart/related; boundary="-=BOUNDARY_EXCEL"

" +
                "---=BOUNDARY_EXCEL
" +
                "Content-Type: text/html; charset="gbk"

" +
                "<html xmlns:o="urn:schemas-microsoft-com:office:office"
" +
                "xmlns:x="urn:schemas-microsoft-com:office:excel">

" +
                "<head>
" +
                "<xml>
" +
                "<o:DocumentProperties>
" +
                "<o:Author>{0}</o:Author>
" +
                "<o:LastAuthor>{0}</o:LastAuthor>
" +
                "<o:Created>{1}</o:Created>
" +
                "<o:LastSaved>{1}</o:LastSaved>
" +
                "<o:Company>{2}</o:Company>
" +
                "<o:Version>11.5606</o:Version>
" +
                "</o:DocumentProperties>
" +
                "</xml>
" +
                "<xml>
" +
                "<x:ExcelWorkbook>
" +
                "<x:ExcelWorksheets>
"
               , Author
               , DateTime.Now.ToString()
               , Company);

        foreach (var d in dt)
        {
            string gid = Guid.NewGuid().ToString();
            sbBody.AppendFormat("<x:ExcelWorksheet>
" +
                "<x:Name>{0}</x:Name>
" +
                "<x:WorksheetSource HRef="cid:{1}"/>
" +
                "</x:ExcelWorksheet>
"
                , d.TableName.Replace(":", "").Replace("\", "").Replace("/", "").Replace("?", "").Replace("*", "").Replace("[", "").Replace("]", "").Trim()
                , gid);


            sbSheet.AppendFormat(
             "---=BOUNDARY_EXCEL
" +
             "Content-ID: {0}
" +
             "Content-Type: text/html; charset="gbk"

" +
             "<html xmlns:o="urn:schemas-microsoft-com:office:office"
" +
             "xmlns:x="urn:schemas-microsoft-com:office:excel">

" +
             "<head>
" +
             "<xml>
" +
             "<x:WorksheetOptions>
" +
             "<x:ProtectContents>False</x:ProtectContents>
" +
             "<x:ProtectObjects>False</x:ProtectObjects>
" +
             "<x:ProtectScenarios>False</x:ProtectScenarios>
" +
             "</x:WorksheetOptions>
" +
             "</xml>
" +
             "</head>
" +
             "<body>
"
             , 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>
" +
                "</html>

");
        }

        StringBuilder sb = new StringBuilder(sbBody.ToString());

        sb.Append("</x:ExcelWorksheets>
" +
            "</x:ExcelWorkbook>
" +
           "</xml>
" +
            "</head>
" +
            "</html>

");

        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;
}
  • 相关阅读:
    Node.js安装及环境配置（windows）
    table
    检测浏览器
    ickeck插件
    全国三级联动
    css3-calc用法
    jQuery Portamento 滑动定位
    canvas版《俄罗斯方块》
    canvas入门级小游戏《开关灯》思路讲解
    css3 matrix 2D矩阵和canvas transform 2D矩阵
  • 原文地址：https://www.cnblogs.com/elves/p/12523075.html
Copyright © 2020-2023  润新知