excel 与 DataTable 转换

来源：互联网发布：mac chrome 编辑：程序博客网时间：2024/06/08 03:48

在工作中我们经常要实现 Excel 文件和 DataTable 的转换：

首先我们来讨论读取Excel :

我以前通过的方式为：

string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 8.0";

OleDbConnection cn = new OleDbConnection(sConnectionString);

OleDbDataAdapter adp = new OleDbDataAdapter(

"Select * from [Sheet1$]",cn);

DataTable ds = new DataTable();

adp.Fill(ds,"myTable");

这种方式最大的问题就是读取Excel钱必须确定读取的Excel 文档下的 sheet 名称（红色标记的地方），因为用户的 sheet 名是经常改动的，所以很容易出错，给用户也带来了很多不必要的麻烦，而且在读取单个文档下的多个 sheet 也很不方便。

在微软的MSDN 下我们找到了解决方法：

static DataTable GetSchemaTable(string connectionString)
{
    using (OleDbConnection connection = new
               OleDbConnection(connectionString))
    {
        connection.Open();
        DataTable schemaTable = connection.GetOleDbSchemaTable(
            OleDbSchemaGuid.Tables,
            new object[] { null, null, null, "TABLE" });
        return schemaTable;
    }
}

通过这种方式我们就能动态的获取Excel文档下的所有sheet名

最后整理为：

/// <summary>
        /// 将Excel文档转换成Datatable
        /// </summary>
        /// <param name="path">Excel文档的路径</param>
        /// <returns ></returns>
        public System.Data.DataTable[] GetExcelData(string path)
        {
            if (Path.GetExtension(path) != ".xls")
            {
                path += ".xls";
            }
            if (!File.Exists(path))
            {
                throw new Exception("文件不存在!");
            }
            System.Data.DataTable[] dts;
            System.Data.DataTable tempDt = new System.Data.DataTable();
            string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + ";Extended Properties=Excel 8.0;";
            OleDbConnection craboDbConnection = new OleDbConnection(strConn);
            craboDbConnection.Open();
            System.Data.DataTable dtSheetName = craboDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

string[] strTableNames = new string[dtSheetName.Rows.Count];
dts = new System.Data.DataTable[dtSheetName.Rows.Count];

            for (int k = 0; k < dtSheetName.Rows.Count; k++)
            {
                strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
            }

            try
            {
                for (int i = 0; i < dtSheetName.Rows.Count; i++)
                {
                    OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" + strTableNames[i] + "]", craboDbConnection);
                    OleDbDataAdapter adapter = new OleDbDataAdapter(myOleDbCommand);
                    dts[i] = new System.Data.DataTable(strTableNames[i]);
                    adapter.Fill(dts[i]);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("读取Excel失败！" + ex.Message);
            }
            finally
            {
                craboDbConnection.Close();
                craboDbConnection = null;
            }
            return dts;

}

这样我们可以不用知道具体sheet名称的情况下获取多个sheet 的内容

现在我们来讨论将DataTbale 转换成Excel 文件

