Excel匯入匯出整理

http://www.dotblogs.com.tw/killysss/archive/2009/11/23/12111.aspx

Excel匯入匯出整理

參考資料很多=.=，感謝各位大大無私的分享（洪爺早餐店…）

整理一下結果

取得所有worksheet名稱

view source
print?
01 /// <summary>
02 /// Gets the name of all sheet.
03 /// </summary>
04 /// <param name="FName">Name of the F.</param>
05 /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
06 /// <returns></returns>
07 public static List<string> GetAllSheetName(string FName, bool HasFieldName)
08 {
09     string strConn;
10     List<string> sTBList = new List<string>();
11     if (HasFieldName)
12         /*如果Excel中的第一列為欄名,則寫成*/
13         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/"";
14     else
15         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/"";
16     OleDbConnection odc = new OleDbConnection(strConn);
17     odc.Open();
18     DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
19     if (dt.Rows.Count > 0)
20     {
21         foreach (DataRow dr in dt.Rows)
22         {
23             sTBList.Add(dr["TABLE_NAME"].ToString().Replace("$",string.Empty));
24         }
25     }
26     return sTBList;
27 }

取得第一個worksheet

view source
print?
01 /// <summary>
02 /// Gets the first name of the sheet.
03 /// </summary>
04 /// <param name="FName">Name of the F.</param>
05 /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
06 /// <returns></returns>
07 public static string GetFirstSheetName(string FName, bool HasFieldName)
08 {
09     string strConn;
10     List<string> sTBList = new List<string>();
11     if (HasFieldName)
12         /*如果Excel中的第一列為欄名,則寫成*/
13         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/"";
14     else
15         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/"";
16     OleDbConnection odc = new OleDbConnection(strConn);
17     odc.Open();
18     DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
19     if (dt.Rows.Count > 0)
20     {
21         foreach (DataRow dr in dt.Rows)
22         {
23             sTBList.Add(dr["TABLE_NAME"].ToString().Replace("$", string.Empty));
24         }
25     }
26     return sTBList[0];
27 }

匯入excel資料到DataTable

view source
print?
01 /// <summary>
02 /// Imports the excel.
03 /// </summary>
04 /// <param name="FName">Name of the F.</param>
05 /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
06 /// <returns></returns>
07 public static System.Data.DataTable ImportExcel(string FName, bool HasFieldName)
08 {
09     try
10     {
11         List<string> sTBList = new List<string>();
12         string strConn;
13         /*  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
14           "Data Source=" + FName +
15           ";Extended Properties=Excel 8.0;";*/
16         if (HasFieldName)
17             /*如果Excel中的第一列為欄名,則寫成*/
18             strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/"";
19         else
20             strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/"";
21         OleDbConnection odc = new OleDbConnection(strConn);
22         odc.Open();
23         DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                
24         if (dt.Rows.Count > 0)
25         {
26             foreach (DataRow dr in dt.Rows)
27             {
28                 sTBList.Add(dr["TABLE_NAME"].ToString());
29             }
30         }
31         OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + sTBList[0] + "]", strConn);
32         //OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
33         System.Data.DataTable myDataSet = new System.Data.DataTable();
34         myCommand.Fill(myDataSet);
35         odc.Close();
36         return myDataSet;
37     }
38     catch (Exception e)
39     {               
40         return null;
41     }
42 }

匯入excel資料到List<string>

view source
print?
01 /// <summary>
02 /// Imports the excel to list.
03 /// </summary>
04 /// <param name="FName">Name of the F.</param>
05 /// <param name="TableName">Name of the table.</param>
06 /// <param name="SheetName">Name of the sheet.</param>
07 /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
08 /// <param name="delimiter">The delimiter.</param>
09 /// <returns></returns>
10 public static List<string> ImportExcelToList(string FName, string TableName, bool HasFieldName, string delimiter)
11 {
12     List<string> result = new List<string>();
13     string strConn;
14     /*  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
15       "Data Source=" + FName +
16       ";Extended Properties=Excel 8.0;";*/
17     if (HasFieldName)
18         /*如果Excel中的第一列為欄名,則寫成*/
19         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/"";
20     else
21         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/"";
22     using (OleDbConnection cn = new OleDbConnection(strConn))
23     {
24         cn.Open();
25         List<string> sTBList = new List<string>();
26         DataTable dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                
27         if (dt.Rows.Count > 0)
28         {
29             foreach (DataRow dr in dt.Rows)
30             {
31                 sTBList.Add(dr["TABLE_NAME"].ToString());
32             }
33         }
34         //工作表名稱需要加$字串
35         //string qs = "select * from[" + SheetName + "$]";
36         string qs = "select * from[" + sTBList[0] + "]";
37         try
38         {
39             using (OleDbCommand cmd = new OleDbCommand(qs, cn))
40             {
41                 using (OleDbDataReader dr = cmd.ExecuteReader())
42                 {
43                     while (dr.Read())
44                     {
45                         string TempString = "";
46                         //int Col = dr.FieldCount;
47                         for (int i = 0; i < dr.FieldCount; i++)
48                         {
49                             TempString += dr[i].ToString() + delimiter;
50                         }
51                         result.Add(TempString);
52                     }
53                 }
54             }
55         }
56         catch (Exception ex)
57         {
58             MessageBox.Show(ex.Message);
59         }
60           
61     }
62     return result;
63 }

