急求帮助完善代码

Question

我有一同事交给我一个任务 客户需求说要导出的Excel以每500条数据为一张Sheet，然后将所有数据以次导出到一张Excel表里，先付上导出Excel的方法函数求达人帮忙改改  我不知道该怎么去改
代码如下：
        private string  ExportExcel(DataSet ds, string currentYear, string currentMonth, string dis)
        {
            
                int i = 0;
                //判断 选择导出所有时进入
                string templatePOSFile = HttpRuntime.AppDomainAppPath + @"Template\China POS TemplateDC.xls";
                if (RBExp2.Checked) 
                   templatePOSFile = HttpRuntime.AppDomainAppPath + @"Template\China POS TemplateDIS.xls";
                string fileName="DC_POS_"+dis+"_"+ currentYear + "-" + currentMonth+".xls";
                string outputPOSFile = HttpRuntime.AppDomainAppPath + @"Output\" + fileName;//@"Output\DOWPos" + currentYear + "-" + currentMonth+".xls";
                File.Copy(templatePOSFile, outputPOSFile, true);
                
                //使用excel的表，sheet 写入
                object missing = Missing.Value;
                Excel.Application excel = new Excel.Application();
                excel.Visible = false;
                excel.UserControl = true;
                Workbooks workbooks = excel.Workbooks;                workbooks.Open(outputPOSFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                Excel.Workbook workbook = excel.Workbooks[1];
                Excel.Sheets sheets = workbook.Worksheets;
                Excel._Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
            
            string str = GetColumnFromCusRev.getColumn(currentMonth.Trim());
            string[] vol = str.Split(',');            try
            {
            foreach (System.Data.DataTable tb in ds.Tables)
            {
                //写出数据
                foreach (DataRow row in tb.Rows)
                {
                    
                    worksheet.Cells[2 + i, 1] =currentYear;
                    worksheet.Cells[2 + i, 2] = GetColumnFromCusRev.getEnMonth(currentMonth);
                    worksheet.Cells[2 + i, 3] = row["ChineseName"]; 
                    worksheet.Cells[2 + i, 4] = row["MaterialNumber"]; 
                    worksheet.Cells[2 + i, 5] = row["MaterialName"];
                    worksheet.Cells[2 + i, 6] = row["ccy"]; 
                    worksheet.Cells[2 + i, 7] = row["BaseUnit"];
                    worksheet.Cells[2 + i, 8] = getRount(row[vol[1].Trim()].ToString());
                    worksheet.Cells[2 + i, 9] = getEndUserRevValue(row["ccy"].ToString(), Convert.ToDouble(row[vol[0].Trim()].ToString()), Convert.ToDouble(row["EndCustomer"].ToString()));
                    worksheet.Cells[2 + i, 10] = getRount(row[vol[2].Trim()].ToString()); 
                    worksheet.Cells[2 + i, 11] = getEndUserPrice(row["ccy"].ToString(), Convert.ToDouble(row["EndCustomer"].ToString()));
                    worksheet.Cells[2 + i, 12] = row[vol[0].Trim()];
                    worksheet.Cells[2 + i, 13] = row["Market"];
                    worksheet.Cells[2 + i, 14] = row["SaleNameLastName"];
                    worksheet.Cells[2 + i, 15] = row["SaleNameFirstName"];
                    worksheet.Cells[2 + i, 16] = row["EnglishName"];
                    worksheet.Cells[2 + i, 17] = row["RegisAddress"]; 
                    worksheet.Cells[2 + i, 18] = row["City"]; //row[col[1].Trim()];
                    worksheet.Cells[2 + i, 19] = row["province"];
                    worksheet.Cells[2 + i, 20] = row["PostCode"]; 
                    worksheet.Cells[2 + i, 21] = row["Country"]; 
                    worksheet.Cells[2 + i, 22] = row["ApplicationPoint"];
                    worksheet.Cells[2 + i, 23] = row["CustomerType"];
                    worksheet.Cells[2 + i, 24] = row["DistributionID"];
                    worksheet.Cells[2 + i, 25] = row["CustomerID"]; 
                    worksheet.Cells[2 + i, 26] = row["RegisterDate"]; 
                    worksheet.Cells[2 + i, 27] = row["RecentRevisedDate"];
                    worksheet.Cells[2 + i, 28] = row["GroupName"];
                    worksheet.Cells[2 + i, 29] = row["Territory"];
                    worksheet.Cells[2 + i, 30] = row["FirstContactDate"];
                    worksheet.Cells[2 + i, 31] = row["Name"];
                    worksheet.Cells[2 + i, 32] = row["Tel"];
                    worksheet.Cells[2 + i, 33] = row["Email"];
                    worksheet.Cells[2 + i, 34] = row["OperationalFAEName"];
                    worksheet.Cells[2 + i, 35] = row["ProjectName"];
                    worksheet.Cells[2 + i, 36] = row["ApplicationStatus"];
                    if (double.Parse(row[vol[3].Trim()].ToString()) > 0)
                      worksheet.Cells[2 + i, 37] ="YES";
                    else
                      worksheet.Cells[2 + i, 37] ="NO";
                    worksheet.Cells[2 + i, 38] = row["PotentialSalesValue"];
                    worksheet.Cells[2 + i, 39] = row["MarketSegment"];
                    worksheet.Cells[2 + i, 40] = row["CompetitorName1"];
                    worksheet.Cells[2 + i, 41] = row["ProductTypeCom1"];                    //选择导出所有时进入
                    if (RBExp1.Checked)
                    {
                        double vol12Sum = 0;
                        double rev12Sum = 0;
                        //12个月
                        for (int m = 0; m < 12; m++)
                        {
                            string str12 = GetColumnFromCusRev.getColumn((m + 1).ToString());
                            string[] vol12 = str12.Split(',');
                            worksheet.Cells[2 + i, 44 + m * 2] = row[vol12[0].Trim()];
                            vol12Sum += Convert.ToDouble(row[vol12[0].Trim()].ToString());
                            worksheet.Cells[2 + i, 45 + m * 2] = row[vol12[1].Trim()];
                            rev12Sum += Convert.ToDouble(row[vol12[1].Trim()].ToString());
                        }                        worksheet.Cells[2 + i, 68] = vol12Sum;
                        worksheet.Cells[2 + i, 69] = rev12Sum;
                    }
                   
                    i++;                }
            }
            workbook.Save();    //保存目标文件
            workbooks.Close();
            excel.Quit();
            System.GC.Collect();
            return fileName;            }catch (Exception ee)
            {
                workbook.Save();    //保存目标文件
                workbooks.Close();
                excel.Quit();
                System.GC.Collect();
                Response.Write("生成excel："+ee);
                return null;
            }        }

Answer 1

你这函数里还调用了很多其他方法，无法给你进行测试。
给你个思路把，先判断要导出的数据总行数，然后除以500，得出要分页的总数。然后把
//写出数据
                foreach (DataRow row in tb.Rows)
这个循环做相应的修改就行了。应该不难的。
附求需要分页的总数的方法：
 //获取总行数
        //int rowcount = ds.Tables[0].Rows.Count;        int m = rowcount % 500;
        //获取要划分的sheet数
        int n = 0;
        if (m == 0)
        {
            n = Convert.ToInt32(rowcount / 500);
        }
        else
        {
            n = Convert.ToInt32(rowcount / 500) + 1;
        }

Answer 2

我就是不理解这个思路啊  一直没用过导EXCEL所以不懂

Answer 3

大量数据使用二维数组提高效率， 数据使用分页存储过程
Excel.Application app = new Excel.ApplicationClass(); 
app.Visible = true; 
Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing); 
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); 
for(int i=1;i <sheetCount;i++) 
{ 
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]); } 

Answer 4

如果兄弟 你这个是 服务器端程序  则要在 服务器上安装 excel 还要考虑权限问题。每次导入 都必须执行 excel.exe ，我个人感觉很不爽。建议不用这个方法。用oledb 对excel的读写。http://www.codeproject.com/KB/office/excel_using_oledb.aspx