导出Excel，如何产生多个工作薄？

Question

有20W条记录，一个Excel工作薄只能放65535条记录，这样我就要用多个工作薄来存放。
我也写了段代码，贴出来大家看看。不过不知道问题处在哪里？各位朋友有没有什么更好的方法？实在不行我保存成多个文件了。
// 选择文件名
Boolean fileSaved = false;
String filename = String.Empty;
SaveFileDialog sfd = new SaveFileDialog();
sfd.DefaultExt = "xls";
sfd.Filter = "Excel文件|*.xls";
sfd.FileName = DateTime.Now.ToString("yyyyMMddhhmmssfff");if (sfd.ShowDialog() == DialogResult.OK)
{
    filename = sfd.FileName;
}
else
{
    return;
}
// 产生对象
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
    MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
    return;
}
Int32 pagesize = 6;
Int32 pagecount = dt.Rows.Count / pagesize + (dt.Rows.Count % pagesize > 0 ? 1 : 0);Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
for (Int32 y = 1; y <= pagecount; y++)
{
    // 创建工作薄
    workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
    //取得sheet1
    worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[y];
    //写入字段 
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
    }
    //写入数值 
    Int32 x = 2;
    for (int r = (y - 1) * pagesize; r < (y * pagesize > dt.Rows.Count ? dt.Rows.Count : y * pagesize); r++)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
worksheet.Cells[x, i + 1] = dt.Rows[r][i];
        }
        x++;
        System.Windows.Forms.Application.DoEvents();
    }
}
//列宽自适应。
worksheet.Columns.EntireColumn.AutoFit();
// 保存文件
try
{
    if (filename.Trim().Length > 0)
    {
        workbook.Saved = true;
        workbook.SaveCopyAs(filename);
        fileSaved = true;        MessageBox.Show("保存成功");
    }
    else
    {
        fileSaved = false;
        MessageBox.Show("保存失败");
    }
}
catch (Exception ex)
{
    fileSaved = false;
    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
finally
{
    xlApp.Quit();
    //强行销毁 
    GC.Collect();
}

Answer 1

现在问题出在这句上，说找不到索引//取得sheet1
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[y];

Answer 2

用现成的控件省时间
http://www.gemboxsoftware.com/
my blog
http://ufo-crackerx.blog.163.com/

Answer 3

没有创建工作薄：
可以利用以下创建工作薄
 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
 sheet.Name = "Sheet1";

Answer 4

或者你换office 2007 可以放100W行

Answer 5

public class ExcelExport
    {
        public ExcelExport(string SQLCOndition, string title, string city)
        {            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel._Workbook xBk;
            Microsoft.Office.Interop.Excel._Worksheet xSt;
            Microsoft.Office.Interop.Excel._QueryTable xQt;            SqlConnection tmpUpdConn = new SqlConnection(@"Server=" + ConfigurationManager.AppSettings["DBAddr"].ToString() + ";Initial Catalog=" + ConfigurationManager.AppSettings["DBName"].ToString() + ";Uid=" + ConfigurationManager.AppSettings["UserName"].ToString() + ";Pwd=" + ConfigurationManager.AppSettings["UserPwd"].ToString() + ";");
            tmpUpdConn.Open();
            SqlCommand tmpUpdCmd = new SqlCommand(SQLCOndition, tmpUpdConn);
            tmpUpdCmd.CommandTimeout = 6000;
            SqlDataAdapter tmpada = new SqlDataAdapter(tmpUpdCmd);
            DataSet tmpds = new DataSet();
            tmpada.Fill(tmpds);
            System.Data.DataTable tmpt = tmpds.Tables[0];
            tmpUpdConn.Close();
            CreateExcel_51.App_Code.Global.C += tmpt.Rows.Count;
            if (tmpt.Rows.Count > 5000)
            {
                CreateExcel_51.App_Code.Global.isOutOfMem = true;
                GC.Collect();
                tmpds.Dispose();
                tmpada.Dispose();
                tmpt.Dispose();
                return;
            }            if (tmpt.Rows.Count <= 0)
            {
                CreateExcel_51.App_Code.Global.isNull = true;
                GC.Collect();
                tmpds.Dispose();
                tmpada.Dispose();
                tmpt.Dispose();
                return;
            }            string Conn = "ODBC;DRIVER=SQL Server;SERVER=" + ConfigurationManager.AppSettings["DBAddr"].ToString() + ";UID=" + ConfigurationManager.AppSettings["UserName"].ToString() + ";PWD=" + ConfigurationManager.AppSettings["UserPwd"].ToString() + ";DATABASE=" + ConfigurationManager.AppSettings["DBName"].ToString() + "";
            string Select = SQLCOndition;
            excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;
            xQt = xSt.QueryTables.Add(Conn, xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]), Select);
            xQt.Name = "";
            xQt.FieldNames = true;
            xQt.RowNumbers = false;
            xQt.FillAdjacentFormulas = false;
            xQt.PreserveFormatting = false;
            xQt.BackgroundQuery = false;
            xQt.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells;
            xQt.AdjustColumnWidth = true;
            xQt.RefreshPeriod = 0;
            xQt.PreserveColumnInfo = true;
            xQt.Refresh(xQt.BackgroundQuery);
            string Dir = "f:\\test\\" + ConfigurationManager.AppSettings["ExcelDir"].ToString() ;
            string CityFile = city + "\\";
            string FileName = CreateExcel_51.App_Code.Global.n + "_Profile.xls";            string ExcelFileName = Dir  + FileName;
            while (File.Exists(ExcelFileName))
            {
                FileName = CreateExcel_51.App_Code.Global.n + 1 + "_Profile.xls";
                ExcelFileName = Dir  + FileName;
                CreateExcel_51.App_Code.Global.n += 1;
            }
            excel.ActiveWorkbook.SaveCopyAs(ExcelFileName);
            excel.DisplayAlerts = false;
            excel.Quit();
            GC.Collect();
            Console.WriteLine("Create Excel " + ConfigurationManager.AppSettings["DBName"].ToString() + " {0}: {1}_profile.xls,CityData:{3}/{2}", city, CreateExcel_51.App_Code.Global.n, Convert.ToInt32((string)CreateExcel_51.App_Code.HATable.htCities[city]), CreateExcel_51.App_Code.Global.C);
        }
    }
