程序博客网 > 汽车电脑编程叫什么

Excel匯入匯出整理

来源：互联网 发布：汽车电脑编程叫什么 编辑：程序博客网 时间：2024/04/30 15:24

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        }
汽车电脑编程叫什么 汽车电脑编程叫什么
原创粉丝点击
热门IT博客
vmware 端口转发 sshvmware 端口转发 ssh
linux就业方向linux就业方向
linux 别名
优惠券制作软件
即时通讯办公软件
linux中expect
艾泽拉斯数据库
windows 8.1专业版激活
苹果cms采集插件制作
免费推广淘宝店铺
零基础学算法可行
pcp弹道软件中文版
詹姆斯11年总决赛数据
淘宝网童装呢子
大数据常用算法
软通动力java试题2016
华为 acl 端口
sql双机热备
淘宝助理不显示宝贝
阿里云售后技术支持
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 冷光美白价格 如何才能美白 简单皮肤美白的方法 如何面部美白 怎样能快速美白 美白的小窍门 怎么才能美白皮肤 能美白的方法 一周快速美白小妙招 全身美白针售价 什么东西美白 怎么快速美白皮肤 美容院能美白吗 美白效果好的 什么方法能美白皮肤 快速美白的方法有哪些 全身美白方法大全 美白护肤品哪个好 什么美白产品好 冷光美白多少钱一次 华美水光美白 如何能全身美白 皮肤美白哪家好 美白好用的产品 美白嫩肤要多少钱 美白皮肤要多少钱 冷光美白要多少钱 简单有效美白方法 彩光美白多少钱 淡斑美白产品 全身美白的价格 美白针要多少钱 怎样能美白皮肤 美白小窍门100妙招 美白产品哪个好 全身皮肤美白的方法 有什么美白的方法 一次美白针多少钱 天生黑快速美白的方法 怎样能全身美白 怎么样可以美白全身

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里