Excel匯入匯出整理

于 2010-07-29 10:28:00 发布
阅读量1.4k 收藏 2
点赞数
分类专栏： C# 文章标签： excel properties string null exception table

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>
07public 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>
07public 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>
07public 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>
10public 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>
08public 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)]
02public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
03[DllImport("User32.dll", CharSet = CharSet.Auto)]
04public 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>
11public 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        }
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值