以前我的方式是这样的：

     /// <summary>
        /// 将DataTable里面的内容保存成Excel文件
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="savepath">保存路径</param>
        public void DataTableConverExcel(System.Data.DataTable dt, string savepath)
        {
            int count = dt.Rows.Count;//获取数据表中DataRow行总数
            int column = dt.Columns.Count;//获取数据表中列总数
            Excel.ApplicationClass excelapp = new ApplicationClass();
            Excel.Workbook wb = excelapp.Application.Workbooks.Add(true);
            int index = 1;
            string values;
            foreach (DataColumn dc in dt.Columns)//添加列的信息
            {
                excelapp.Cells[1, index] = dc.ColumnName;
                index++;
            }
            for (int x = 1; x <= count; x++)
            {

                for (int y = 1; y <= column; y++)
                {
                    values = dt.Rows[x - 1].ItemArray[y - 1].ToString();
                    if (values != "")
                    {
                        if (isNum(values))
                        {
                            values = "'" + values;
                        }
                    }

                    excelapp.Cells[x + 1, y] = values;
                }
            }
            string tname = savepath;
            wb.SaveAs(tname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            try
            {
                wb.Saved = true;
                excelapp.UserControl = false;

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                excelapp.Quit();
            }

        }
        /// <summary>
        ///应为在导成Excel文件的时候如果前面为0所有字符为数字的文本类型将会识别成数字类型而0 将不会显示出来
        /// </summary>
        /// <param name="values">sitrngValue</param>
        /// <returns>isNum</returns>
        bool isNum(string values)
        {
            bool isNum = true;
            if (values[0] == '0')//防止将数字类型成为文本类型
            {
                foreach (char c in values.ToCharArray())
                {
                    if (!char.IsNumber(c))
                    {
                        isNum = false;
                    }
                }
            }
            else
            {
                isNum = false;
            }
            // Debug.WriteLine(values+" : "+isNum.ToString ());
            return isNum;
        }

这种方式在数据量比较小的情况下还是可以的，但如果数据量大的话，嘿嘿....

后来在网上找到了通过数组处理的方式：

        /// <summary>
        /// 将DataTable里面的内容保存成Excel文件
        /// </summary>
        /// <param name="dt">System.Data.DataTable</param>
        /// <param name="strFileName">fileName</param>
        public void DataTableToFile(System.Data.DataTable dt, string strFileName)
        {
            Excel.Application app = null;
            Excel.Workbook book = null;
            Excel.Worksheet sheet = null;
            Excel.Range rng = null;
            bool bExp = false;
            const int MAX_EXCEL_ROW = 65535;
            try
            {
                app = new Excel.Application();
                app.DisplayAlerts = false;
                app.Visible = false;

int nSheetIdx = 1;

                book = app.Workbooks.Add(Missing.Value);
                ///列数
                int nColumnCnt = 0;
                ///行数
                int nRowCnt = 0;
                ///当前列索引
                int nColumnIdx = 0;
                ///当前行索引
                int nRowIdx = 0;
                ///Sheet表的个数
                int nSheetCnt = 0; //Sheet   Number   of   Same   Table
                int nSheetRowCnt = 0; //Sheet   Row   Count
                int nSheetRowIdx = 0; //Sheet   Row   Index
                string strValue = " ";
                nColumnCnt = dt.Columns.Count;
                if (nColumnCnt > 255) nColumnCnt = 255;
                nRowCnt = dt.Rows.Count;
                if (nRowCnt % MAX_EXCEL_ROW == 0)
                    nSheetCnt = nRowCnt / MAX_EXCEL_ROW;
                else
                    nSheetCnt = nRowCnt / MAX_EXCEL_ROW + 1;
                string[,] strColumns = new string[1, nColumnCnt];
                //把列标题存放在数组中。
                for (nColumnIdx = 0; nColumnIdx < nColumnCnt; nColumnIdx++)
                    strColumns[0, nColumnIdx] = dt.Columns[nColumnIdx].ColumnName;
                for (int nTableSheetIdx = 0; nTableSheetIdx < nSheetCnt; nTableSheetIdx++)
                {
                    if (nSheetIdx > book.Worksheets.Count)
                        book.Worksheets.Add(Missing.Value, book.Worksheets.get_Item(nSheetIdx - 1), 1, Missing.Value);

                    sheet = (Excel.Worksheet)book.Worksheets.get_Item(nSheetIdx);
                    if (nTableSheetIdx == 0)
                        if (dt.TableName == "")
                        {
                            sheet.Name = "Sheet" + (nTableSheetIdx + 1).ToString();
                        }
                        else
                        {
                            sheet.Name = dt.TableName;
                        }
                    else
                        if (dt.TableName == "")
                        {
                            sheet.Name = (nTableSheetIdx + 1).ToString();
                        }
                        else
                        {
                            sheet.Name = dt.TableName + "_ " + nTableSheetIdx.ToString();
                        }
                    nSheetIdx++;
                    ////   报告的标题
                    rng = sheet.get_Range("A1 ", Missing.Value);
                    rng = rng.get_Resize(1, nColumnCnt);
                    rng.Font.Bold = true;
                    rng.set_Value(Missing.Value, strColumns);
                    rng.EntireColumn.AutoFit();

                    if (nRowCnt == 0) continue;
                    rng = sheet.get_Range("A2 ", Missing.Value);
                    if (nTableSheetIdx == 0)
                    {
                        if (nRowCnt > MAX_EXCEL_ROW)
                            nSheetRowCnt = MAX_EXCEL_ROW;
                        else
                            nSheetRowCnt = nRowCnt;
                    }
                    else if (nTableSheetIdx < (nSheetCnt - 1))
                        nSheetRowCnt = MAX_EXCEL_ROW;
                    else
                        nSheetRowCnt = nRowCnt - nTableSheetIdx * MAX_EXCEL_ROW;
                    //获得range的区域
                    rng = rng.get_Resize(nSheetRowCnt, nColumnCnt);
                    //定义对象数组，用来存放从数据库中取出来的数。最终写到Excel文件中。
                    object[,] objValues = new object[nSheetRowCnt, nColumnCnt];

                    for (nRowIdx = 0 + nTableSheetIdx * MAX_EXCEL_ROW; nRowIdx < (1 + nTableSheetIdx) * MAX_EXCEL_ROW;    nRowIdx++)
                    {
                        if (nRowIdx == nRowCnt) break;
                        nSheetRowIdx = nRowIdx - nTableSheetIdx * MAX_EXCEL_ROW;
                        for (nColumnIdx = 0; nColumnIdx < nColumnCnt; nColumnIdx++)
                        {
                            if (dt.Rows[nRowIdx][nColumnIdx] != System.DBNull.Value)
                            {
                                strValue = dt.Rows[nRowIdx][nColumnIdx].ToString();
                                objValues[nSheetRowIdx, nColumnIdx] = strValue;
                            }

                        }
                    }
                    rng.set_Value(Missing.Value, objValues);
                }
            }
            catch (System.Exception ex)
            {
                bExp = true;
                throw ex;
            }
            finally
            {
                if (book != null)
                {
                    if (bExp)
                        book.Close(false, Missing.Value, Missing.Value);
                    else
                        book.SaveAs(strFileName, Excel.XlFileFormat.xlExcel9795, Missing.Value, Missing.Value, false,
                                false, Excel.XlSaveAsAccessMode.xlNoChange,
                                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                }
                if (app != null)
                {
                    app.Quit();
                    app = null;
                }

}
}

效率的话那就至少高了一个等级，俗话说喝水不忘挖井人，但是我却不记得是哪位高手写的了，呵呵不好意思啊！谁知道原著请提醒下我加上去