将数据导出到excel java_数据无法导出到excel[通俗易懂]

将数据导出到excel java_数据无法导出到excel[通俗易懂]最近在做一个项目,涉及到将数据导出到excel,因此花了一天多时间,从网上查询了一些相关方法:下面做一下总结:下面这个项目里面的步骤是本人已经成功应用到项目中的。第一步骤:将NPOIDLL文件引用到项目中:然后在项目引中会出现usingNPOI.SS.UserModel;usingNPOI.HSSF.UserModel;usingNPOI.XSSF.UserModel;…_程序员将系统中的数据导出为excel

最近在做一个项目,涉及到将数据导出到excel,因此花了一天多时间,从网上查询了一些相关方法:下面做一下总结:
下面这个项目里面的步骤是本人已经成功应用到项目中的。
第一步骤:将NPOI DLL文件引用到项目中:
在这里插入图片描述
在这里插入图片描述
然后在项目引中会出现
在这里插入图片描述

using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

第二步:打开保存文件夹

			SaveFileDialog sfd = new SaveFileDialog();

            //设置文件类型 
            sfd.Filter = "Excel文件(*.xls)|*.xls";

            //设置默认文件类型显示顺序 
            sfd.FilterIndex = 1;

            //保存对话框是否记忆上次打开的目录 
            sfd.RestoreDirectory = true;

            //设置默认的文件名
            DateTime dataNow = DateTime.Now;
            string fileName = "XXXXXx" + dataNow.ToString("yyyy-MM-dd-HH-mm-ss");
            sfd.FileName = fileName;
            string localFilePath = null;

            //点了保存按钮进入 
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                localFilePath = sfd.FileName.ToString(); //获得文件路径 
                //string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名,不带路径
            }
            else
            {
                return;
            }

