第522篇--DataTable to Excel C#

今天药忘吃喽~ 2021-11-01 08:48 234阅读 0赞

The code is quite simpke, just have a look.

Add reference to the following dlls:

Interop.Microsoft.Office.Core.dll  
Interop.Microsoft.Office.Interop.Excel.dll

Download dlls: [https://skydrive.live.com/\#cid=6B286CBEF1610557&id=6B286CBEF1610557!677][https_skydrive.live.com_cid_6B286CBEF1610557_id_6B286CBEF1610557_677]

class Program
        {
            static void Main(string[] args)
            {
                System.Data.DataTable table = new System.Data.DataTable();
                DataColumn column1 = new DataColumn() { ColumnName="Column1"};
                DataColumn column2 = new DataColumn() { ColumnName = "Column2" };
    
                table.Columns.Add(column1);
                table.Columns.Add(column2);
                table.Rows.Add("1","2");
                table.Rows.Add("3", "4");
                DataTabletoExcel(table, @"C:\temp.XLS");
    
            }
            public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
            {
                if (tmpDataTable == null)
                    return;
                int rowNum = tmpDataTable.Rows.Count;
                int columnNum = tmpDataTable.Columns.Count;
                int rowIndex = 1;
                int columnIndex = 0;
                Application xlApp = new ApplicationClass();
                xlApp.DefaultFilePath = "";
                xlApp.DisplayAlerts = true;
                xlApp.SheetsInNewWorkbook = 1;
                Workbook xlBook = xlApp.Workbooks.Add(true);
                //将DataTable的列名导入Excel表第一行
                foreach (DataColumn dc in tmpDataTable.Columns)
                {
                    columnIndex++;
                    xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
                }
                //将DataTable中的数据导入Excel中
                for (int i = 0; i < rowNum; i++)
                {
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum; j++)
                    {
                        columnIndex++;
                        xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
                    }
                }
                //xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
                xlBook.SaveCopyAs(strFileName);
            }
        }

Excel to Data:

private static  System.Data.DataTable ExportFromExcelToDataTable()
            {
                System.Data.DataTable excelDataTable = new System.Data.DataTable();
                string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0";
                // Create Connection to Excel Workbook
                using (System.Data.OleDb.OleDbConnection excelConnection =
                      new System.Data.OleDb.OleDbConnection(excelConnectionString))
                {
                    excelConnection.Open();
                    System.Data.DataTable dt = excelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    string[] excelSheet = new String[dt.Rows.Count];
                    int sheet = 0;
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheet[sheet] = row["Table_Name"].ToString();
                        sheet++;
    
                    }
                    excelDataTable.Clear();
                    for (int i = 0; i < excelSheet.Length; i++)
                    {
                        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand
                             ("Select  * FROM [" + excelSheet[i] + "]", excelConnection);
                        System.Data.OleDb.OleDbDataAdapter excelAdapter = new System.Data.OleDb.OleDbDataAdapter();
                        excelAdapter.SelectCommand = command;
                        excelAdapter.Fill(excelDataTable);
                    }
                    excelConnection.Close();
                }
    
                return excelDataTable;
            }

转载于:https://www.cnblogs.com/shanghaijimzhou/archive/2013/03/25/2981503.html

[https_skydrive.live.com_cid_6B286CBEF1610557_id_6B286CBEF1610557_677]: https://skydrive.live.com/#cid=6B286CBEF1610557&id=6B286CBEF1610557!677