C#大批量数据导出几种方式

1：NPOI导出

 　　　　/// <summary>
        /// 创建空EXCEL文档
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="bIsXls"></param>
        /// <param name="iSheetCount"></param>
        /// <param name="strSheetName"></param>
        private void CreateExcel(string fileName, bool bIsXls, int iSheetCount, string strSheetName)
        {
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }
            NPOI.SS.UserModel.IWorkbook workbook = null;
            if (bIsXls)
            {
                workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }
            else
            {
                workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
            }
            for (int i = 1; i <= iSheetCount; i++)
            {
                NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(strSheetName + i);
            }
            FileStream sw = File.Create(fileName);
            workbook.Write(sw);
            sw.Close();
        }

　　　　　/// <summary>
        /// 大批量数据导出
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="strSheetName">sheet名称</param>
        /// <param name="strFileName">文件名称（文件路径）</param>
        /// <param name="dHeaderRow">自定义表头</param>
        /// <param name="errorMsg">异常信息</param>
        /// <returns></returns>
        public bool ExportExcelByNPOI(System.Data.DataTable dt, string strSheetName, string strFileName, Dictionary<string, string> dHeaderRow, ref string errorMsg)
        {
            try
            {
                bool bIsXls = true;
                int maxPageLines = 65000; //一个Sheet导出的记录数  
                int rowIndex = 1;
                int index = 0;
                int sheetNum = 1;
                // 判断是否XLSX
                if (strFileName.Substring(strFileName.LastIndexOf('.')).Equals(".xlsx"))
                {
                    bIsXls = false;
                    maxPageLines = 1000000;
                }
                #region 创建临时文件
                int iSheetCount = dt.Rows.Count / maxPageLines + 1;
                CreateExcel(strFileName, bIsXls, iSheetCount, strSheetName);
                #endregion
                NPOI.SS.UserModel.IWorkbook workbook = null;
                NPOI.SS.UserModel.ISheet sheet = null;
                NPOI.SS.UserModel.IRow headerRow = null;
                List<int> headColumnIndexList = new List<int>();
                List<string> headColumn = new List<string>();
                int headerdColumnIndex = 0;
                FileStream fs = File.Open(strFileName, FileMode.Open);
                if (bIsXls)
                {
                    workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
                }
                else
                {
                    workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
                }
                foreach (System.Data.DataRow row in dt.Rows)
                {
                    if (index % maxPageLines == 0)
                    {
                        headColumnIndexList = new List<int>();
                        headColumn = new List<string>();
                        headerdColumnIndex = 0;
                        sheet = workbook.GetSheet(strSheetName + (sheetNum.ToString()));
                        sheetNum++;
                        headerRow = sheet.CreateRow(0);
                        if (dHeaderRow != null && dHeaderRow.Count > 0)
                        {
                            // 标头
                            foreach (System.Data.DataColumn column in dt.Columns)
                            {
                                if (dHeaderRow.Any(p => p.Key == column.Caption))
                                {
                                    string colos = dHeaderRow.First(p => p.Key == column.Caption).Value;
                                    if (!string.IsNullOrEmpty(colos))
                                    {
                                        headColumnIndexList.Add(headerdColumnIndex);
                                        headColumn.Add(colos);
                                    }
                                }
                                headerdColumnIndex++;
                            }
                            for (int i = 0; i < headColumnIndexList.Count; i++)
                            {
                                headerRow.CreateCell(i).SetCellValue(headColumn[i]);
                            }
                            var keyValuePairs = dHeaderRow.Where(p => !headColumn.Contains(p.Value)).ToList();
                            if (keyValuePairs.Count() > 0)
                            {
                                int lastColumns = headColumnIndexList.Count;
                                foreach (var item in keyValuePairs)
                                {
                                    lastColumns++;
                                    headerRow.CreateCell(lastColumns).SetCellValue(item.Value);
                                }
                            }
                        }
                        else
                        {
                            foreach (System.Data.DataColumn column in dt.Columns)
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
                            }
                        }
                        rowIndex = 1;
                    }
                    NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                    if (headColumnIndexList.Count > 0)
                    {
                        for (int i = 0; i < headColumnIndexList.Count; i++)
                        {
                            dataRow.CreateCell(i).SetCellValue(row[headColumnIndexList[i]].ToString());
                        }
                    }
                    else
                    {
                        foreach (System.Data.DataColumn column in dt.Columns)
                        {
                            if (column.DataType.FullName.Equals("System.Decimal"))
                            {
                                if (!String.IsNullOrEmpty(row[column].ToString()))
                                {
                                    dataRow.CreateCell(column.Ordinal).SetCellValue(double.Parse(row[column].ToString()));
                                }
                                else
                                {
                                    dataRow.CreateCell(column.Ordinal).SetCellValue(0);
                                }
                            }
                            else
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                            }
                        }
                    }
                    var keyValuePair = dHeaderRow.Where(p => !headColumn.Contains(p.Value)).ToList();
                    if (keyValuePair.Count() > 0)
                    {
                        int count = keyValuePair.Count();
                        for (int n = headColumnIndexList.Count + 1; n < headColumnIndexList.Count + count + 1; n++)
                        {
                            dataRow.CreateCell(n).SetCellValue("");
                        }
                    }
                    rowIndex++;
                    index++;
                }
                FileStream outFs = new FileStream(strFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);
                workbook.Write(outFs);
                outFs.Close();
            }
            catch (Exception ex)
            {
                errorMsg = ex.Message;
                return false;
            }
            return true;
   

