使用c＃.net将一个数据表的每450个记录拆分为多个excel表

本文介绍了使用c＃.net将一个数据表的每450个记录拆分为多个excel表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我在一个数据表中有数千条记录。 现在我必须将数据拆分为450条记录450条记录存储在动态创建的Excel工作表中。（1张工作簿多张）。 请帮助我。 提前致谢。 来自答案 i有这样的..下面是我试过的c＃代码。但在这里，在一张纸上，一切都写完了。并且在表1中显示最后450条记录...Hi,I have thousand's of record in one datatable.Now i have to split the data to 450 records and every 450 records to be stored in excel sheets that creates dynamically .(1 workbook multiple sheets).Please help me.Thanks in advance.From answersi have like this.. below is the c# code which i tried. but here, in one sheet everything is over written. and in sheet 1 last 450 records are getting displayed...int x = (table1.Rows.Count / 450); for (int i = 0; i < x; i++) { System.Data.DataTable dtfind = table1.AsEnumerable().Take(450).CopyToDataTable(); int r = 1; ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets[i + 1]; //Writing Columns Name in Excel Sheet for (int col = 1; col <= table1.Columns.Count; col++) ExcelWorkSheet.Cells[r, col] = table1.Columns[col - 1].ColumnName; r++; //Writing Rows into Excel Sheet //r stands for ExcelRow and col for ExcelColumn for (int row = 0; row < dtfind.Rows.Count; row++) { // Excel row and column start positions for writing Row=1 and Col=1 for (int col = 1; col <= dtfind.Columns.Count; col++) { ExcelWorkSheet.Cells[r, col] = dtfind.Rows[row][col - 1].ToString(); } dtfind.Rows[row].Delete(); dtfind.AcceptChanges(); r++; } ExcelWorkBook.Worksheets.Add(); }推荐答案System.Data.DataTable dtfind = table1.AsEnumerable().Take(450).CopyToDataTable(); 每次只获得450条记录。为了能够获取下一部分数据，您应该跳过 [ ^ ]一组数据应计算为 n * 450 。Everytime you get first 450 records only. To be able to fetch next portion of data, you should Skip[^] a set of data which should be calculated as n*450.System.Data.DataTable dtfind = table1.AsEnumerable().Skip(i*450).Take(450).CopyToDataTable(); 示例：Example:List<int> numbers = Enumerable.Range(1,5000).ToList();for(int i=0;i<numbers.Count;i++){ var result = numbers.Skip(i*450).Take(450).ToList(); //returns List<int> //if i == 0 then returns range 1-450 //if i == 1 then returns range 451-900 //and so on..} 提示： 而不是逐个单元格复制，使用更快的方法。请参阅：如何使用Visual C＃2005或Visual C＃.NET将数据传输到Excel工作簿 [ ^ ]和快速从DataSet导出到Excel [ ^ ]int x = (table1.Rows.Count / 450); for (int i = 0; i < x; i++) { ... } 如果我没有弄错，它将只填写完整的表并错过最后的部分表。 如果有850条记录，则生成的工作簿将只有1张450条记录。 如果有这个改变，最后的部分表格将被包括在内：If I don't make a mistake, it will fill only full sheets and miss the last partial sheet.if there are 850 records, the resulting workbook will have only 1 sheet of 450 records.with this change, the last partial sheet will be included:int x = table1.Rows.Count; for (int i = 0; i < x; i+= 450) { ... } 我不知道为什么你在多张450张唱片中拆分你的基础，但它肯定更容易放1张表中的所有记录。 自Excel 2007起，每张表的限制为1,048,576行。I don't know why you split your base in multi sheets of 450 records, but it is certainly easier to put all records in 1 sheet.Since Excel 2007, the limit is 1,048,576 rows per sheet. 这篇关于使用c＃.net将一个数据表的每450个记录拆分为多个excel表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！