第三步:

 			IWorkbook wb = new HSSFWorkbook();//建立一个excel文件
            //创建一个工作簿
            ISheet sh = wb.CreateSheet(captionName);

            #region 表头(第1行)
            //合并单元格
            sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, propDictionary.Count - 1));
            //创建第1行
            IRow row0 = sh.CreateRow(0);
            //设置第1行高度
            row0.Height = 20 * 20;
            //创建第1行第1列
            ICell icell1top0 = row0.CreateCell(0);
            //设置第1行第1列格式
            icell1top0.CellStyle = Getcellstyle(wb, "head");
            //设置第1行第1列内容
            icell1top0.SetCellValue(captionName);
            #endregion

            #region 抬头(第2行)
            //创建第2行
            IRow row1 = sh.CreateRow(1);
            //设置高度
            row1.Height = 20 * 20;
            //columnt_index是列的索引
            int columnt_index = 0;
            ICellStyle style = Getcellstyle(wb, "");
            foreach (var itemtmp in propDictionary)
            {
                //获取T的字段名称
                string name = itemtmp.Key.ToString();
                //创建第2行的第columnt_index列
                ICell icell1top = row1.CreateCell(columnt_index);
                //设置第2行的第columnt_index列的格式
                icell1top.CellStyle = style;
                //设置第2行的第columnt_index列的内容
                icell1top.SetCellValue(name);
                //设置第2行的第columnt_index列的宽度
                sh.SetColumnWidth(columnt_index, (int)((15 + 0.72) * 256));
                columnt_index++;
            }
            #endregion  

            
            #region 这里是data具体内容
            //创建第3行
            int rownums = 2;
            foreach (var item in resultToXLS.Items)
            {
                IRow row_zs = sh.CreateRow(rownums);
                //创建第1列、格式、字段值
                ICell icell1top = row_zs.CreateCell(0);
                icell1top.CellStyle = style;
                icell1top.SetCellValue(item.ID.ToString());

                ICell icell1top1 = row_zs.CreateCell(1);
                icell1top1.CellStyle = style;
                icell1top1.SetCellValue(item.PlateNum);

                ICell icell1top2 = row_zs.CreateCell(2);
                icell1top2.CellStyle = style;
                icell1top2.SetCellValue(item.PlateColor);

                ICell icell1top3 = row_zs.CreateCell(3);
                icell1top3.CellStyle = style;
                icell1top3.SetCellValue(item.PassTime.ToString("yyyy-MM-dd HH:mm:ss"));

                ICell icell1top4 = row_zs.CreateCell(4);
                icell1top4.CellStyle = style;
                icell1top4.SetCellValue(item.Result);

                ICell icell1top5 = row_zs.CreateCell(5);
                icell1top5.CellStyle = style;
                icell1top5.SetCellValue(item.CO.ToString());

                ICell icell1top6 = row_zs.CreateCell(6);
                icell1top6.CellStyle = style;
                icell1top6.SetCellValue(item.CO2.ToString());

                ICell icell1top7 = row_zs.CreateCell(7);
                icell1top7.CellStyle = style;
                icell1top7.SetCellValue(item.NO.ToString());

                ICell icell1top8 = row_zs.CreateCell(8);
                icell1top8.CellStyle = style;
                icell1top8.SetCellValue(item.HC.ToString());

                ICell icell1top9 = row_zs.CreateCell(9);
                icell1top9.CellStyle = style;
                icell1top9.SetCellValue(item.Opacity.ToString());

                ICell icell1top10 = row_zs.CreateCell(10);
                icell1top10.CellStyle = style;
                icell1top10.SetCellValue(item.K.ToString());

                ICell icell1top11 = row_zs.CreateCell(11);
                icell1top11.CellStyle = style;
                icell1top11.SetCellValue(item.SF.ToString());

                ICell icell1top12 = row_zs.CreateCell(12);
                icell1top12.CellStyle = style;
                icell1top12.SetCellValue(item.Speed.ToString());

                ICell icell1top13 = row_zs.CreateCell(13);
                icell1top13.CellStyle = style;
                icell1top13.SetCellValue(item.Acc.ToString());

                ICell icell1top14 = row_zs.CreateCell(14);
                icell1top14.CellStyle = style;
                icell1top14.SetCellValue(item.Temperature.ToString());

                ICell icell1top15 = row_zs.CreateCell(15);
                icell1top15.CellStyle = style;
                icell1top15.SetCellValue(item.WindSpeed.ToString());

                ICell icell1top16 = row_zs.CreateCell(16);
                icell1top16.CellStyle = style;
                icell1top16.SetCellValue(item.WindDirection.ToString());

                ICell icell1top17 = row_zs.CreateCell(17);
                icell1top17.CellStyle = style;
                icell1top17.SetCellValue(item.Pressure.ToString());

                ICell icell1top18 = row_zs.CreateCell(18);
                icell1top18.CellStyle = style;
                icell1top18.SetCellValue(item.EntireBelieve.ToString());

                ICell icell1top19 = row_zs.CreateCell(19);
                icell1top19.CellStyle = style;
                icell1top19.SetCellValue(item.Humidity.ToString());

                ICell icell1top20 = row_zs.CreateCell(20);
                icell1top20.CellStyle = style;
                icell1top20.SetCellValue(item.IsUploaded.ToString());
                rownums++;
            }
            #endregion

            //输出内容
            if (string.IsNullOrEmpty(localFilePath))
            {
                return;
            }
            using (FileStream stm = File.OpenWrite(@localFilePath))
            {
                wb.Write(stm);
                MessageBox.Show("导出Excle完成!");
            }                      

第四步:

 /// 格式设置
        /// </summary>
        static ICellStyle Getcellstyle(IWorkbook wb, string type)
        {
            ICellStyle cellStyle = wb.CreateCellStyle();
            //定义字体  
            IFont font = wb.CreateFont();
            font.FontName = "微软雅黑";
            //水平对齐  
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
            //垂直对齐  
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            //自动换行  
            cellStyle.WrapText = true;
            //缩进
            cellStyle.Indention = 0;

            switch (type)
            {
                case "head":
                    cellStyle.SetFont(font);
                    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    break;
                default:
                    cellStyle.SetFont(font);
                    break;
            }
            return cellStyle;
        }