2：直接导出

  /// <summary>
        /// 导出DataTable的数据到Excel
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="path">存放路径</param>
        public bool DataTabletoExcel(DataTable dt, string path, Dictionary<string, string> colosHead, ref string errorMsg)
        {
            try
            {
                StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));
                List<int> headColumnIndex = new List<int>();
                List<string> headColumn = new List<string>();
                //加载表头
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string columnName = dt.Columns[i].ColumnName.ToString();
                    if (colosHead != null && colosHead.Count > 0)
                    {
                        if (colosHead.Any(p => p.Key == columnName))
                        {
                            string colos = colosHead.First(p => p.Key == columnName).Value;
                            if (!string.IsNullOrEmpty(colos))
                            {
                                headColumnIndex.Add(i);
                                headColumn.Add(colos);
                                sw.Write(colos + "\t");
                            }
                        }
                    }
                    else
                    {
                        sw.Write(columnName + "\t");
                    }
                }
                var keyValuePairs = colosHead.Where(p => !headColumn.Contains(p.Value)).ToList();
                if (keyValuePairs.Count() > 0)
                {
                    foreach (var item in keyValuePairs)
                    {
                        sw.Write(item.Value + "\t");
                    }
                }
                sw.Write(Environment.NewLine);
                for (int m = 0; m < dt.Rows.Count; m++)
                {
                    if (headColumnIndex.Count > 0)
                    {
                        foreach (var item in headColumnIndex)
                        {
                            sw.Write(dt.Rows[m][item].ToString() + "\t");
                        }
                    }
                    else
                    {
                        for (int n = 0; n < dt.Columns.Count; n++)
                        {
                            sw.Write(dt.Rows[m][n].ToString() + "\t");
                        }
                    }
                    sw.Write(Environment.NewLine);
                }
                if (keyValuePairs.Count() > 0)
                {
                    int count = keyValuePairs.Count();
                    for (int m = 0; m < dt.Rows.Count; m++)
                    {
                        for (int n = dt.Columns.Count; n < dt.Columns.Count + count; n++)
                        {
                            sw.Write("" + "\t");
                        }
                        sw.Write(Environment.NewLine);
                    }
                }
                sw.Flush();
                sw.Close();
                return true;
            }
            catch (Exception ex)
            {
                errorMsg = "生成Excel表失败,原因：" + ex.Message;
                return false;
            }
        }
备注：此方式导出会存在问题，因为数据格式的问题