C#將DataTable的數據導出到Excel

將DataTable的數據導出到Excel

可自動生成多張Sheet

NPOI可不依賴Excel客戶端

using System;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Configuration;
using System.Text;
using NPOI.HPSF;

namespace WindowsFormsApp1.Common
{
    class ExcelHelper
    {
        /// <summary>  
        ///保存文件XLS(多sheet)
        /// </summary>  
        /// <param name="dt"></param>  
        /// <param name="file"></param>  
        public static string TableToExcelForXLSHead(DataTable dt, string file)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                #region 右擊檔案 屬性資訊
                {
                    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                    dsi.Company = "NPOI";
                    workbook.DocumentSummaryInformation = dsi;
                    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                    si.Author = "檔案作者資訊"; //填加xls檔案作者資訊
                    si.ApplicationName = "建立程式資訊"; //填加xls檔案建立程式資訊
                    si.LastAuthor = "最後儲存者資訊"; //填加xls檔案最後儲存者資訊
                    si.Comments = "作者資訊"; //填加xls檔案作者資訊
                    si.Title = "標題資訊"; //填加xls檔案標題資訊
                    si.Subject = "主題資訊";//填加檔案主題資訊
                    si.CreateDateTime = System.DateTime.Now;
                    workbook.SummaryInformation = si;
                }
                #endregion
                ICellStyle dateStyle = workbook.CreateCellStyle();
                IDataFormat format = workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                // 取得列寬
                int[] arrColWidth = GetWidth(dt);

