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

[ASP.net教程]csharp: Export DataSet into Excel and import all the Excel sheets to DataSet


 /// <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();    }

  

 /// <summary>    /// EXCEL表的所有工作表导入到DataSet    /// 涂聚文 Microsoft.ACE.OLEDB.12.0    /// Geovin Du    /// </summary>    /// <param name="fileName"></param>    /// <returns></returns>    static DataSet  ImportExcelParse(string fileName)    {      string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);      DataSet data = new DataSet();      foreach (var sheetName in GetExcelSheetNames(connectionString))      {        using (OleDbConnection con = new OleDbConnection(connectionString))        {          var dataTable = new DataTable();          string query = string.Format("SELECT * FROM [{0}]", sheetName);          con.Open();          OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);          adapter.Fill(dataTable);          data.Tables.Add(dataTable);        }      }      return data;    }    /// <summary>    /// 读取所有工作表名    /// </summary>    /// <param name="connectionString"></param>    /// <returns></returns>    static string[] GetExcelSheetNames(string connectionString)    {      OleDbConnection con = null;      DataTable dt = null;      con = new OleDbConnection(connectionString);      con.Open();      dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);      if (dt == null)      {        return null;      }      String[] excelSheetNames = new String[dt.Rows.Count];      int i = 0;      foreach (DataRow row in dt.Rows)      {        excelSheetNames[i] = row["TABLE_NAME"].ToString();        i++;      }      return excelSheetNames;    }

  

 /// <summary>    /// 添加图片    /// 涂聚文    /// </summary>    /// <param name="dt"></param>    protected void ExportExcelImg(System.Data.DataTable dt)     {       if (dt == null || dt.Rows.Count == 0) return;       Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();         if (xlApp == null)       {         return;       }       xlApp.Application.Workbooks.Add(true);       string timeMark = DateTime.Now.ToString("yyyyMMddHHmmss");       string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "geovindu" + timeMark + ".xlsx");       System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;       System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");       Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;       Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);       Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];       Microsoft.Office.Interop.Excel.Range range;       System.Reflection.Missing miss = System.Reflection.Missing.Value;       long totalCount = dt.Rows.Count;       long rowRead = 0;       float percent = 0;       for (int i = 0; i < dt.Columns.Count; i++)       {         worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;         range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];         range.Interior.ColorIndex = 15;       }       for (int r = 0; r < dt.Rows.Count; r++)       {         for (int i = 0; i < dt.Columns.Count; i++)         {           try           {             worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();           }           catch           {             worksheet.Cells[r + 2, i + 1] = 		    dt.Rows[r][i].ToString().Replace("=", "");           }         }         rowRead++;         percent = ((float)(100 * rowRead)) / totalCount;       }       string strimg =Application.StartupPath+@"/IMG_6851.JPG";       worksheet.Shapes.AddPicture(strimg, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300);       //在添加的图片上加文字       worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "涂聚文写上", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse,Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);       xlApp.Visible = true;       workbook.SaveAs(FilePath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);       workbook.Close(false, miss, miss);       //excelWorkBook.Save();       workbooks.Close();       xlApp.Quit();     }