net中操作excel.如何动态添加多个工作表

Question

net中操作excel时,请问如何动态添加多个工作表,并填充数据

Answer 1

.NET操作excel最好用VB.NET写
添加工作表就是sheets.add方法

Answer 2

oledb
create table
insert

Answer 3

前段时间乱写的.估计你能用上..动态添加sheetexcel.Sheets.Add(Type.Missing, workSheet, Type.Missing, Type.Missing);//这个是添加sheet
lz看一下吧..
public void CreateExcelFile(string path, string orBatchid, string batchName)
        {
            DataSet ds = prm.GetExportToExcelData(Convert.ToInt32(orBatchid));            Excel.ApplicationClass excel = new Excel.ApplicationClass();
            Excel.Worksheet workSheet;
            excel.Application.Workbooks.Add(true);            if (ds.Tables[0].Rows.Count == ds.Tables.Count - 1)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)//sheet name
                {
                    workSheet = (Excel.Worksheet)excel.Sheets.get_Item(i + 1);
                    workSheet.Name = ds.Tables[0].Rows[i][0].ToString() + "_" + ds.Tables[0].Rows[i][1].ToString();//sheet name                    int rowIndex = 1;
                    int colIndex = 0;                    foreach (DataColumn col in ds.Tables[i + 1].Columns)// fill table column
                    {
                        colIndex++;
                        workSheet.Cells[1, colIndex] = col.ColumnName;                       
                    }
                    
                    workSheet.Cells[1, ++colIndex] = ds.Tables[0].Rows[i][0].ToString() + " " + ds.Tables[0].Rows[i][1].ToString();                    foreach (DataRow row in ds.Tables[i + 1].Rows)//fill data
                    {
                        rowIndex++;
                        colIndex = 0;
                        foreach (DataColumn col in ds.Tables[i + 1].Columns)
                        {
                            colIndex++;
                            workSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                    }                    workSheet.Cells[rowIndex + 2, 1] = "---------End of Data---------";
                    
                    workSheet.Cells.Font.Name = "Arial";
                    workSheet.Cells.Font.Size = 10;
                    workSheet.Cells.EntireColumn.AutoFit();                    if (i < ds.Tables[0].Rows.Count - 1)
                    {
                        excel.Sheets.Add(Type.Missing, workSheet, Type.Missing, Type.Missing);
                    }
                }                excel.Visible = false;
                excel.DisplayAlerts = false;
                excel.ActiveWorkbook.SaveAs(path + "\\" + batchName + DateTime.Now.ToString("yyyyMMdd"), Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excel.Quit();
                excel = null;
                GC.Collect();
            }
        }