csharp: Export DataSet into Excel and import all the Excel sheets to DataSet

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76

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

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

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

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

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

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87

/// <summary> /// GirdView转换成DataTable /// 20150813 /// 涂聚文 /// </summary> /// <param name="dgv"></param> /// <returns></returns> public static DataTable GetGirdViewToTableHeaderText(DataGridView dgv) {     DataTable dt = new DataTable();     try     {                        for (int count = 0; count < dgv.Columns.Count; count++)         {             if (dgv.Columns[count].Visible == true)             {                 DataColumn dc = new DataColumn(dgv.Columns[count].HeaderText);                 dt.Columns.Add(dc);             }           }         for (int count = 0; count < dgv.Rows.Count; count++)         {             DataRow dr = dt.NewRow();             for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)             {                 if (dgv.Columns[count].Visible == true)                 {                     //if (dgv[countsub, count].ValueType == typeof(string))                     //{                     //    dr[countsub] = "'" + dgv.Rows[count].Cells[countsub].Value;                     //}                     //else                     //{                         dr[countsub] = dgv.Rows[count].Cells[countsub].Value;                     //}                                       }               }             dt.Rows.Add(dr);             }     }     catch (Exception ex)     {         ex.Message.ToString();     }     return dt; }   /// <summary> /// List 转DataTable /// 涂聚文 /// </summary> /// <param name="list"></param> /// <returns></returns>  public static DataTable ConvertListToDataTable(List<string[]> list) {     // New table.     DataTable table = new DataTable();       // Get max columns.     int columns = 0;     foreach (var array in list)     {         if (array.Length > columns)         {             columns = array.Length;         }     }       // Add columns.     for (int i = 0; i < columns; i++)     {         table.Columns.Add();     }       // Add rows.     foreach (var array in list)     {         table.Rows.Add(array);     }       return table; }

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105

/// <summary> /// 涂聚文 /// 2015.08.18 /// </summary> /// <param name="dataGridView"></param> /// <returns></returns> public static DataTable DataGridViewToDataTable(DataGridView dataGridView) {       DataTable dt = new DataTable();     try     {         foreach (DataGridViewColumn col in dataGridView.Columns)         {             if (col.Visible == true)             {                 dt.Columns.Add(col.HeaderText, col.ValueType);             }         }         foreach (DataGridViewRow gridRow in dataGridView.Rows)         {             if (gridRow.IsNewRow)                 continue;             int irow = 0;             DataRow dtRow = dt.NewRow();             for (int i1 = 0; i1 < dataGridView.Columns.Count; i1++)             {                 if (dataGridView.Columns[i1].Visible == true)                 {                     dtRow[irow] = (gridRow.Cells[i1].Value == null ? DBNull.Value : gridRow.Cells[i1].Value);                     irow++;                 }               }             dt.Rows.Add(dtRow);         }         //ds.Tables.Add(dt);         //System.Diagnostics.Debugger.Break();     }     catch (Exception ex)     {        MessageBox.Show(ex.Message.ToString());     }     return dt;   } /// <summary> /// 涂聚文 /// </summary> /// <param name="dgv"></param> /// <returns></returns> public static DataTable GetGirdViewToTableHeaderText(DataGridView dgv) {       //DataGridViewColumnCollection       DataTable dt = new DataTable();     try     {         //标题         for (int count = 0; count < dgv.Columns.Count; count++)         {             if (dgv.Columns[count].Visible == true)             {                 DataColumn dc = new DataColumn(dgv.Columns[count].HeaderText, dgv.Columns[count].ValueType);                 dt.Columns.Add(dc);             }           }           for (int count = 0; count < dgv.Rows.Count; count++)         {             int irow = 0;            DataRow dr = dt.NewRow();              //DataRow dr = dt.Rows.Add();             for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)             {                 if (dgv.Columns[countsub].Visible == true)                 {                     //if (dgv[countsub, count].ValueType == typeof(string))                     //{                     //    dr[countsub] = "'" + dgv.Rows[count].Cells[countsub].Value;                     //}                     //elsedt.Rows[countsub][count]                     //{                     //dr[countsub] = dgv[countsub, count].Value;// dgv.Rows[count].Cells[countsub].Value;                     dr[irow] = dgv.Rows[count].Cells[countsub].Value; // dgv[countsub, count].Value;                     //}                        irow++;                 }               }             dt.Rows.Add(dr);                         }     }     catch (Exception ex)     {         MessageBox.Show(ex.Message.ToString());     }     return dt; }