内射一区二区三区四区,人妻系列无码专区69影院,小污女小欲女导航

打工筆記----------------------------C# Excel導(dǎo)出超出65536行報(bào)錯(cuò) Invalid row number (65536) outside allowable range (0..65535)

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

產(chǎn)生該錯(cuò)誤的原因是Excel 03的限制，它最多支持65536行，如果數(shù)據(jù)行數(shù)超過了這個(gè)限制，就會(huì)觸發(fā)錯(cuò)誤。

解決方案是創(chuàng)建多個(gè)sheet來分散數(shù)據(jù)，例如，每當(dāng)達(dá)到65535行時(shí)，就創(chuàng)建一個(gè)新的sheet來繼續(xù)寫入數(shù)據(jù)

原來的代碼如下：

  1 public void ExportDataToExcel(DataTable TableName, string FileName)
  2         {
  3             SaveFileDialog saveFileDialog = new SaveFileDialog();
  4             //設(shè)置文件標(biāo)題
  5             saveFileDialog.Title = "導(dǎo)出Excel文件";
  6             //設(shè)置文件類型
  7             saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
  8             //設(shè)置默認(rèn)文件類型顯示順序  
  9             saveFileDialog.FilterIndex = 1;
 10             //是否自動(dòng)在文件名中添加擴(kuò)展名
 11             saveFileDialog.AddExtension = true;
 12             //是否記憶上次打開的目錄
 13             saveFileDialog.RestoreDirectory = true;
 14             //設(shè)置默認(rèn)文件名
 15             saveFileDialog.FileName = FileName;
 16             //按下確定選擇的按鈕  
 17             if (saveFileDialog.ShowDialog() == DialogResult.OK)
 18             {
 19                 //獲得文件路徑 
 20                 string localFilePath = saveFileDialog.FileName.ToString();
 21 
 22                 //數(shù)據(jù)初始化
 23                 int TotalCount;     //總行數(shù)
 24                 int RowRead = 0;    //已讀行數(shù)
 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                     //讀取標(biāo)題  
 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                     //讀取數(shù)據(jù)  
 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                         //狀態(tài)欄顯示
 75                         RowRead++;
 76                         Percent = (int)(100 * RowRead / TotalCount);
 77                         Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("共有" + TotalCount + "條數(shù)據(jù)，已讀取" + Percent.ToString() + "%的數(shù)據(jù)。");
 78                         Application.DoEvents();
 79                     }
 80                     
 81                     Application.DoEvents();
 82 
 83                     //轉(zhuǎn)為字節(jié)數(shù)組  
 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                     //狀態(tài)欄更改
 97                     Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("生成Excel成功，共耗時(shí)" + timer.ElapsedMilliseconds + "毫秒。");
 98                     Application.DoEvents();
 99 
100                     //關(guān)閉秒鐘
101                     timer.Reset();
102                     timer.Stop();
103 
104                     //成功提示
105                     if (MessageBox.Show("導(dǎo)出成功，是否立即打開？", "提示", 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                     //關(guān)閉秒鐘
120                     timer.Reset();
121                     timer.Stop();
122                 }
123             }
124         }

修改后的代碼如下：

  1 public void ExportDataToExcel(DataTable TableName, string FileName)
  2 {
  3     SaveFileDialog saveFileDialog = new SaveFileDialog();
  4     //設(shè)置文件標(biāo)題
  5     saveFileDialog.Title = "導(dǎo)出Excel文件";
  6     //設(shè)置文件類型
  7     saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
  8     //設(shè)置默認(rèn)文件類型顯示順序  
  9     saveFileDialog.FilterIndex = 1;
 10     //是否自動(dòng)在文件名中添加擴(kuò)展名
 11     saveFileDialog.AddExtension = true;
 12     //是否記憶上次打開的目錄
 13     saveFileDialog.RestoreDirectory = true;
 14     //設(shè)置默認(rèn)文件名
 15     saveFileDialog.FileName = FileName;
 16     //按下確定選擇的按鈕  
 17     if (saveFileDialog.ShowDialog() == DialogResult.OK)
 18     {
 19         //獲得文件路徑 
 20         string localFilePath = saveFileDialog.FileName.ToString();
 21 
 22         //數(shù)據(jù)初始化
 23         int TotalCount;     //總行數(shù)
 24         int RowRead = 0;    //已讀行數(shù)
 25         int Percent = 0;    //百分比
 26 
 27         //{B3B9C35D-E268-4609-8E66-048E0CBEAC7D}@li-yue-li@LSRM-922導(dǎo)出報(bào)錯(cuò)問題處理
 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因?yàn)閤ls文件格式最長(zhǎng)支持65536行，繼續(xù)導(dǎo)出需新建sheet
 53             //秒鐘開始               
 54             timer.Start();
 55             if (FileExt == ".xlsx")
 56             {
 57                 ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(FileName);
 58                 
 59                 //讀取標(biāo)題  
 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                 //讀取數(shù)據(jù)  
 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                     //狀態(tài)欄顯示
 77                     RowRead++;
 78                     Percent = (int)(100 * RowRead / TotalCount);
 79                     Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("共有" + TotalCount + "條數(shù)據(jù)，已讀取" + Percent.ToString() + "%的數(shù)據(jù)。");
 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                     //讀取標(biāo)題  
 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                     //讀取數(shù)據(jù)  
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                         //狀態(tài)欄顯示
115                         RowRead++;
116                         Percent = (int)(100 * RowRead / TotalCount);
117                         Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("共有" + TotalCount + "條數(shù)據(jù)，已讀取" + Percent.ToString() + "%的數(shù)據(jù)。");
118                         Application.DoEvents();
119                     }
120                 }
121             }
122             
123 
124             Application.DoEvents();
125 
126             //轉(zhuǎn)為字節(jié)數(shù)組  
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             //狀態(tài)欄更改
140             Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("生成Excel成功，共耗時(shí)" + timer.ElapsedMilliseconds + "毫秒。");
141             Application.DoEvents();
142 
143             //關(guān)閉秒鐘
144             timer.Reset();
145             timer.Stop();
146 
147             //成功提示
148             if (MessageBox.Show(this, "導(dǎo)出成功，是否立即打開？", "提示", 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             //關(guān)閉秒鐘
163             timer.Reset();
164             timer.Stop();
165         }
166     }
167 }

posted on 2025-07-17 17:28 我們打工人閱讀(105) 評(píng)論(0) 收藏舉報(bào)

刷新頁面返回頂部