以上就是成功应用的步骤。


private void Export()
        {
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = "export";
            saveDialog.DefaultExt = "xlsx";
            saveDialog.AddExtension = true;
            saveDialog.RestoreDirectory = true;
            DialogResult result = saveDialog.ShowDialog();
            if(result==DialogResult.OK)
            {
                DateTime start = dateEditStart.DateTime;
                DateTime end = dateEditEnd.DateTime.AddDays(1).AddSeconds(-1);
                List<StaticTesting> dataList = staticTestingRepository.GetByTime(start, end);

                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("sheet");
                IRow row0 = sheet.CreateRow(0);
                row0.CreateCell(0).SetCellValue("11111");
                row0.CreateCell(1).SetCellValue("222222");
                row0.CreateCell(2).SetCellValue("333333");
              
                for (int r = 0; r < dataList.Count; r++)
                {
                    IRow row = sheet.CreateRow(r+1);
                    row.CreateCell(0).SetCellValue(dataList[r].111);
                    row.CreateCell(1).SetCellValue(dataList[r].222);
                    row.CreateCell(2).SetCellValue(dataList[r].333);
                   
                }
                using (FileStream fs = File.OpenWrite(saveDialog.FileName))
                {
                    workbook.Write(fs);
                    fs.Flush();
                    fs.Close();
                };

            }
        }

下面是我统计一些较为有用的方法
1、使用NPOI处理大数据量EXCEl2007
excel2003只能存储65536行数据,而excel2007能存储100多万行数据。所以针对日后的数据的一个可预见性增长,所以采用excel2007来处理数据,但是C#默认的excel

的处理方式只能处理excel2003,所以为了处理大数据的excel2007,我们使用NPOI来进行处理
需要引入相应的dll文件,具体引入方法就不再多说,具体的实现方式见下方代码。

  *生成导出EXCEl文件对话框
             *同时将导出文件类型定义为Excel
             *保存时只需指明对应的文件名即可
             */
            SaveFileDialog sdfexport = new SaveFileDialog();
            sdfexport.Filter = "Excel文件|*.xlsx";
            if (sdfexport.ShowDialog() == DialogResult.No)
            {
                return;
            }
            string filename = sdfexport.FileName;
 
            //创建Excel2007工作簿
            IWorkbook book = new XSSFWorkbook();
 
            //创建Excel2007工作表
            ISheet sheet = book.CreateSheet("standard_template");
 
            //创建Excel行
            IRow row = sheet.CreateRow(0);
 
            //给单元格赋值
            row.CreateCell(0).SetCellValue("序号");
            row.CreateCell(1).SetCellValue("大区(区域)");
            row.CreateCell(2).SetCellValue("省(简)");
            row.CreateCell(3).SetCellValue("说明");
            
            /*
             * 将Excel文件写入相应的Excel文件中
             */ 
            FileStream fs = File.Create(filename);
            book.Write(fs);
            fs.Close();

2、NPOI导入导出EXCEL通用类,供参考,可直接使用在WinForm项目中

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using Zwj.TEMS.Common;

namespace TEMS.Service
{
    public static class ExcelHelperForCs
    {
        #region 私有方法

        /// <summary>
        /// 获取要保存的文件名称(含完整路径)
        /// </summary>
        /// <returns></returns>
        private static string GetSaveFilePath()
        {
            SaveFileDialog saveFileDig = new SaveFileDialog();
            saveFileDig.Filter = "Excel Office97-2003(*.xls)|.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";
            saveFileDig.FilterIndex = 0;
            saveFileDig.OverwritePrompt = true;
            saveFileDig.InitialDirectory = Common.DesktopDirectory;
            string filePath = null;
            if (saveFileDig.ShowDialog() == DialogResult.OK)
            {
                filePath = saveFileDig.FileName;
            }

            return filePath;
        }

        /// <summary>
        /// 判断是否为兼容模式
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static bool GetIsCompatible(string filePath)
        {
            return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
        }



