C# 未安裝Office環境下使用NPOI導出Excel檔案

1、NuGet直接安裝NPOI程式包；

2、

using NPOI.XSSF.UserModel;

3、導出Excel代碼：

private void TsbExport2ExcelClick(object sender, EventArgs e)
        {
            //擷取qrkey
            ToolStripButton tsbFiterBy = ((ToolStripButton)sender);
            long qryRKEY = Convert.ToInt64(tsbFiterBy.Name.Replace("TsbExport2Excel", string.Empty));
            QARRect qr = QryAndRptList.Find(q => q.QryRKEY == qryRKEY);
            if (qr == null)
            {
                MyMsg.Warning("沒有找到查詢定義條目,請關閉本窗體重試.");
                return;
            }

            A12DataGridView dgvAutoMain = (A12DataGridView)Controls.Find("DGVAutoMain" + qryRKEY, true)[0];
            if (dgvAutoMain == null) return;
            if (dgvAutoMain.Rows.Count <= 0)
            {
                MyMsg.Information("沒有需要導出的資料,請檢查.");
                return;
            }
            TabPage tabPage= (TabPage)Controls.Find("QryTpg" + qryRKEY, true)[0];
            string tagSheetName = tabPage.Text;
            string saveFileName = tabPage.Text + DateTime.Now.ToString("yyyy-MM-dd");

            var saveFileDialoge = new SaveFileDialog
            {
                FileName = saveFileName,
                Filter = "Excel Documents|*.xls;*.xlsx;*.xlsm",
                DefaultExt = ".xlsx"
            };
            if (saveFileDialoge.ShowDialog() != DialogResult.OK)
            {
                return;             
            }
            else 
            {
                saveFileName = saveFileDialoge.FileName;
                if (string.IsNullOrEmpty(saveFileName))
                {
                    return;
                }                    
            }
            if (File.Exists(saveFileName))
            {
                File.Delete(saveFileName);//因為在選取檔案名稱時就有提示是否覆寫，此處直接删除
            }
            //建立或比對一個BackgroundWorker,初始化一個耗時任務
            BackgroundWorker bgwk = new BackgroundWorker();
            BgwkDef bgwkDef = new BgwkDef()
            {
                RunningAction = delegate () { Export2Excel(bgwk,dgvAutoMain, saveFileName, tagSheetName); },
                TagBgwk = bgwk
            };
            BeginBgwork(bgwkDef);
        }      

導出按鈕代碼

