你的位置:首页 > ASP.net教程

[ASP.net教程]csharp: Export DataSet into Excel


 /// <summary>    /// Export DataSet into Excel    /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void Form3_Load(object sender, EventArgs e)    {      //Create an Emplyee DataTable      DataTable employeeTable = new DataTable("Employee");      employeeTable.Columns.Add("Employee ID");      employeeTable.Columns.Add("Employee Name");      employeeTable.Rows.Add("1", "涂聚文");      employeeTable.Rows.Add("2", "geovindu");      employeeTable.Rows.Add("3", "李蘢怡");      employeeTable.Rows.Add("4", "ноппчц");      employeeTable.Rows.Add("5", "ニヌネハヒフキカォноппчц");      //Create a Department Table      DataTable departmentTable = new DataTable("Department");      departmentTable.Columns.Add("Department ID");      departmentTable.Columns.Add("Department Name");      departmentTable.Rows.Add("1", "IT");      departmentTable.Rows.Add("2", "HR");      departmentTable.Rows.Add("3", "Finance");      //Create a DataSet with the existing DataTables      DataSet ds = new DataSet("Organization");      ds.Tables.Add(employeeTable);      ds.Tables.Add(departmentTable);      ExportDataSetToExcel(ds);    }    /// <summary>    /// This method takes DataSet as input paramenter and it exports the same to excel    /// </summary>    /// <param name="ds"></param>    private void ExportDataSetToExcel(DataSet ds)    {      //Creae an Excel application instance      //EXCEL组件接口      System.Reflection.Missing miss = System.Reflection.Missing.Value;      Excel.Application excelApp = new Excel.Application();      excelApp.Application.Workbooks.Add(true);      string timeMark = DateTime.Now.ToString("yyyyMMddHHmmss");      string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "geovindu" + timeMark + ".xlsx");      //Create an Excel workbook instance and open it from the predefined location      //Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(FilePath);      Excel.Workbooks books = (Excel.Workbooks)excelApp.Workbooks;      Excel.Workbook excelWorkBook = (Excel.Workbook)books.Add(miss);      foreach (DataTable table in ds.Tables)      {        //Add a new worksheet to workbook with the Datatable name        Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();        excelWorkSheet.Name = table.TableName;        for (int i = 1; i < table.Columns.Count + 1; i++)        {          excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;        }        for (int j = 0; j < table.Rows.Count; j++)        {          for (int k = 0; k < table.Columns.Count; k++)          {            excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();          }        }      }      excelWorkBook.SaveAs(FilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);      excelWorkBook.Close(false, miss, miss);      //excelWorkBook.Save();      books.Close();      excelApp.Quit();    }