使用Excel读写数据

Excel读取数据比较简单，一般直接用ODBC进行读取。

ODBC导入，需要安装AccessDatabaseEngine.exe，这个安装包是区分32位64位的。一般服务器上都需要安装，然后重启IIS

       /// <summary>
        /// 执行导入
        /// </summary>
        /// <param name="strFileName">文件名</param>
        /// <returns>DataSet</returns>
        private static DataTable doImportExcel(string strFileName, string SheetName)
        {
            string strConn;

            if (String.IsNullOrEmpty(strFileName)) return null;
            DataTable dtExcel = new DataTable();
            System.Data.OleDb.OleDbConnection conn = null;
            try
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                  "Data Source=" + strFileName + ";" +
                  "Extended Properties=Excel 12.0;";

                conn = new System.Data.OleDb.OleDbConnection(strConn);
                conn.Open();
                if (String.IsNullOrEmpty(SheetName))
                {
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    if (dt.Rows[0]["TABLE_Name"].ToString().IndexOf("$") < 0)
                    {
                        dt.Rows[0]["TABLE_Name"] += "$";
                    }

                    SheetName = dt.Rows[0]["TABLE_Name"].ToString();
                }
                else
                {
                    SheetName += "$";
                }

                OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "]", strConn);
                ExcelDA.Fill(dtExcel);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                    conn = null;
                }

            }

            return dtExcel;
        }

写入数据到Excel的话麻烦些，有两种方法。

1.还是用ODBC进行操作，首先需要用手动创建一个模板，模板包含数据的各个Fields,然后把数据Update进去。

       /// <summary>
        /// 使用ODBC来写excel.
        /// </summary>
        /// <param name="table"></param>
        /// <param name="template"></param>
        /// <param name="path"></param>
        private static void WriteExcel(DataTable table, string template, string path)
        {
            try
            {
                File.Copy(template, path);

                //先得到EXCEL在DataSet中的结构
                string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
                using (OleDbConnection connection = new OleDbConnection(strCon))
                {
                    string strCom = "select * from [Sheet1$]";
                    connection.Open();
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, connection);
                    OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
                    //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
                    builder.QuotePrefix = "[";     //获取insert语句中保留字符（起始位置）
                    builder.QuoteSuffix = "]"; //获取insert语句中保留字符（结束位置）
                    DataSet dataSet = new DataSet();
                    myCommand.Fill(dataSet, "Table1");
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        //在这里不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。
                        //在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState!=Added
                        DataRow nrow = dataSet.Tables["Table1"].NewRow();
                        for (int j = 0; j < dataSet.Tables[0].Columns.Count; j++)
                        {
                            nrow[j] = table.Rows[i][j];
                        }
                        dataSet.Tables["Table1"].Rows.Add(nrow);
                    }
                    myCommand.Update(dataSet, "Table1");
                }
            }
            catch (Exception ex)
            {
                LogVeiwerHelper.WriteException(ex, "酒店房型同步错误导出");
            }
        }

2.使用Microsoft.Office.Interop.Excel类库来进行操作

这个类库是Office tools中的类库，有丰富的API来进行操作。使用时，需要把此dll的Embeded Interop Types属性设为false.

       /// <summary>
        /// Write a datatable to the excel file
        /// </summary>
        /// <param name="tmpDataTable"></param>
        /// <param name="strFileName"></param>
        private static void DataTabletoExcel(DataTable tmpDataTable, string strFileName)
        {
            if (tmpDataTable == null)
                return;
            int rowNum = tmpDataTable.Rows.Count;
            int columnNum = tmpDataTable.Columns.Count;
            int rowIndex = 1;
            int columnIndex = 0;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.DefaultFilePath = "";
            xlApp.DisplayAlerts = true;
            xlApp.SheetsInNewWorkbook = 1;
            Microsoft.Office.Interop.Excel.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(strFileName);
        }