        /// <summary>
        /// 创建工作薄
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook();
            }
            else
            {
                return new XSSFWorkbook();
            }
        }

        /// <summary>
        /// 创建工作薄(依据文件流)
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook(stream);
            }
            else
            {
                return new XSSFWorkbook(stream);
            }
        }

        /// <summary>
        /// 创建表格头单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.FillPattern = FillPattern.SolidForeground;
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;

            return style;
        }


        /// <summary>
        /// 从工作表中生成DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
        {
            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                {
                    // 如果遇到第一个空列,则不再继续向后读取
                    cellCount = i + 1;
                    break;
                }
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);

                if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue))
                {
                    DataRow dataRow = table.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }

                    table.Rows.Add(dataRow);
                }
            }

            return table;
        }

        #endregion

        #region 公共导出方法

        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <returns>Excel工作表</returns>
        public static string ExportToExcel(DataSet sourceDs, string filePath = null)
        {

            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetCellStyle(workbook);

            for (int i = 0; i < sourceDs.Tables.Count; i++)
            {
                DataTable table = sourceDs.Tables[i];
                string sheetName = "result" + i.ToString();
                ISheet sheet = workbook.CreateSheet(sheetName);
                IRow headerRow = sheet.CreateRow(0);
                // handling header.
                foreach (DataColumn column in table.Columns)
                {
                    ICell cell = headerRow.CreateCell(column.Ordinal);
                    cell.SetCellValue(column.ColumnName);
                    cell.CellStyle = cellStyle;
                }

                // handling value.
                int rowIndex = 1;

                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in table.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString());
                    }

                    rowIndex++;
                }
            }

            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
            workbook = null;

            return filePath;

        }


        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <returns>Excel工作表</returns>
        public static string ExportToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null)
        {
            if (sourceTable.Rows.Count <= 0) return null;

            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetCellStyle(workbook);

            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);
            // handling header.
            foreach (DataColumn column in sourceTable.Columns)
            {
                ICell headerCell = headerRow.CreateCell(column.Ordinal);
                headerCell.SetCellValue(column.ColumnName);
                headerCell.CellStyle = cellStyle;
            }

            // handling value.
            int rowIndex = 1;

            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue((row[column]??"").ToString());
                }

                rowIndex++;
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();

            sheet = null;
            headerRow = null;
            workbook = null;

            return filePath;
        }

        /// <summary>
        /// 由List导出Excel
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="data">在导出的List</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "result", string filePath = null) where T : class
        {
            if (data.Count <= 0) return null;

            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetCellStyle(workbook);
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);

            for (int i = 0; i < headerNameList.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(headerNameList[i].Value);
                cell.CellStyle = cellStyle;
            }

            Type t = typeof(T);
            int rowIndex = 1;
            foreach (T item in data)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int n = 0; n < headerNameList.Count; n++)
                {
                    object pValue = t.GetProperty(headerNameList[n].Key).GetValue(item, null);
                    dataRow.CreateCell(n).SetCellValue((pValue ?? "").ToString());
                }
                rowIndex++;
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();

            sheet = null;
            headerRow = null;
            workbook = null;

            return filePath;
        }

        /// <summary>
        /// 由DataGridView导出
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="sheetName"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static string ExportToExcel(DataGridView grid, string sheetName = "result", string filePath = null)
        {
            if (grid.Rows.Count <= 0) return null;

            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetCellStyle(workbook);
            ISheet sheet = workbook.CreateSheet(sheetName);

            IRow headerRow = sheet.CreateRow(0);

            for (int i = 0; i < grid.Columns.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(grid.Columns[i].Name);
                cell.CellStyle = cellStyle;
            }

            int rowIndex = 1;
            foreach (DataGridViewRow row in grid.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int n = 0; n < grid.Columns.Count; n++)
                {
                    dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString());
                }
                rowIndex++;
            }

            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();

            sheet = null;
            headerRow = null;
            workbook = null;

            return filePath;
        }

        #endregion

        #region 公共导入方法

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
        {
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
            ISheet sheet = null;
            int sheetIndex = -1;
            if (int.TryParse(sheetName, out sheetIndex))
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            else
            {
                sheet = workbook.GetSheet(sheetName);
            }

            DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);

            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径,为物理路径。</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                bool isCompatible = GetIsCompatible(excelFilePath);
                return ImportFromExcel(stream, sheetName, headerRowIndex, isCompatible);
            }
        }

        /// <summary>
        /// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataSet</returns>
        public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible)
        {
            DataSet ds = new DataSet();
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);
                DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
                ds.Tables.Add(table);
            }

            excelFileStream.Close();
            workbook = null;

            return ds;
        }

        /// <summary>
        /// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径,为物理路径。</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataSet</returns>
        public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                bool isCompatible = GetIsCompatible(excelFilePath);
                return ImportFromExcel(stream, headerRowIndex, isCompatible);
            }
        }

        #endregion

        #region 公共转换方法

        /// <summary>
        /// 将Excel的列索引转换为列名,列索引从0开始,列名从A开始。如第0列为A,第1列为B...
        /// </summary>
        /// <param name="index">列索引</param>
        /// <returns>列名,如第0列为A,第1列为B...</returns>
        public static string ConvertColumnIndexToColumnName(int index)
        {
            index = index + 1;
            int system = 26;
            char[] digArray = new char[100];
            int i = 0;
            while (index > 0)
            {
                int mod = index % system;
                if (mod == 0) mod = system;
                digArray[i++] = (char)(mod - 1 + 'A');
                index = (index - 1) / 26;
            }
            StringBuilder sb = new StringBuilder(i);
            for (int j = i - 1; j >= 0; j--)
            {
                sb.Append(digArray[j]);
            }
            return sb.ToString();
        }


        /// <summary>
        /// 转化日期
        /// </summary>
        /// <param name="date">日期</param>
        /// <returns></returns>
        public static DateTime ConvertDate(object date)
        {
            string dtStr = (date ?? "").ToString();

            DateTime dt = new DateTime();

            if (DateTime.TryParse(dtStr, out dt))
            {
                return dt;
            }

            try
            {
                string spStr = "";
                if (dtStr.Contains("-"))
                {
                    spStr = "-";
                }
                else if (dtStr.Contains("/"))
                {
                    spStr = "/";
                }
                string[] time = dtStr.Split(spStr.ToCharArray());
                int year = Convert.ToInt32(time[2]);
                int month = Convert.ToInt32(time[0]);
                int day = Convert.ToInt32(time[1]);
                string years = Convert.ToString(year);
                string months = Convert.ToString(month);
                string days = Convert.ToString(day);
                if (months.Length == 4)
                {
                    dt = Convert.ToDateTime(date);
                }
                else
                {
                    string rq = "";
                    if (years.Length == 1)
                    {
                        years = "0" + years;
                    }
                    if (months.Length == 1)
                    {
                        months = "0" + months;
                    }
                    if (days.Length == 1)
                    {
                        days = "0" + days;
                    }
                    rq = "20" + years + "-" + months + "-" + days;
                    dt = Convert.ToDateTime(rq);
                }
            }
            catch
            {
                throw new Exception("日期格式不正确,转换日期失败!");
            }
            return dt;
        }

        /// <summary>
        /// 转化数字
        /// </summary>
        /// <param name="d">数字字符串</param>
        /// <returns></returns>
        public static decimal ConvertDecimal(object d)
        {
            string dStr = (d ?? "").ToString();
            decimal result = 0;
            if (decimal.TryParse(dStr, out result))
            {
                return result;
            }
            else
            {
                throw new Exception("数字格式不正确,转换数字失败!");
            }

        }

        #endregion
    }
}

