• 上万级大批量数据导出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;
    }
  • 相关阅读:
    利用BitLocker和vhdx创建一个有加密的Win10系统
    macOS 10.12 任何来源
    Xcode 8 GM 编译缺失 /Users/usr/lib/libresolv.9.dylib
    基于inline-block的列表布局
    markdown 的基本操作
    easyui1.32 各种问题汇总
    angular笔记
    underscore 笔记
    我的问道游戏主题皮肤
    在bootstrap ace样式框架上修改的后台管理型模板(Tab页后台管理模板)
  • 原文地址:https://www.cnblogs.com/elves/p/12523075.html
Copyright © 2020-2023  润新知