读取Excel

Question

现在大家都是使用什么方法读取Excel的？

Answer 1

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
 using(OleDbConnection OleConn = new OleDbConnection(strConn))
{
  OleConn.Open();
  String sql = "SELECT * FROM [Sheet1$]";
  OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
  DataSet ds= new DataSet();
  OleDaExcel.Fill(ds);
  OleConn.Close();
}
遍历EXCEL行，获取单元格数据

Answer 2

最近单位相对轻松了点，叫我做了个文档整理工具，以下是替换Excel文件信息方法，你可以参考下
/// <summary>
        /// EXCEL替换
        /// </summary>
        /// <param name="wordPath">文档路径</param>
        /// <param name="strOldText">被替换字符串</param>
        /// <param name="strNewText">替换后新字符串</param>
        /// <returns>是否替换成功</returns>
        public bool ExcelReplace(string wordPath, string strOldText, string strNewText, bool is2007, bool isTemplete, ref FileInfo fileInfo)
        {
            ////Excel文档保存时不支持[]符号，暂时进行替换，然后再重命名
            string tempWordPath = wordPath;
            tempWordPath = tempWordPath.Replace(res.ExcelWarningChar1, res.ExcelWarningCharReplaced1);
            tempWordPath = tempWordPath.Replace(res.ExcelWarningChar2, res.ExcelWarningCharReplaced2);            Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                excelApplication.DisplayAlerts = false;
                excelApplication.Visible = false;                object UpdateLinkMissValue = 3;
                ////更新链接，避免弹出询问对话框
                Workbook wb = excelApplication.Workbooks._Open(
                              wordPath,
                             UpdateLinkMissValue,
                              Missing.Value,
                              Missing.Value,
                              Missing.Value,
                              Missing.Value,
                              Missing.Value,
                              Missing.Value,
                              Missing.Value,
                              Missing.Value,
                              Missing.Value,
                              Missing.Value,
                              Missing.Value);                Worksheet xSheet = (Worksheet)wb.Sheets[1];
                int icount = wb.Sheets.Count;
                for (int i = 1; i <= icount; i++)
                {
                    xSheet = (Worksheet)wb.Sheets[i];                    Microsoft.Office.Interop.Excel.Range range = xSheet.Cells.Find(strOldText, xSheet.Cells[1, 1],
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Missing.Value,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext,
false,
Missing.Value,
Missing.Value);
                    ////查找不到，不进行替换操作
                    if (range != null)
                    {
                        xSheet.Cells.Replace(strOldText, strNewText, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value);
                    }
                }                ////特别针对Excel模板进行处理
                if (isTemplete)
                {
                    ////没有找到支持0ffice2007模板的格式
                    object excelFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlTemplate;
                    if (is2007)
                    {
                        wordPath = wordPath.Substring(0, wordPath.LastIndexOf(".")) + FileExtension.Excel03TempleteExtension;
                    }
                    wb.SaveAs(tempWordPath, excelFormat, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    wb.Close(false, Missing.Value, Missing.Value);                }
                else
                {
                    object excelFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlAddIn8;
                    if (is2007)
                    {
                        wordPath = wordPath.Substring(0, wordPath.LastIndexOf(".")) + FileExtension.Excel03Extension;                        wb.SaveAs(tempWordPath, excelFormat, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                        wb.Close(false, Missing.Value, Missing.Value);
                    }
                    else
                    {
                        wb.Close(true, Missing.Value, Missing.Value);
                    }
                }                NAR(wb);
                excelApplication.Quit();
                NAR(excelApplication);
                ////当Excel文档包含有非法Excel字符[]时，重命名原保存的临时文件名
                if (File.Exists(tempWordPath) && !tempWordPath.Equals(wordPath))
                {
                    if (File.Exists(wordPath))
                    {
                        File.Delete(wordPath);
                    }
                    File.Move(tempWordPath, wordPath);
                    ////文件格式已经改变
                    FileInfo newFileInfo = new FileInfo(wordPath);
                    fileInfo = newFileInfo;
                }
                System.GC.Collect();
                return true;
            }
            catch (Exception ex)
            {
                NAR(excelApplication.ActiveWorkbook);
                excelApplication.Quit();
                NAR(excelApplication);                ////处理异常，删除文件
                if (File.Exists(wordPath))
                {
                    File.Delete(wordPath);
                }
                System.GC.Collect();
                return false;
            }
        }