关于用COM.Excel如何批量导出多个工作薄的Excel

Question

用Com.Excel导出只有一个工作薄的Excel代码如下所示，请问怎么改代码，能导出一个Excel文件有多个工作薄 /*使用示例：
 * DataSet ds=(DataSet)Session["AdBrowseHitDayList"];//本ds中有多个Dt
string ExcelFolder=Assistant.GetConfigString("ExcelFolder");
string FilePath=Server.MapPath(".")+"\\"+ExcelFolder+"\\";

//生成列的中文对应表
Hashtable nameList = new Hashtable();
nameList.Add("ADID", "广告编码");
nameList.Add("ADName", "广告名称");
nameList.Add("year", "年");
nameList.Add("month", "月");
nameList.Add("browsum", "显示数");
nameList.Add("hitsum", "点击数");
nameList.Add("BrowsinglIP", "独立IP显示");
nameList.Add("HitsinglIP", "独立IP点击");
//利用excel对象
DataToExcel dte=new DataToExcel();
string filename="";
try
{
if(ds.Tables[0].Rows.Count>0)
{
filename=dte.DataExcel(ds.Tables[0],"标题",FilePath,nameList);
}
}
catch
{
//dte.KillExcelProcess();
}

if(filename!="")
{
Response.Redirect(ExcelFolder+"\\"+filename,true);
}
 * 
 * */   public string DataExcel(System.Data.DataTable dt, string strTitle, string FilePath, Hashtable nameList)
        {
            COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            excel.CreateFile(FilePath + filename);
            excel.PrintGridLines = false;            COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
            COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
            COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
            COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;            double height = 1.5;
            excel.SetMargin(ref mt1, ref height);
            excel.SetMargin(ref mt2, ref height);
            excel.SetMargin(ref mt3, ref height);
            excel.SetMargin(ref mt4, ref height);            COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
            string font = "宋体";
            short fontsize = 9;
            excel.SetFont(ref font, ref fontsize, ref ff);            byte b1 = 1,
                b2 = 12;
            short s3 = 12;
            excel.SetColumnWidth(ref b1, ref b2, ref s3);            string header = "页眉";
            string footer = "页脚";
            excel.SetHeader(ref header);
            excel.SetFooter(ref footer);
            COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
            COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
            COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
            COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;            // 报表标题
            int cellformat = 1;
            // int rowindex = 1,colindex = 3;
            // object title = (object)strTitle;
            // excel.WriteValue(ref vt, ref cf, ref ca, ref chl,ref rowindex,ref colindex,ref title,ref cellformat);            int rowIndex = 1;//起始行
            int colIndex = 0;            //取得列标题
            foreach (DataColumn colhead in dt.Columns)
            {
                colIndex++;
                string name = colhead.ColumnName.Trim();
                object namestr = (object)name;
                IDictionaryEnumerator Enum = nameList.GetEnumerator();
                while (Enum.MoveNext())
                {
                    if (Enum.Key.ToString().Trim() == name)
                    {
                        namestr = Enum.Value;
                    }
                }
                excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
            }            //取得表格中的数据
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        object str = (object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); ;
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                    }
                    else
                    {
                        object str = (object)row[col.ColumnName].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                    }
                }
            }
            int ret = excel.CloseFile();            // if(ret!=0)
            // {
            // //MessageBox.Show(this,"Error!");
            // }
            // else
            // {
            // //MessageBox.Show(this,"请打开文件c:\\test.xls!");
            // }
            return filename;        }

Answer 1

你是用的现成的类还是什么？
找找看应该有创建worksheet的方法

Answer 2

string currentSheet = "Sheet1 ";
Excel.Workshee excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
Excel.Worksheet objSheet=(Excel.Worksheet)   newWorkbook.Worksheets.Add(excelWorksheet,Type.Missing,Type.Missing,Type.Missing); 

Answer 3

建议楼主用 NPOI.dll
方法就不挨个说了，网上一搜都是一堆

Answer 4

想用Com.Excel实现，没办法，最后用其他方式做了