建立worksheet

view source
print?
01 /// <summary>
02 /// Creates the excel sheet.
03 /// </summary>
04 /// <param name="FName">Name of the F.</param>
05 /// <param name="TableName">Name of the table.</param>
06 /// <param name="SheetName">Name of the sheet.</param>
07 /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
08 public static void CreateExcelSheet(string FName, string TableName, string SheetName, bool HasFieldName)
09 {
10     string strConn;
11     /*  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
12       "Data Source=" + FName +
13       ";Extended Properties=Excel 8.0;";*/
14     if (HasFieldName)
15         /*如果Excel中的第一列為欄名,則寫成*/
16         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/"";
17     else
18         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/"";
19       
20     string ColumnName = NewMediaTest1.Model.DbOperation.GetColumnName(TableName);
21     string[] ColTemp = ColumnName.Split(',');                                                  
22     // 用OldDb方式去建table 
23     string ExcelColumnName = string.Join(" text , ", ColTemp);  
24     ExcelColumnName += " text ";
25       using (OleDbConnection cn = new OleDbConnection(strConn))
26      {
27          cn.Open();
28          //工作表名稱需要加$字串
29            //建立工作表
30                  string qs = " CREATE TABLE " + SheetName + " (" + ExcelColumnName + " ) ";                         
31                  try
32                  {                            
33                      using (OleDbCommand cmd = new OleDbCommand(qs, cn))
34                      {
35                          //新增Excel工作表
36                          cmd.ExecuteNonQuery();                                
37                      }
38                  }
39                  catch (Exception ex)
40                  {
41                      MessageBox.Show(ex.Message);
42                  }
43      }
44 }

在worksheet中新增一行

view source
print?
01 /// <summary>
02         /// Inserts the single line excel sheet.
03         /// </summary>
04         /// <param name="FName">Name of the F.</param>
05         /// <param name="SheetName">Name of the sheet.</param>
06         /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
07         /// <param name="InsertData">The insert data.</param>
08         public static void InsertSingleLineExcelSheet(string FName, string SheetName, bool HasFieldName, params string[] InsertData)
09         {
10             string strConn;
11             /*  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
12               "Data Source=" + FName +
13               ";Extended Properties=Excel 8.0;";*/
14             if (HasFieldName)
15                 /*如果Excel中的第一列為欄名,則寫成*/
16                 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/"";
17             else
18                 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/"";
19             string InsertString = NewMediaTest1.Model.DbOperation.InsertDataString(InsertData);          
20             // 用OldDb方式去建table 
21             using (OleDbConnection cn = new OleDbConnection(strConn))
22             {
23                 cn.Open();
24                 //工作表名稱需要加$字串
25                 //建立工作表 
26                 string qs = "INSERT INTO [" + SheetName + "$] VALUES( " + InsertString + " )";
27                 try
28                 {
29                     using (OleDbCommand cmd = new OleDbCommand(qs, cn))
30                     {
31   
32                         //增加資料
33   
34                         //  cmd.CommandText = "INSERT INTO [" + SheetName + "$] VALUES( " +InsertString+" )";
35   
36                         cmd.ExecuteNonQuery();
37   
38                     }
39   
40                 }
41   
42                 catch (Exception ex)
43                 {
44   
45                     MessageBox.Show(ex.Message);
46   
47                 }
48   
49             }
50   
51         }

更新worksheet一行

view source
print?
01 /// <summary>
02         /// Updates the sheet single line.
03         /// </summary>
04         /// <param name="FName">Name of the F.</param>
05         /// <param name="TableName">Name of the table.</param>
06         /// <param name="SheetName">Name of the sheet.</param>
07         /// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
08         /// <param name="Condition">The condition.</param>
09         /// <param name="UpdateData">The update data.</param>
10         public static void UpdateSheetSingleLine(string FName,string TableName, string SheetName,bool HasFieldName,string Condition, params string[] UpdateData)
11         {
12             string strConn;            
13             if (HasFieldName)
14                 /*如果Excel中的第一列為欄名,則寫成*/
15                 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/"";
16             else
17                 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/"";
18             string UpdateDataString = NewMediaTest1.Model.DbOperation.UpdateDataString(UpdateData, TableName);                        
19             string WhereCondition = "";
20             if(Condition != "")
21                 WhereCondition = " where " + Condition ;
22             string qs1 = "Update [" + SheetName + "$] set " + UpdateDataString + WhereCondition;           
23             //1.建立連線
24             using (OleDbConnection cn = new OleDbConnection(strConn))
25             {
26                 cn.Open();
27                 //2.建立OleDbCommand物件
28                 using (OleDbCommand cm = new OleDbCommand(qs1, cn))
29                 {
30                     cm.ExecuteNonQuery();
31                 }
32             }
33   
34         }

