C#使用Npoi匯出Excel併合並行列

2022-02-21 16:01:12

在工作開發中，客戶經常要求資料庫中資料匯出到Excel表格。以前方法是參照office相關元件，如果客戶沒有安裝office，功能就會遇到問題。

現在用Npoi匯出Excel，匯出表格是合併行列，如圖：

匯出的要求：合計列要進行合併，序號一致的要合併。最後一行要合併列。
因為相同序號數量不是固定的，要動態算合併的行數。

合併行列介面：XXX.AddMergedRegion(new CellRangeAddress(開始行, 最後一行, 開始列, 最後一列));

隱藏指定：sheet.SetColumnHidden(cellIndex, true);

參照元件：

NPOI.dll；
NPOI.OOXML.dll；
NPOI.OpenXml4Net.dll；
NPOI.OpenXmlFormats.dll；
ICSharpCode.SharpZipLib.dll；

程式碼如下：

/// <summary>
/// 
/// </summary>
/// <param name="dtSource">資料來源</param>
/// <param name="strFileName">儲存路徑</param>
/// <param name="dvXH">序號</param>
public void Export(DataTable dtSource,string strFileName,DataView dvXH=null)
        {
            //建立工作簿 office2007以上
            XSSFWorkbook workbook = new XSSFWorkbook();
            //為工作簿建立工作表並命名
            ISheet sheet = workbook.CreateSheet("商品表");

            ICellStyle dateStyle = workbook.CreateCellStyle();

            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            #region 表頭及樣式
            int cellIndex = 0;
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < dtSource.Columns.Count; i++)
            {
                #region MyRegion
                string ColumnsName = dtSource.Columns[i].ToString();
                if (dtSource.Columns[i].ColumnName.EndsWith("XH"))
                {
                    ColumnsName = "序號";
                    sheet.SetColumnWidth(cellIndex, 3000);
                   //sheet.SetColumnHidden(cellIndex, true);隱藏指定列
                }
                else if (dtSource.Columns[i].ColumnName.EndsWith("GoogName"))
                {
                    ColumnsName = "商品名稱";
                    sheet.SetColumnWidth(cellIndex,10000);//設定列寬
                }
                else if (dtSource.Columns[i].ColumnName.EndsWith("Num"))
                {
                    ColumnsName = "數量";
                    sheet.SetColumnWidth(cellIndex, 5000);
                }

                else if (dtSource.Columns[i].ColumnName.EndsWith("Summation"))
                {
                    ColumnsName = "合計（元）";
                    sheet.SetColumnWidth(cellIndex, 5000);
                }
                #endregion

                //設定行高
                headerRow.HeightInPoints = 35; 
                headerRow.CreateCell(cellIndex).SetCellValue(ColumnsName);

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.WrapText = true;
                IFont font = workbook.CreateFont();
                //字型大小
                font.FontHeightInPoints = 12;
                font.Boldweight = 360;
                headStyle.SetFont(font);
                headerRow.GetCell(cellIndex).CellStyle = headStyle;
                cellIndex++;
            }
            #endregion

            int rowIndex = 1;//行數一定要從1行開始
            int count = 1;
            int startRow = 1;
            DataView dvSource = dtSource.DefaultView;
            if (dvXH!=null)
            {
                foreach (DataRowView drv in dvXH)
                {//1-10.11-12,13-14,15-16
                    int rowcout = 0;
                    dvSource.RowFilter = "XH='" + drv["XH"] + "'";
                    foreach (DataRowView row in dvSource)
                    {
                        #region 填充內容
                        IRow dataRow = sheet.CreateRow(rowIndex);

                        //序號
                        ICell newCel0 = dataRow.CreateCell(0);
                        ICellStyle style0 = workbook.CreateCellStyle();
                        style0.DataFormat = format.GetFormat("text");
                        newCel0.SetCellValue(row["XH"].ToString());

                        //標的名稱
                        ICell newCel2 = dataRow.CreateCell(1);
                        ICellStyle style2 = workbook.CreateCellStyle();
                        style2.DataFormat = format.GetFormat("text");
                        newCel2.SetCellValue(row["GoogName"].ToString());

                        //標的數量
                        ICell newCel4 = dataRow.CreateCell(2);
                        ICellStyle style4 = workbook.CreateCellStyle();
                        style4.DataFormat = format.GetFormat("text");
                        newCel4.SetCellValue(row["Num"].ToString());

                        //合計（元）
                        ICell newCel8 = dataRow.CreateCell(3);
                        ICellStyle style8 = workbook.CreateCellStyle();
                        style8.DataFormat = format.GetFormat("text");
                        newCel8.SetCellValue(row["Summation"].ToString());
                        #endregion

                        rowIndex++;
                        rowcout++;
                    }

                    if (count == 1)
                    {
                        //合併行數
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, rowcout, 3, 3));
                        startRow = startRow + rowcout;
                    }
                    else
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, startRow + rowcout - 1, 3, 3));
                        startRow = startRow + rowcout;
                    }
                    count++;
                }
            }
            else
            {
                #region MyRegion
                foreach (DataRowView row in dvSource)
                {
                    #region 填充內容
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    //序號
                    ICell newCel0 = dataRow.CreateCell(0);
                    ICellStyle style0 = workbook.CreateCellStyle();
                    style0.DataFormat = format.GetFormat("text");
                    newCel0.SetCellValue(row["XH"].ToString());
              
                    //商品名稱
                    ICell newCel1 = dataRow.CreateCell(1);
                    ICellStyle style1 = workbook.CreateCellStyle();
                    style1.DataFormat = format.GetFormat("text");
                    newCel1.SetCellValue(row["GoogName"].ToString());

                    //數量
                    ICell newCel2 = dataRow.CreateCell(2);
                    ICellStyle style2 = workbook.CreateCellStyle();
                    style2.DataFormat = format.GetFormat("text");
                    newCel2.SetCellValue(row["Num"].ToString());
                   
                    //合計（元）
                    ICell newCel3 = dataRow.CreateCell(3);
                    ICellStyle style3 = workbook.CreateCellStyle();
                    style3.DataFormat = format.GetFormat("text");
                    newCel3.SetCellValue(row["Summation"].ToString());
                    #endregion
                    rowIndex++;
                }
                #endregion
            }
            #region 拼接最後一行
            IFont fontLast = workbook.CreateFont();
            fontLast.FontHeightInPoints = 30;
            fontLast.Boldweight = 480;
            IRow dataRowLast = sheet.CreateRow(rowIndex);
            dataRowLast.HeightInPoints = 40;
            ICell newCelLast = dataRowLast.CreateCell(0);
            ICellStyle styleLast = workbook.CreateCellStyle();
            styleLast.DataFormat = format.GetFormat("text");
            styleLast.SetFont(fontLast);
            newCelLast.SetCellValue("製作人：張三");
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 3));
            #endregion

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
}

