批量拼脚本神器

做事中要给产品经营写各类脚本拉数据、纠正数据。这种批量是拼sql,Excel当然是最合适的。可是苦于Excel玩不转,早前向来用Visual
Studio Code
的多主旨编辑成效,即使如此,那在同事眼中已然是神器了。然则当遭逢NimberText现在,VS
Code也要现在排了。
如何也不说了,直接上海体育场合:
www.350.vip 1

NPOI导出:

www.350.vip ,越来越多效果与利益见官网
晋升:官方网站无偿版已经去掉了Where条件部分,已经满意大多数常常性操作。假诺需要,可选拔dnspy进行破解,作者风华正茂度打响破解完成,但不便民提供破解版。

www.350.vip 2www.350.vip 3

累积NPOI、EPPlus类库dll使用的是NuGet增多。

NPIO官方网站地址:

 

private static MemoryStream ExportXlsx(DataTable dt)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = null;

            int headRowIndex = 0;
            string sheetName = "Sheet1";
            if (!string.IsNullOrEmpty(dt.TableName))
            {
                sheetName = dt.TableName;
            }
            sheet = workbook.CreateSheet(sheetName);
            int rowIndex = 0;

            #region 列头及样式
            {
                XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.Center;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);

                foreach (DataColumn column in dt.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                }
            }
            #endregion

            #region 填充内容

            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    string drValue = row[column].ToString();
                    dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
                }
            }
            #endregion


            MemoryStream ms = new MemoryStream();

            workbook.Write(ms);
            ms.Flush();

            return ms;
        }

        public static void ExportXlsxByWeb(DataTable dt, string strFileName)
        {

            HttpContext curContext = HttpContext.Current;

            MemoryStream ms = ExportXlsx(dt);

            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");
            curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
            curContext.Response.ContentEncoding = Encoding.UTF8;

            curContext.Response.BinaryWrite(ms.ToArray());
            ms.Close();
            ms.Dispose();
            curContext.Response.End();

        }

View Code

/// <summary>
        /// 使用EPPlus导出Excel(xlsx)
        /// </summary>
        /// <param name="sourceTable">数据源</param>
        /// <param name="strFileName">xlsx文件名(不含后缀名)</param>
        public static void ExportByEPPlus(DataTable sourceTable, string strFileName)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName;
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);

                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(sourceTable, true);

                //Format the row
                ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
                Color borderColor = Color.FromArgb(155, 155, 155);

                using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count])
                {
                    rng.Style.Font.Name = "宋体";
                    rng.Style.Font.Size = 10;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));

                    rng.Style.Border.Top.Style = borderStyle;
                    rng.Style.Border.Top.Color.SetColor(borderColor);

                    rng.Style.Border.Bottom.Style = borderStyle;
                    rng.Style.Border.Bottom.Color.SetColor(borderColor);

                    rng.Style.Border.Right.Style = borderStyle;
                    rng.Style.Border.Right.Color.SetColor(borderColor);
                }

                //Format the header row
                using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count])
                {
                    rng.Style.Font.Bold = true;
                    rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
                }

                //Write it back to the client
                HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));
                HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;

                HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
                HttpContext.Current.Response.End();
            }
        }
条数 NPOI EPPlus
40000 成功生成 成功生成
50000 失败 成功生成
230000 失败 失败
     
     
     
     
     

View Code

系统中时时会动用导出Excel的功能。

EPPlus官方网站地址:

发表评论

电子邮件地址不会被公开。 必填项已用*标注