                int rowIndex = 0;
                foreach (DataRow row in dt.Rows)
                {
                    #region 新建表，填充表頭，填充列頭，樣式
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }
                        #region 表頭及樣式
                        {
                            IRow headerRow = sheet.CreateRow(0);
                            headerRow.HeightInPoints = 25;
                            headerRow.CreateCell(0).SetCellValue("NSD01");
                            ICellStyle headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = HorizontalAlignment.Center;
                            IFont font = workbook.CreateFont();
                            font.FontHeightInPoints = 20;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);
                            headerRow.GetCell(0).CellStyle = headStyle;
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
                        }
                        #endregion
                        #region 列頭及樣式
                        {
                            IRow headerRow = sheet.CreateRow(1);
                            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;
                                //設定列寬
                                sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal]) * 256);
                            }
                        }
                        #endregion
                        rowIndex = 2;
                    }
                    #endregion
                    #region 填充內容
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dt.Columns)
                    {
                        ICell newCell = dataRow.CreateCell(column.Ordinal);
                        //newCell.SetCellValue(row[column].ToString());
                        string drValue = row[column].ToString();
                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字串型別
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期型別
                                System.DateTime dateV;
                                System.DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = dateStyle;//格式化顯示
                                break;
                            case "System.Boolean"://布林型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮點型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值處理
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }
                    }
                    #endregion
                    rowIndex++;
                }
 
                string strFileName = CreateFile("Excel/" + DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.ToString("MM-dd HHmm") + file, ".xls");
                using (var fs = File.Create(strFileName))
                {
                    workbook.Write(fs);
                    return "OK," + strFileName;
                }
            }
            catch (Exception ex)
            {
                return "Fail:" + ex.Message;
                throw;
            }
        }

        /// <summary>  
        /// 保存文件XLS (多sheet)
        /// </summary>  
        /// <param name="dt"></param>  
        /// <param name="file"></param>  
        public static string TableToExcelForXLSNoHead(DataTable dt, string file)
        {
            try
            {

                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                
                
                //cell設置文字居中
                ICellStyle newCellStyle = workbook.CreateCellStyle();
                newCellStyle.Alignment = HorizontalAlignment.Center;


                //取得列寬
                int[] arrColWidth = GetWidth(dt);
                 

                int rowIndex = 0;
                foreach (DataRow dataRow in dt.Rows)
                {
                    #region 新建表，填充表頭，填充列頭，樣式
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }
                        #region 列頭及樣式
                        {
                            IRow headerRow = sheet.CreateRow(0);


                            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);

                                //ICell cell = row.CreateCell(i);
                                //cell.SetCellValue(dt.Columns[i].ColumnName);

                                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                                //設定列寬
                                sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal]) * 256);
                            }

                        }
                        #endregion

                        rowIndex = 1;
                    }

                    #region 填充內容
                    {
                        IRow row1 = sheet.CreateRow(rowIndex);

                        foreach (DataColumn column in dt.Columns)
                        {
                            ICell newCell = row1.CreateCell(column.Ordinal);
                             
                            newCell.SetCellValue(dataRow[column].ToString());
                            newCell.CellStyle = newCellStyle; 

                        }
                    }

                    #endregion

                    rowIndex++;  
                    #endregion
                } 

                string strFileName = CreateFile("Excel/" + DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.ToString("MM-dd HHmm") + file, ".xls");

                using (var fs = File.Create(strFileName))
                {
                    workbook.Write(fs);
                    return "OK," + strFileName;
                }
            }
            catch (Exception ex)
            {
                return "Fail:" + ex.Message;
                throw;
            }

        }

        /// <summary>  
        /// 将DataTable数据导出到Excel文件中(xls) ,一張sheet65536 (多sheet,sheet有限制)
        /// </summary>  
        /// <param name="dt"></param>  
        /// <param name="file"></param>  
        public static string TableToExcelForXLS(DataTable dt, string file)
        {
            try
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();

                ISheet sheet = hssfworkbook.CreateSheet(file);
                //表头  
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }
                //数据  
                //Auto(1, hssfworkbook, dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i < 65535)
                    {
                        IRow row1 = sheet.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = row1.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }
                    }
                    else
                    {
                        Auto(i, hssfworkbook, dt);
                        break;
                    }
                }
                if (!Directory.Exists(ConfigurationSettings.AppSettings["PATH"]))
                {
                    Directory.CreateDirectory(ConfigurationSettings.AppSettings["PATH"]);
                }
                string strFileName = ConfigurationSettings.AppSettings["PATH"] + DateTime.Now.ToString("yyyy-MM-dd-24hhmm") + file + ".xls";

                using (var fs = File.Create(strFileName))
                {
                    hssfworkbook.Write(fs);
                    return "OK," + strFileName;
                }
            }
            catch (Exception ex)
            {
                return "Fail:" + ex.Message;
                throw;
            }

        }


        /// <summary>
        /// 自動分頁sheet
        /// </summary>
        /// <param name="i"></param>
        /// <param name="hssfworkbook"></param>
        /// <param name="dt"></param>
        private static void Auto(int i, HSSFWorkbook hssfworkbook, DataTable dt)
        {

            //object[] str = new object[] { "sheet1", "sheet2", "sheet3" };
            //ISheet[] obj = new ISheet[] { (ISheet)str[0] };
            ISheet[] arrayA = new ISheet[10];

            for (int k = 1; k <= dt.Rows.Count / 65536; k++, i++)
            {
                arrayA[k] = hssfworkbook.CreateSheet("sheet" + k.ToString());
                for (; i < dt.Rows.Count; i++)
                {
                    IRow row1 = arrayA[k].CreateRow(i - (65536 * k - 1));
                    //IRow row1 = arrayA[k].CreateRow(i - (65536 * k - 1 + y));
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                    if (i - (65536 * k - 1) >= 65535)
                    {
                        break;
                    }
                }
            }

        }


        /// <summary>  
        /// 将DataTable数据导出到Excel文件中(xlsx)  
        /// </summary>  
        /// <param name="dt"></param>  
        /// <param name="file"></param>  
        public static string TableToExcelForXLSX(DataTable dt, string file)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet sheet = xssfworkbook.CreateSheet(file);
            //表头  
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }
            //数据  
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            if (!Directory.Exists(ConfigurationSettings.AppSettings["PATH"]))
            {
                Directory.CreateDirectory(ConfigurationSettings.AppSettings["PATH"]);
            }
            string strFileName = ConfigurationSettings.AppSettings["PATH"] + DateTime.Now.ToString("yyyy-MM-dd") + file + ".xlsx";
            using (var fs = File.Create(strFileName))
            {
                xssfworkbook.Write(fs);
                //自动列宽
                for (int i = 0; i <= dt.Columns.Count; i++)
                {
                    sheet.AutoSizeColumn(i, true);
                }
                return "OK," + strFileName;
            } 
        }


        /// <summary>
        /// 創建文件
        /// </summary>
        public static string CreateFile(string folder, string fileName, string fileExtension)
        {
            FileStream fs = null;
            string filePath = folder + "/" + fileName + "." + fileExtension;
            try
            {
                if (!Directory.Exists(folder))
                {
                    Directory.CreateDirectory(folder);
                }
                fs = File.Create(filePath);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (fs != null)
                {
                    fs.Dispose();
                }
            }
            return filePath;
        }

        /// <summary>
        /// 取得列寬
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static int[] GetWidth(DataTable dt)
        {
            #region 取得列寬  
            int[] arrColWidth = new int[dt.Columns.Count];
            foreach (DataColumn item in dt.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length + 1;
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length + 1;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            return arrColWidth;
            #endregion
        }

        /// <summary>
        /// 保存文件CSV,數據本身不能帶有逗號，數據會亂
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static bool SaveDataToCSVFile(DataTable dt, string filePath)
        {

            bool successFlag = true;
            StringBuilder strColumn = new StringBuilder();
            StringBuilder strValue = new StringBuilder();
            StreamWriter sw = null; 

            try
            {               
                using (StreamWriter streamWriter = new StreamWriter(filePath, false, System.Text.Encoding.UTF8))
                {
                    //表头   
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        strColumn.Append(dt.Columns[i].ColumnName);
                        strColumn.Append(",");
                    }

                    strColumn.Remove(strColumn.Length - 1, 1);
                    streamWriter.WriteLine(strColumn);    //write the column name 

                    //數據
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (i < 1000000)
                        {
                            strValue.Remove(0, strValue.Length); //clear the temp row value                                                                           

                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                strValue.Append(dt.Rows[i][j].ToString());
                                strValue.Append(",");
                            }

                            strValue.Remove(strValue.Length - 1, 1);
                            streamWriter.WriteLine(strValue);//write the data
                        }
                        else
                        {
                            //Auto(i, hssfworkbook, dt);
                            break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                successFlag = false;
            }
            finally
            {
                if (sw != null)
                {
                    sw.Dispose();
                }
            }
            return successFlag;
        }

         
    }
}