匯出excel for windowfrom

view source
print?
01 [DllImport("User32.dll", CharSet = CharSet.Auto)]
02 public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
03 [DllImport("User32.dll", CharSet = CharSet.Auto)]
04 public static extern int FindWindow(string strclassName, string strWindowName);
05 /// <summary>
06 /// Exports the excel.
07 /// </summary>
08 /// <param name="ds">The ds.</param>
09 /// <param name="ListName">Name of the list.</param>
10 /// <param name="AddTitle">if set to <c>true</c> [add title].</param>
11 public static void ExportExcel(System.Data.DataTable ds, string[] ListName, bool AddTitle)
12 {
13     try
14     {
15         Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
16         excel.Caption = "ExportExcel";
17         excel.Application.Workbooks.Add(true);
18         Microsoft.Office.Interop.Excel.Worksheet ExcelSheets;
19         ExcelSheets = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets.get_Item(1);
20         if (AddTitle == true)
21         {
22             for (int q = 0; q < ListName.Length; q++)
23                 ExcelSheets.Cells[1, q + 1] = ListName[q].ToString();
24         }
25         object missing = Missing.Value;
26         excel.DisplayAlerts = false;
27         excel.Visible = false;
28         int RoLength = ds.Rows.Count;
29         int i, j;
30         for (i = 0; i < RoLength; i++)
31         {
32             for (j = 0; j < ListName.Length; j++)
33             {
34                 string value = ds.Rows[i][j].ToString();
35                 if (AddTitle == true)
36                     ExcelSheets.Cells[i + 2, j + 1] = value;
37                 else
38                     ExcelSheets.Cells[i + 1, j + 1] = value;
39             }
40         }//for 
41         SaveFileDialog saveFileDialog = new SaveFileDialog();
42         saveFileDialog.Filter = "Excel files(*.xls)|*.xls|All files(*.*)|*.*";
43         saveFileDialog.Title = "test";
44         saveFileDialog.FilterIndex = 1;
45         saveFileDialog.RestoreDirectory = true;
46         if (saveFileDialog.ShowDialog() == DialogResult.OK)
47         {
48             ExcelSheets.SaveAs(saveFileDialog.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, Type.Missing, Type.Missing, false, false, false, Type.Missing, Type.Missing, true);
49         }//if()
50         //針對標題去找EXCEL程式的PID(唯一值),最後會依靠PID去關閉EXCEL程式
51         IntPtr t = new IntPtr(FindWindow("XLMAIN", excel.Caption));
52         int k = 0;
53         GetWindowThreadProcessId(t, out k);
54         System.Diagnostics.Process p_excel = System.Diagnostics.Process.GetProcessById(k);
55         excel.Workbooks.Close();
56         excel.Quit();
57         p_excel.Kill();
58         excel = null;
59         p_excel = null;
60     }//try 
61     catch (System.Exception e)
62     {
63        return null;
64     }
65 }

合併worksheet

view source
print?
01 /// <summary>
02         /// Merges the sheet from file.
03         /// </summary>
04         /// <param name="SourceFile1">The source file1.</param>
05         /// <param name="SourceFile2">The source file2.</param>
06         /// <param name="Destiation">The destiation.</param>
07         /// <param name="AppendInFirst">if set to <c>true</c> [append in first].</param>
08         /// <param name="Source2SheetName">Name of the source2 sheet.</param>
09         public static void MergeSheetFromFile(string SourceFile1, string SourceFile2, string Destiation,bool AppendInFirst,string Source2SheetName)
10         {
11             object missing = Missing.Value;
12             string oFirstXls = SourceFile1;//excel檔1  
13             string oSecondXls = SourceFile2;//excel檔2  
14             string oOutputXls = Destiation;//合併檔,excel檔3  
15             string SheetName = "";
16             if (Source2SheetName == "")
17                 SheetName = "Sheet1";
18             else
19                 SheetName = string.Copy(Source2SheetName);
20   
21             Excel.Application excelApp = new Excel.ApplicationClass();
22             Excel.Workbook wbook1 = excelApp.Workbooks.Open(oFirstXls, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);//載入excel檔1
23             Excel.Workbook wbook2 = excelApp.Workbooks.Open(oSecondXls, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);//載入excel檔2
24             if(!AppendInFirst)
25                 wbook1.Worksheets.Copy(missing, wbook2.Sheets[SheetName]);//在excel檔2的Sheet1之後插入所有excel檔1的Sheet
26             else
27                 wbook1.Worksheets.Copy(wbook2.Sheets[SheetName], missing);//在excel檔2的Sheet1之前插入所有excel檔1的Sheet
28             wbook2.SaveAs(oOutputXls, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);//另存excel檔3
29             wbook1.Close(missing, missing, missing);
30             wbook2.Close(missing, missing, missing);
31             excelApp.Quit();
32         }
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/marryZhan/p/2213946.html