3、最近工作导数据失败,找问题原因:ORALCE导出数据,超出5w多条数据,由于数据量过大(加上列数多),无法导出。
PLSQL工具导出EXCEL失败,卡死白屏。
使用NPOI第三方类导出EXCEL失败,超出100+M多的数据就抛出异常提示内存溢出。
最后找到方法,使用OLEDB方式,数据导出到EXCEL文件,数据量大的时候,运行占用内存明显比NPOI和微软组件小(不超过100M)。

/// <summary>
/// 使用OLEDB导出Excel
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="filepath">文件目录和文件名</param>
/// <param name="tablename">SHEET页名称</param>
/// <param name="pagecount">每页记录数</param>
public static void Export(DataTable dt, string filepath, string tablename, int pagecount)
{
    //excel 2003格式
    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
    //Excel 2007格式
    //string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";
    try
    {
        using (OleDbConnection con = new OleDbConnection(connString))
        {
            con.Open();
            
            //开始分页
            if (dt.Rows.Count > pagecount)
            {
                int page = dt.Rows.Count / pagecount + 1; //总页数
                for (int i = 0; i < page; i++)
                {
                    //建新sheet和表头
                    StringBuilder strSQL = new StringBuilder();
                    string tabname = tablename + i.ToString();
                    strSQL.Append("CREATE TABLE ").Append("[" + tabname + "]"); //每60000项建一页
                    strSQL.Append("(");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        strSQL.Append("[" + dt.Columns[j].ColumnName + "] text,");
                    }
                    strSQL = strSQL.Remove(strSQL.Length - 1, 1);
                    strSQL.Append(")");
 
                    OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
                    cmd.ExecuteNonQuery();
 
                    //准备逐条插入数据
                    for (int j = i * pagecount; j < (i + 1) * pagecount; j++)
                    {
                        if (i == 0 || j < dt.Rows.Count)
                        {
                            StringBuilder tmp = new StringBuilder();
                            StringBuilder strfield = new StringBuilder();
                            StringBuilder strvalue = new StringBuilder();
                            for (int z = 0; z < dt.Columns.Count; z++)
                            {
                                strfield.Append("[" + dt.Columns[z].ColumnName + "]");
                                strvalue.Append("'" + dt.Rows[j][z].ToString() + "'");
                                if (z != dt.Columns.Count - 1)
                                {
                                    strfield.Append(",");
                                    strvalue.Append(",");
                                }
                                else
                                {
                                }
                            }
                            cmd.CommandText = tmp.Append(" insert into [" + tabname + "]( ")
                                .Append(strfield.ToString())
                                .Append(") values (").Append(strvalue).Append(")").ToString();
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            con.Close();
            no = count;
        }
        Console.WriteLine("OK");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    GC.Collect();
}

4、NPOI导出Excel和EPPlus导出Excel比较
系统中经常会使用导出Excel的功能。

之前使用的是NPOI,但是导出数据行数多就报内存溢出。

最近看到EPPlus可以用来导出Excel,就自己测了下两者导出上的差异。

NPIO官网地址:http://npoi.codeplex.com/

EPPlus官网地址:http://epplus.codeplex.com/

添加NPOI、EPPlus类库dll使用的是NuGet添加。

在类库References右键Manage NuGet Packages…,之后选择添加对应的dll。

测试结果显示,相同数据结构的数据,EPPlus的导出能力比NPOI强。

20列,NPOI能导出4万数据,导出5万数据时报内存溢出。

EPPlus能导出20万以上数据,导出23万测试时内存溢出。
NPOI导出:

1 private static MemoryStream ExportXlsx(DataTable dt)
 2         {
 3             XSSFWorkbook workbook = new XSSFWorkbook();
 4             ISheet sheet = null;
 5 
 6             int headRowIndex = 0;
 7             string sheetName = "Sheet1";
 8             if (!string.IsNullOrEmpty(dt.TableName))
 9             {
10                 sheetName = dt.TableName;
11             }
12             sheet = workbook.CreateSheet(sheetName);
13             int rowIndex = 0;
14 
15             #region 列头及样式
16             {
17                 XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);
18 
19                 ICellStyle headStyle = workbook.CreateCellStyle();
20                 headStyle.Alignment = HorizontalAlignment.Center;
21                 IFont font = workbook.CreateFont();
22                 font.FontHeightInPoints = 10;
23                 font.Boldweight = 700;
24                 headStyle.SetFont(font);
25 
26                 foreach (DataColumn column in dt.Columns)
27                 {
28                     headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
29                     headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
30                 }
31             }
32             #endregion
33 
34             #region 填充内容
35 
36             foreach (DataRow row in dt.Rows)
37             {
38                 rowIndex++;
39                 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
40                 foreach (DataColumn column in dt.Columns)
41                 {
42                     string drValue = row[column].ToString();
43                     dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
44                 }
45             }
46             #endregion
47 
48 
49             MemoryStream ms = new MemoryStream();
50 
51             workbook.Write(ms);
52             ms.Flush();
53 
54             return ms;
55         }
56 
57         public static void ExportXlsxByWeb(DataTable dt, string strFileName)
58         {
59 
60             HttpContext curContext = HttpContext.Current;
61 
62             MemoryStream ms = ExportXlsx(dt);
63 
64             curContext.Response.AppendHeader("Content-Disposition",
65                 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");
66             curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
67             curContext.Response.ContentEncoding = Encoding.UTF8;
68 
69             curContext.Response.BinaryWrite(ms.ToArray());
70             ms.Close();
71             ms.Dispose();
72             curContext.Response.End();
73 
74         }

EPPlus导出:

1 /// <summary>
 2         /// 使用EPPlus导出Excel(xlsx)
 3         /// </summary>
 4         /// <param name="sourceTable">数据源</param>
 5         /// <param name="strFileName">xlsx文件名(不含后缀名)</param>
 6         public static void ExportByEPPlus(DataTable sourceTable, string strFileName)
 7         {
 8             using (ExcelPackage pck = new ExcelPackage())
 9             {
10                 //Create the worksheet
11                 string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName;
12                 ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
13 
14                 //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
15                 ws.Cells["A1"].LoadFromDataTable(sourceTable, true);
16 
17                 //Format the row
18                 ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
19                 Color borderColor = Color.FromArgb(155, 155, 155);
20 
21                 using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count])
22                 {
23                     rng.Style.Font.Name = "宋体";
24                     rng.Style.Font.Size = 10;
25                     rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
26                     rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
27 
28                     rng.Style.Border.Top.Style = borderStyle;
29                     rng.Style.Border.Top.Color.SetColor(borderColor);
30 
31                     rng.Style.Border.Bottom.Style = borderStyle;
32                     rng.Style.Border.Bottom.Color.SetColor(borderColor);
33 
34                     rng.Style.Border.Right.Style = borderStyle;
35                     rng.Style.Border.Right.Color.SetColor(borderColor);
36                 }
37 
38                 //Format the header row
39                 using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count])
40                 {
41                     rng.Style.Font.Bold = true;
42                     rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
43                     rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
44                     rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
45                 }
46 
47                 //Write it back to the client
48                 HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
49                 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));
50                 HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
51 
52                 HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
53                 HttpContext.Current.Response.End();
54             }
55         }

在这里插入图片描述


5导出数据到Excel(百万级)保存为csv格式

private void DataTableToCsv(DataTable table, string file)
        {
            StringBuilder titleBuilder = new StringBuilder();
            StringBuilder lineBuilder = new StringBuilder();
            FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
            StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);

            for (int i = 0; i < table.Columns.Count; i++)
            {
                titleBuilder.Append(table.Columns[i].ColumnName + "\t"); //栏位:自动跳到下一单元格
            }

            string title = titleBuilder.ToString();
            title = title.Substring(0, title.Length - 1) + "\n";

            sw.Write(title);

            string line = string.Empty;
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    lineBuilder.Append(row[i].ToString().Trim() + "\t"); //内容:自动跳到下一单元格
                }

                line = lineBuilder.ToString();
                line = line.Substring(0, line.Length - 1) + "\n";
                sw.Write(line);

                lineBuilder.Clear();
            }

            sw.Close();
            fs.Close();
        }

今天的文章将数据导出到excel java_数据无法导出到excel[通俗易懂]分享到此就结束了,感谢您的阅读,如果确实帮到您,您可以动动手指转发给其他人。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/58919.html

(0)
编程小号编程小号

相关推荐

发表回复

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