Excel匯入匯出整理

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
}

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
}

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
}

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
}

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
}

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
        }

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
        }

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
}

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
        }

分享----适度设计

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

Excel匯入匯出整理

公告