打工笔记----------------------------C# Excel导出超出65536行报错 Invalid row number (65536) outside allowable range (0..65535) - 我们打工人

打工笔记----------------------------C# Excel导出超出65536行报错 Invalid row number (65536) outside allowable range (0..65535)

Invalid row number (65536) outside allowable range (0..65535)

产生该错误的原因是Excel 03的限制，它最多支持65536行，如果数据行数超过了这个限制，就会触发错误。

解决方案是创建多个sheet来分散数据，例如，每当达到65535行时，就创建一个新的sheet来继续写入数据

原来的代码如下：

  1 public void ExportDataToExcel(DataTable TableName, string FileName)
  2         {
  3             SaveFileDialog saveFileDialog = new SaveFileDialog();
  4             //设置文件标题
  5             saveFileDialog.Title = "导出Excel文件";
  6             //设置文件类型
  7             saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
  8             //设置默认文件类型显示顺序  
  9             saveFileDialog.FilterIndex = 1;
 10             //是否自动在文件名中添加扩展名
 11             saveFileDialog.AddExtension = true;
 12             //是否记忆上次打开的目录
 13             saveFileDialog.RestoreDirectory = true;
 14             //设置默认文件名
 15             saveFileDialog.FileName = FileName;
 16             //按下确定选择的按钮  
 17             if (saveFileDialog.ShowDialog() == DialogResult.OK)
 18             {
 19                 //获得文件路径 
 20                 string localFilePath = saveFileDialog.FileName.ToString();
 21 
 22                 //数据初始化
 23                 int TotalCount;     //总行数
 24                 int RowRead = 0;    //已读行数
 25                 int Percent = 0;    //百分比
 26 
 27                 TotalCount = TableName.Rows.Count;
 28 
 29                 //NPOI
 30                 IWorkbook workbook;
 31                 string FileExt = Path.GetExtension(localFilePath).ToLower();
 32                 if (FileExt == ".xlsx")
 33                 {
 34                     workbook = new XSSFWorkbook();
 35                 }
 36                 else if (FileExt == ".xls")
 37                 {
 38                     workbook = new HSSFWorkbook();
 39                 }
 40                 else
 41                 {
 42                     workbook = null;
 43                 }
 44                 if (workbook == null)
 45                 {
 46                     return;
 47                 }
 48                 ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(FileName);
 49 
 50 
 51                 //秒钟
 52                 Stopwatch timer = new Stopwatch();
 53                 timer.Start();
 54 
 55                 try
 56                 {
 57                     //读取标题  
 58                     IRow rowHeader = sheet.CreateRow(0);
 59                     for (int i = 0; i < TableName.Columns.Count; i++)
 60                     {
 61                         ICell cell = rowHeader.CreateCell(i);
 62                         cell.SetCellValue(TableName.Columns[i].ColumnName);
 63                     }
 64 
 65                     //读取数据  
 66                     for (int i = 0; i < TableName.Rows.Count; i++)
 67                     {
 68                         IRow rowData = sheet.CreateRow(i + 1);
 69                         for (int j = 0; j < TableName.Columns.Count; j++)
 70                         {
 71                             ICell cell = rowData.CreateCell(j);
 72                             cell.SetCellValue(TableName.Rows[i][j].ToString());
 73                         }
 74                         //状态栏显示
 75                         RowRead++;
 76                         Percent = (int)(100 * RowRead / TotalCount);
 77                         Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("共有" + TotalCount + "条数据，已读取" + Percent.ToString() + "%的数据。");
 78                         Application.DoEvents();
 79                     }
 80                     
 81                     Application.DoEvents();
 82 
 83                     //转为字节数组  
 84                     MemoryStream stream = new MemoryStream();
 85                     workbook.Write(stream);
 86                     var buf = stream.ToArray();
 87 
 88                     //保存为Excel文件  
 89                     using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
 90                     {
 91                         fs.Write(buf, 0, buf.Length);
 92                         fs.Flush();
 93                         fs.Close();
 94                     }
 95 
 96                     //状态栏更改
 97                     Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("生成Excel成功，共耗时" + timer.ElapsedMilliseconds + "毫秒。");
 98                     Application.DoEvents();
 99 
100                     //关闭秒钟
101                     timer.Reset();
102                     timer.Stop();
103 
104                     //成功提示
105                     if (MessageBox.Show("导出成功，是否立即打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
106                     {
107                         System.Diagnostics.Process.Start(localFilePath);
108                     }
109                     Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm();
110                     
111                 }
112                 catch (Exception ex)
113                 {
114                     MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
115                     Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm();
116                 }
117                 finally
118                 {
119                     //关闭秒钟
120                     timer.Reset();
121                     timer.Stop();
122                 }
123             }
124         }

修改后的代码如下：

  1 public void ExportDataToExcel(DataTable TableName, string FileName)
  2 {
  3     SaveFileDialog saveFileDialog = new SaveFileDialog();
  4     //设置文件标题
  5     saveFileDialog.Title = "导出Excel文件";
  6     //设置文件类型
  7     saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
  8     //设置默认文件类型显示顺序  
  9     saveFileDialog.FilterIndex = 1;
 10     //是否自动在文件名中添加扩展名
 11     saveFileDialog.AddExtension = true;
 12     //是否记忆上次打开的目录
 13     saveFileDialog.RestoreDirectory = true;
 14     //设置默认文件名
 15     saveFileDialog.FileName = FileName;
 16     //按下确定选择的按钮  
 17     if (saveFileDialog.ShowDialog() == DialogResult.OK)
 18     {
 19         //获得文件路径 
 20         string localFilePath = saveFileDialog.FileName.ToString();
 21 
 22         //数据初始化
 23         int TotalCount;     //总行数
 24         int RowRead = 0;    //已读行数
 25         int Percent = 0;    //百分比
 26 
 27         //{B3B9C35D-E268-4609-8E66-048E0CBEAC7D}@li-yue-li@LSRM-922导出报错问题处理
 28         TotalCount = TableName.Rows.Count;
 29         //秒钟      
 30         Stopwatch timer = new Stopwatch();
 31         try
 32         {
 33             //NPOI
 34             IWorkbook workbook;
 35             string FileExt = Path.GetExtension(localFilePath).ToLower();
 36             if (FileExt == ".xlsx")
 37             {
 38                 workbook = new XSSFWorkbook();
 39             }
 40             else if (FileExt == ".xls")
 41             {
 42                 workbook = new HSSFWorkbook();
 43             }
 44             else
 45             {
 46                 workbook = null;
 47             }
 48             if (workbook == null)
 49             {
 50                 return;
 51             }
 52             //{B3B9C35D-E268-4609-8E66-048E0CBEAC7D}@li-yue-li@LSRM-922因为xls文件格式最长支持65536行，继续导出需新建sheet
 53             //秒钟开始               
 54             timer.Start();
 55             if (FileExt == ".xlsx")
 56             {
 57                 ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(FileName);
 58                 
 59                 //读取标题  
 60                 IRow rowHeader = sheet.CreateRow(0);
 61                 for (int i = 0; i < TableName.Columns.Count; i++)
 62                 {
 63                     ICell cell = rowHeader.CreateCell(i);
 64                     cell.SetCellValue(TableName.Columns[i].ColumnName);
 65                 }
 66 
 67                 //读取数据  
 68                 for (int i = 0; i < TableName.Rows.Count; i++)
 69                 {
 70                     IRow rowData = sheet.CreateRow(i + 1);
 71                     for (int j = 0; j < TableName.Columns.Count; j++)
 72                     {
 73                         ICell cell = rowData.CreateCell(j);
 74                         cell.SetCellValue(TableName.Rows[i][j].ToString());
 75                     }
 76                     //状态栏显示
 77                     RowRead++;
 78                     Percent = (int)(100 * RowRead / TotalCount);
 79                     Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("共有" + TotalCount + "条数据，已读取" + Percent.ToString() + "%的数据。");
 80                     Application.DoEvents();
 81                 }
 82             }
 83             else if (FileExt == ".xls")
 84             {
 85                 var sheetindex = Math.Ceiling(Convert.ToDouble(TotalCount) / 65000);
 86                 for (var k = 0; k < sheetindex; k++)
 87                 {
 88                     ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet-" + k) : workbook.CreateSheet(FileName.Replace(".xlsx", "-" + k).Replace(".xls", "-" + k));
 89                     //读取标题  
 90                     IRow rowHeader = sheet.CreateRow(0);
 91                     for (int i = 0; i < TableName.Columns.Count; i++)
 92                     {
 93                         ICell cell = rowHeader.CreateCell(i);
 94                         cell.SetCellValue(TableName.Columns[i].ColumnName);
 95                     }
 96                     var starrow = (65000 * k);
 97                     var lastrow = 65000;
 98                     if((TableName.Rows.Count - starrow) < 65000)
 99                     {
100                         lastrow = TableName.Rows.Count - starrow;
101                     }
102                     var rowcount = starrow + lastrow;
103                     var creatrow = 1;
104                     //读取数据  
105                     for (int i = starrow; i < rowcount; i++)
106                     {
107                         IRow rowData = sheet.CreateRow(creatrow);
108                         creatrow++;
109                         for (int j = 0; j < TableName.Columns.Count; j++)
110                         {
111                             ICell cell = rowData.CreateCell(j);
112                             cell.SetCellValue(TableName.Rows[i][j].ToString());
113                         }
114                         //状态栏显示
115                         RowRead++;
116                         Percent = (int)(100 * RowRead / TotalCount);
117                         Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("共有" + TotalCount + "条数据，已读取" + Percent.ToString() + "%的数据。");
118                         Application.DoEvents();
119                     }
120                 }
121             }
122             
123 
124             Application.DoEvents();
125 
126             //转为字节数组  
127             MemoryStream stream = new MemoryStream();
128             workbook.Write(stream);
129             var buf = stream.ToArray();
130 
131             //保存为Excel文件  
132             using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
133             {
134                 fs.Write(buf, 0, buf.Length);
135                 fs.Flush();
136                 fs.Close();
137             }
138 
139             //状态栏更改
140             Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("生成Excel成功，共耗时" + timer.ElapsedMilliseconds + "毫秒。");
141             Application.DoEvents();
142 
143             //关闭秒钟
144             timer.Reset();
145             timer.Stop();
146 
147             //成功提示
148             if (MessageBox.Show(this, "导出成功，是否立即打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
149             {
150                 System.Diagnostics.Process.Start(localFilePath);
151             }
152             Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm();
153 
154         }
155         catch (Exception ex)
156         {
157             MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
158             Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm();
159         }
160         finally
161         {
162             //关闭秒钟
163             timer.Reset();
164             timer.Stop();
165         }
166     }
167 }

posted on 2025-07-17 17:28 我们打工人阅读(118) 评论(0) 收藏举报

刷新页面返回顶部