调用public void CreateExcel()
        {
            //Cities = Cities.Trim();
            //string[] sArray = Cities.Split('|');
            //foreach (string t in sArray)
            //{
                //if (ConfigurationManager.AppSettings["SIDCity"].ToString() == t)
                //{
                //    S = Convert.ToInt32(ConfigurationManager.AppSettings["SidData"].ToString());
                //    E = Convert.ToInt32(ConfigurationManager.AppSettings["SidData"].ToString()) + 200;
                //}
                for (; ; )
                {
                    S += 200;
                    E += 200;
                    DataTableToExcel.ExcelExport ex = new DataTableToExcel.ExcelExport(@"SELECT * FROM tab
                                WHERE  id>='" + S + "' AND id<'" + E + "'", "", "");
                    //975000 975200
                    if (CreateExcel_51.App_Code.Global.C >= 46427)//Convert.ToInt32((string)CreateExcel_51.App_Code.HATable.htCities[t]))
                    {
                        S = -200;
                        E = 0;
                        CreateExcel_51.App_Code.Global.C = 0;
                        CreateExcel_51.App_Code.Global.n = 1;
                        break;
                    }
                    if (CreateExcel_51.App_Code.Global.isNull)
                    {
                        Console.WriteLine( "SID:{0} EID:{1} DataTable isNULL,已经写入的数据行数:{2}", S, E, CreateExcel_51.App_Code.Global.C);
                        CreateExcel_51.App_Code.Global.isNull = false;
                    }
                    if (CreateExcel_51.App_Code.Global.isOutOfMem)
                    {
                        Console.WriteLine("SID:{0} EID:{1} DataTable OutOfMemory", S, E);
                        FileStream fs = new FileStream("f:\\test\\" + ConfigurationManager.AppSettings["ExcelDir"].ToString() + "\\Log.txt", FileMode.Append);
                        StreamWriter sw = new StreamWriter(fs, Encoding.Default);
                        sw.Write("OutOfMemory: " + S + " " + E + " " + CreateExcel_51.App_Code.Global.n + "_profile.xls" + "\r\n");
                        sw.Close();
                        fs.Close();
                        CreateExcel_51.App_Code.Global.isOutOfMem = false;
                    }
                }
            //}
            Console.WriteLine(ConfigurationManager.AppSettings["DBName"].ToString() + " Create Excel Complated");
            Console.ReadKey();
        }

Answer 6

我把部分代码改成了：
Excel.Worksheet sheet = (Excel._Worksheet)workBook.Worksheets.ActiveSheet;
 sheet.Name = "Sheet"+y.ToString();

Answer 7

excel book 默认就3张sheet 如果y>3就会找不到了吧，而且lz的代码没有add sheet啊

Answer 8

结果发现被保存成几个excel文件了，一个是自定义的，然后是sheet2,sheet3...

Answer 9

使用Excel Com 组件 可以创建多个sheet的！_excel.Worksheet tsheet1 = (_excel.Worksheet)wbook.ActiveSheet;
_excel.Worksheet tsheet2 = (_excel.Worksheet)wbook.ActiveSheet;另外我看见你使用了GC.Collect();你参考下关于释放Excel资源的问题的一个帖子：http://topic.csdn.net/u/20100520/12/7e077e0b-6336-4972-bd54-c3b48338f9ba.html

Answer 10

不过我用这个方法创建的多个sheet不在同一个Excel文件里。感觉离成功越来越近了，HOHO

Answer 11

//这样就没问题
_excel.Worksheet tsheet = (_excel.Worksheet)wbook.Worksheets[1];
_excel.Worksheet tsheet1 = (_excel.Worksheet)wbook.Worksheets[2];

Answer 12

首先一个Workbook默认创建的时候就有3个worksheet, 所以代码中要处理:int pagecount = 0;
foreach (Worksheet s in workbook.Worksheets)
{
   SaveData(s, data);  // 具体填一个worksheet中数据的方法
   pagecount ++;
}然后就可以用workbook.Worksheets.Add()创建新的worksheet了:for (; pagecount < max_pagenumber; pagecount ++)
{
   Worksheet s = workbook.Worksheets.Add();
   SaveData(s, data);  // 具体填一个worksheet中数据的方法
}
......
workbooks.SaveAs("testexcel");