private void Export2Excel(BackgroundWorker bgwk, A12DataGridView tagDGV, string fullSaveFileName, string tagSheetName)
        {
            if (string.IsNullOrEmpty(fullSaveFileName)) return;
            try
            {
                bgwk.ReportProgress(1, "正在準備文檔...");
                NPOI.SS.UserModel.IWorkbook tagWorkbook = new XSSFWorkbook();
                NPOI.SS.UserModel.ISheet tagSheet = tagWorkbook.CreateSheet(tagSheetName);//建立一個Sheet

                #region 标題行
                //建立标題行
                NPOI.SS.UserModel.IRow rowH = tagSheet.CreateRow(0);
                //建立單元格樣式
                NPOI.SS.UserModel.ICellStyle cellStyle = tagWorkbook.CreateCellStyle();
                //建立格式
                NPOI.SS.UserModel.IDataFormat dataFormat = tagWorkbook.CreateDataFormat();

                //設定為文本格式，也可以為 text，即 dataFormat.GetFormat("text");
                cellStyle.DataFormat = dataFormat.GetFormat("@");
                cellStyle.FillForegroundColor = NPOI.SS.UserModel.IndexedColors.Yellow.Index;
                cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
                cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Hair;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Hair;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Hair;
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Hair;
                //設定列名
                foreach (DataGridViewColumn col in tagDGV.Columns)
                {
                    //建立單元格并設定單元格内容
                    rowH.CreateCell(col.Index).SetCellValue(col.HeaderText);
                    rowH.Height = 20 * 20;

                    //設定單元格格式，寬度與Datagridview列寬比對
                    rowH.Cells[col.Index].CellStyle = cellStyle;
                    tagSheet.SetColumnWidth(col.Index, OString.NZ2Int(col.Width / 7.5 * 356));
                }
                tagSheet.CreateFreezePane(0, 1, 0, 1);
                #endregion

                bgwk.ReportProgress(2, "正在準備資料...");
                DataTable DTTmp = OData.GetDTFromObject(tagDGV.DataSource).Copy();

                bgwk.ReportProgress(50, "正在寫入資料到檔案中...");
                //寫入資料
                //建立一個單元格
                NPOI.SS.UserModel.ICell cell = null;
                int totColCount = DTTmp.Columns.Count;
                int totRowCount = DTTmp.Rows.Count;
                for (int i = 0; i < totRowCount; i++)
                {
                    //跳過第一行，第一行為列名
                    NPOI.SS.UserModel.IRow row = tagSheet.CreateRow(i + 1);
                    row.Height = 20* 20;
                    for (int j = 0; j < totColCount; j++)
                    {
                        cell = row.CreateCell(j);
                        #region 根據列類型寫入值
                        Type clmDataType = DTTmp.Columns[j].DataType;
                        if ((clmDataType == typeof(char)) || (clmDataType == typeof(Guid))|| (clmDataType == typeof(string)))
                        {
                            cell.SetCellValue(OString.NZ2Str(DTTmp.Rows[i][j]));
                        }
                        else if(clmDataType == typeof(bool))
                        {
                            cell.SetCellValue(OString.NZ2Bool(DTTmp.Rows[i][j]));
                        }
                        else if ((clmDataType == typeof(byte)) || (clmDataType == typeof(decimal)) || (clmDataType == typeof(double)) || (clmDataType == typeof(short)) || (clmDataType == typeof(int)) || (clmDataType == typeof(long)) || (clmDataType == typeof(sbyte)) || (clmDataType == typeof(float)) || (clmDataType == typeof(TimeSpan)) || (clmDataType == typeof(ushort)) || (clmDataType == typeof(uint)) || (clmDataType == typeof(ulong)))
                        {
                            cell.SetCellValue(OString.NZ2Double(DTTmp.Rows[i][j]));
                        }
                        else if(clmDataType == typeof(DateTime))
                        {
                            cell.SetCellValue(Convert.ToDateTime(OString.NZ2DateTime(DTTmp.Rows[i][j])));
                            //建立單元格樣式
                            NPOI.SS.UserModel.ICellStyle cellStyleValues = tagWorkbook.CreateCellStyle();
                            //建立格式
                            NPOI.SS.UserModel.IDataFormat dataFormatValues = tagWorkbook.CreateDataFormat();
                            if (cell.NumericCellValue.ToString().IndexOf('.') == -1)
                            {
                                cellStyleValues.DataFormat = dataFormatValues.GetFormat("yyyy-mm-dd");
                            }
                            else
                            {
                                cellStyleValues.DataFormat = dataFormatValues.GetFormat("yyyy-mm-dd hh:mm:ss");
                            }
                            //設定單元格格式
                            cell.CellStyle = cellStyleValues;
                        }
                        else
                        {
                            cell.SetCellValue(OString.NZ2Str(DTTmp.Rows[i][j]));
                        }
                        #endregion
                    }
                }

                bgwk.ReportProgress(95, "正在儲存檔案...");
                tagWorkbook.Write(new FileStream(fullSaveFileName, FileMode.Create, FileAccess.ReadWrite));

                bgwk.ReportProgress(100, "導出成功!");
                return;
            }
            catch (Exception ex)
            {
                CancelBgwork(bgwk);
                MyMsg.Exclamation("導出資料失敗，請檢查！",ex.Message);
                return;
            }
        }      

核心導出代碼

這2段摘自架構的通用資料分析平台，其中采用了背景導出任務對話框，可以參考其它文章。