實際運用中，涉及到資料，方法中有很多校驗等操作，方法直觀可讀性不是太好，下面附上簡單匯出的方法：

實際上匯出Excel，總結有幾點：

1、參照相關元件
2、建立一個工作簿，建立工作表並命名；
3、設定表頭及樣式；
4、填充資料；
5、儲存資料到指定位置；

/// <summary>
/// 簡單匯出資料
/// </summary>
/// <param name="dtSource">資料來源</param>
/// <param name="strFileName">儲存路徑</param>
/// <param name="dvXH">序號</param>
        public void Export1(DataTable dtSource, string strFileName)
        {
            //建立工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            //為工作簿建立工作表並命名
            ISheet sheet = workbook.CreateSheet("商品表");
            IDataFormat format = workbook.CreateDataFormat();

            #region 表頭及樣式
            int cellIndex = 0;
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < dtSource.Columns.Count; i++)
            {
                //設定行高
                headerRow.HeightInPoints = 35;
                headerRow.CreateCell(cellIndex).SetCellValue(dtSource.Columns[i].ToString());
                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.WrapText = true;
                IFont font = workbook.CreateFont();
                //字型大小
                font.FontHeightInPoints = 12;
                font.Boldweight = 360;
                headStyle.SetFont(font);
                headerRow.GetCell(cellIndex).CellStyle = headStyle;
                cellIndex++;
            }
            #endregion

            #region 資料填充
            int rowIndex = 1;//行數一定要從1行開始，因為上面已經建立了表頭為0行;
            DataView dvSource = dtSource.DefaultView;

            foreach (DataRow row in dtSource.Rows)
            {
                int ColumnIndex = 0;
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    //序號
                    ICell newCel0 = dataRow.CreateCell(ColumnIndex);
                    ICellStyle style0 = workbook.CreateCellStyle();
                    style0.DataFormat = format.GetFormat("text");//資料型別
                    newCel0.SetCellValue(row[column.ColumnName].ToString());
                    ColumnIndex++;
                }
                rowIndex++;
            }
            #endregion

            #region 儲存到指定位置
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
            #endregion

}

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支援it145.com。

C#使用Npoi匯出Excel併合並行列

熱門文章