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 }
浙公網(wǎng)安備 33010602011771號(hào)