C# 解析 Excel 并且生成 Csv 文件代码分析
今天工作中遇到一个需求，就是获取 excel 里面的内容，并且把 excel 另存为 csv，因为本人以前未接触过，所以下面整理出来的代码均来自网络，具体参考链接已丢失，原作者保留所有权利！
例子：
using System; using System.Data;namespace ExportExcelToCode { class ExcelOperater { public void Operater() { // Excel 路径 string excelPath = ""; // Csv 存放路径 string csvPath = "";
// 获取 Excel Sheet 名称列表 string[] sheetNameList = ExcelUtils.GetSheetNameList(excelPath);
if (sheetNameList != null && sheetNameList.Length > 0) { foreach (string sheetName in sheetNameList) { string itemName = sheetName.TrimEnd(new char[] { '$' });
// 解析 Excel 为 DataTable 对象 DataTable dataTable = ExcelUtils.ExcelToDataTable(excelPath, itemName); if (dataTable != null && dataTable.Rows.Count > 0) { // 生成 Csv 文件 ExcelUtils.ExcelToCsv(excelPath, csvPath, itemName, "|#|", 0); } } } } } }
ExcelUtils.cs 文件
using System; using System.Data; using Microsoft.Office.Interop.Excel; using Excel = Microsoft.Office.Interop.Excel;namespace ExportExcelToCode { public partial class ExcelUtils { /// <summary> /// 获取 Sheet 名称 /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static string[] GetSheetNameList(string filePath) { try { string connectionText = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1';";
System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(connectionText);
oleDbConnection.Open();
System.Data.DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); ;
string[] sheetNameList = new string[dataTable.Rows.Count];
for (int index = 0; index < dataTable.Rows.Count; index++) { sheetNameList[index] = dataTable.Rows[index][2].ToString(); }
oleDbConnection.Close();
return sheetNameList; } catch (Exception ex) { return null; } }
/// <summary> /// Excel 转 DataTable /// </summary> /// <param name="filePath"></param> /// <param name="sheetName"></param> /// <returns></returns> public static System.Data.DataTable ExcelToDataTable(string filePath, string sheetName) { try { string connectionText = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1';"; string selectText = string.Format("select * from [{0}$]", sheetName);
DataSet dataSet = new DataSet();
System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(connectionText);
oleDbConnection.Open();
System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(selectText, connectionText); oleDbDataAdapter.Fill(dataSet, sheetName);
oleDbConnection.Close();
return dataSet.Tables[sheetName]; } catch (Exception ex) { return null; } }
/// <summary> /// Excel 转 Csv /// </summary> /// <param name="sourceExcelPathAndName"></param> /// <param name="targetCSVPathAndName"></param> /// <param name="excelSheetName"></param> /// <param name="columnDelimeter"></param> /// <param name="headerRowsToSkip"></param> /// <returns></returns> public static bool ExcelToCsv(string sourceExcelPathAndName, string targetCSVPathAndName, string excelSheetName, string columnDelimeter, int headerRowsToSkip) { Excel.Application oXL = null; Excel.Workbooks workbooks = null; Workbook mWorkBook = null; Sheets mWorkSheets = null; Worksheet mWSheet = null;
try { oXL = new Excel.Application(); oXL.Visible = false; oXL.DisplayAlerts = false; workbooks = oXL.Workbooks; mWorkBook = workbooks.Open(sourceExcelPathAndName, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); mWorkSheets = mWorkBook.Worksheets; mWSheet = (Worksheet)mWorkSheets.get_Item(excelSheetName); Excel.Range range = mWSheet.UsedRange; Excel.Range rngCurrentRow; for (int i = 0; i < headerRowsToSkip; i++) { rngCurrentRow = range.get_Range("A1", Type.Missing).EntireRow; rngCurrentRow.Delete(XlDeleteShiftDirection.xlShiftUp); } range.Replace("\n", " ", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); range.Replace(",", columnDelimeter, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
mWorkBook.SaveAs(targetCSVPathAndName, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false); return true; } catch (Exception ex) { return false; } finally { if (mWSheet != null) mWSheet = null; if (mWorkBook != null) mWorkBook.Close(Type.Missing, Type.Missing, Type.Missing); if (mWorkBook != null) mWorkBook = null; if (oXL != null) oXL.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL); if (oXL != null) oXL = null; GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } } } }
需要特别指出的是：需要在项目中添加 Microsoft.Office.Interop.Excel.dll 文件，具体操作：选中引用->右键添加引用->浏览找到 Microsoft.Office.Interop.Excel，添加引用。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：notice#yiidian.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。
