c#讀取excel數(shù)據(jù)的兩種方法實現(xiàn)
更新時間：2020年12月09日 10:13:12 作者：臉譜匠
這篇文章主要介紹了c#讀取excel數(shù)據(jù)的兩種方法實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
方法一：OleDb： 用這種方法讀取Excel速度還是非常的快的，但這種方式讀取數(shù)據(jù)的時候不太靈活，不過可以在 DataTable 中對數(shù)據(jù)進行一些刪減修改。
優(yōu)點：讀取方式簡單、讀取速度快
缺點：除了讀取過程不太靈活之外，這種讀取方式還有個弊端就是，當Excel數(shù)據(jù)量很大時。會非常占用內(nèi)存，當內(nèi)存不夠時會拋出內(nèi)存溢出的異常。
不過一般情況下還是非常不錯的。
（代碼比原文相較有所修改）
DataTable GetDataFromExcelByConn(bool hasTitle = false)
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
openFile.Multiselect = false;
if (openFile.ShowDialog() == DialogResult.Cancel) return null;
var filePath = openFile.FileName;
string fileType = System.IO.Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileType)) return null;
using (DataSet ds = new DataSet())
{
string strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" +
"Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
"data source={3};",
(fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), filePath);
string strCom = " SELECT * FROM [Sheet1$]";
using (OleDbConnection myConn = new OleDbConnection(strCon))
using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))
{
myConn.Open();
myCommand.Fill(ds);
}
if (ds == null || ds.Tables.Count <= 0) return null;
return ds.Tables[0];
}
}
方法二：Com組件的方式讀取Excel
這種方式需要先引用 Microsoft.Office.Interop.Excel 。首選說下這種方式的優(yōu)缺點
優(yōu)點：可以非常靈活的讀取Excel中的數(shù)據(jù)
缺點：如果是Web站點部署在IIS上時，還需要服務(wù)器機子已安裝了Excel，有時候還需要為配置IIS權(quán)限。最重要的一點因為是基于單元格方式讀取的，所以數(shù)據(jù)很慢（曾做過試驗，直接讀取千行、200多列的文件，直接讀取耗時15分鐘。即使采用多線程分段讀取來提高CPU的利用率也需要8分鐘。PS：CPU I3）
需要讀取大文件的的童鞋們慎重。。。
（代碼比原文相較有所修改）
DataTable GetDataFromExcelByCom(bool hasTitle = false)
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
openFile.Multiselect = false;
if (openFile.ShowDialog() == DialogResult.Cancel) return null;
var excelFilePath = openFile.FileName;
Excel.Application app = new Excel.Application();
Excel.Sheets sheets;
object oMissiong = System.Reflection.Missing.Value;
Excel.Workbook workbook = null;
DataTable dt = new DataTable();
try
{
if (app == null) return null;
workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
sheets = workbook.Worksheets;
//將數(shù)據(jù)讀入到DataTable中
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//讀取第一張表
if (worksheet == null) return null;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
//生成列頭
for (int i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = ((Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
if (!string.IsNullOrWhiteSpace(txt)) name = txt;
}
while (dt.Columns.Contains(name)) name = name + "_1";//重復(fù)行名稱會報錯。
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行數(shù)據(jù)
Excel.Range range;
int rowIdx = hasTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Excel.Range)worksheet.Cells[iRow, iCol];
dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
}
dt.Rows.Add(dr);
}
return dt;
}
catch { return null; }
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
}
原文的方法二還提供了多線程處理數(shù)據(jù)的代碼，一并復(fù)制到此(此處出現(xiàn)了一個SheetOptions的類型，無法考證其來源，如果知曉，請留言，謝謝。)：
/// <summary>
/// 使用COM，多線程讀取Excel（1 主線程、4 副線程）
/// </summary>
/// <param name="excelFilePath">路徑</param>
/// <returns>DataTabel</returns>
public System.Data.DataTable ThreadReadExcel(string excelFilePath)
{
Excel.Application app = new Excel.Application();
Excel.Sheets sheets = null;
Excel.Workbook workbook = null;
object oMissiong = System.Reflection.Missing.Value;
System.Data.DataTable dt = new System.Data.DataTable();
try
{
if (app == null)
{
return null;
}
workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
//將數(shù)據(jù)讀入到DataTable中——Start
sheets = workbook.Worksheets;
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//讀取第一張表
if (worksheet == null)
return null;
string cellContent;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
Excel.Range range;
//負責(zé)列頭Start
DataColumn dc;
int ColumnID = 1;
range = (Excel.Range)worksheet.Cells[1, 1];
//while (range.Text.ToString().Trim() != "")
while (iColCount >= ColumnID)
{
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
string strNewColumnName = range.Text.ToString().Trim();
if (strNewColumnName.Length == 0) strNewColumnName = "_1";
//判斷列名是否重復(fù)
for (int i = 1; i < ColumnID; i++)
{
if (dt.Columns[i - 1].ColumnName == strNewColumnName)
strNewColumnName = strNewColumnName + "_1";
}
dc.ColumnName = strNewColumnName;
dt.Columns.Add(dc);
range = (Excel.Range)worksheet.Cells[1, ++ColumnID];
}
//End
//數(shù)據(jù)大于500條，使用多進程進行讀取數(shù)據(jù)
if (iRowCount - 1 > 500)
{
//開始多線程讀取數(shù)據(jù)
//新建線程
int b2 = (iRowCount - 1) / 10;
DataTable dt1 = new DataTable("dt1");
dt1 = dt.Clone();
SheetOptions sheet1thread = new SheetOptions(worksheet, iColCount, 2, b2 + 1, dt1);
Thread othread1 = new Thread(new ThreadStart(sheet1thread.SheetToDataTable));
othread1.Start();
//阻塞 1 毫秒，保證第一個讀取 dt1
Thread.Sleep(1);
DataTable dt2 = new DataTable("dt2");
dt2 = dt.Clone();
SheetOptions sheet2thread = new SheetOptions(worksheet, iColCount, b2 + 2, b2 * 2 + 1, dt2);
Thread othread2 = new Thread(new ThreadStart(sheet2thread.SheetToDataTable));
othread2.Start();
DataTable dt3 = new DataTable("dt3");
dt3 = dt.Clone();
SheetOptions sheet3thread = new SheetOptions(worksheet, iColCount, b2 * 2 + 2, b2 * 3 + 1, dt3);
Thread othread3 = new Thread(new ThreadStart(sheet3thread.SheetToDataTable));
othread3.Start();
DataTable dt4 = new DataTable("dt4");
dt4 = dt.Clone();
SheetOptions sheet4thread = new SheetOptions(worksheet, iColCount, b2 * 3 + 2, b2 * 4 + 1, dt4);
Thread othread4 = new Thread(new ThreadStart(sheet4thread.SheetToDataTable));
othread4.Start();
//主線程讀取剩余數(shù)據(jù)
for (int iRow = b2 * 4 + 2; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Excel.Range)worksheet.Cells[iRow, iCol];
cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
dr[iCol - 1] = cellContent;
}
dt.Rows.Add(dr);
}
othread1.Join();
othread2.Join();
othread3.Join();
othread4.Join();
//將多個線程讀取出來的數(shù)據(jù)追加至 dt1 后面
foreach (DataRow dr in dt.Rows)
dt1.Rows.Add(dr.ItemArray);
dt.Clear();
dt.Dispose();
foreach (DataRow dr in dt2.Rows)
dt1.Rows.Add(dr.ItemArray);
dt2.Clear();
dt2.Dispose();
foreach (DataRow dr in dt3.Rows)
dt1.Rows.Add(dr.ItemArray);
dt3.Clear();
dt3.Dispose();
foreach (DataRow dr in dt4.Rows)
dt1.Rows.Add(dr.ItemArray);
dt4.Clear();
dt4.Dispose();
return dt1;
}
else
{
for (int iRow = 2; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Excel.Range)worksheet.Cells[iRow, iCol];
cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
dr[iCol - 1] = cellContent;
}
dt.Rows.Add(dr);
}
}
//將數(shù)據(jù)讀入到DataTable中——End
return dt;
}
catch
{
return null;
}
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
補充SheetOptions代碼：
class SheetOptions
{
Microsoft.Office.Interop.Excel.Worksheet worksheet;
int iColCount;
int star;
int end;
System.Data.DataTable dt;
public SheetOptions(Microsoft.Office.Interop.Excel.Worksheet worksheet, int iColCount, int star, int end, System.Data.DataTable dt)
{
this.worksheet = worksheet;
this.iColCount = iColCount;
this.star = star;
this.end = end;
this.dt = dt;
}
public void SheetToDataTable()
{
string cellContent;
Microsoft.Office.Interop.Excel.Range range;
for (int iRow = star; iRow <= end; iRow++)
{
System.Data.DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
dr[iCol - 1] = cellContent;
}
dt.Rows.Add(dr);
}
}
}
原文還提供了第三種方法，感興趣的可以關(guān)心一下：
方法三：NPOI方式讀取Excel，NPOI是一組開源的組件，類似Java的 POI。包括：NPOI、NPOI.HPSF、NPOI.HSSF、NPOI.HSSF.UserModel、NPOI.POIFS、NPOI.Util，下載的時候別只下一個噢
優(yōu)點：讀取Excel速度較快，讀取方式操作靈活性
缺點：只支持03的Excel，xlsx的無法讀取。由于這點，使用這種方式的人不多啊，沒理由要求客戶使用03版Excel吧，再說03版Excel對于行數(shù)還有限制，只支持65536行。
（聽他們的開發(fā)人員說會在2012年底推出新版，支持xlsx的讀取。但一直很忙沒時間去關(guān)注這個事情，有興趣的同學(xué)可以瞧瞧去）
using System;
using System.Data;
using System.IO;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;
using System.Configuration;
public class NPOIHelper
{
private static int ExcelMaxRow = Convert.ToInt32(ConfigurationManager.AppSettings["ExcelMaxRow"]);
/// <summary>
/// 由DataSet導(dǎo)出Excel
/// </summary>
/// <param name="sourceTable">要導(dǎo)出數(shù)據(jù)的DataTable</param>
/// <param name="sheetName">工作表名稱</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataSetToExcel(DataSet sourceDs)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
for (int i = 0; i < sourceDs.Tables.Count; i++)
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sourceDs.Tables[i].TableName);
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceDs.Tables[i].Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return ms;
}
/// <summary>
/// 由DataSet導(dǎo)出Excel
/// </summary>
/// <param name="sourceTable">要導(dǎo)出數(shù)據(jù)的DataTable</param>
/// <param name="fileName">指定Excel工作表名稱</param>
/// <returns>Excel工作表</returns>
public static void ExportDataSetToExcel(DataSet sourceDs, string fileName)
{
//檢查是否有Table數(shù)量超過65325
for (int t = 0; t < sourceDs.Tables.Count; t++)
{
if (sourceDs.Tables[t].Rows.Count > ExcelMaxRow)
{
DataSet ds = GetdtGroup(sourceDs.Tables[t].Copy());
sourceDs.Tables.RemoveAt(t);
//將得到的ds插入 sourceDs中
for (int g = 0; g < ds.Tables.Count; g++)
{
DataTable dt = ds.Tables[g].Copy();
sourceDs.Tables.Add(dt);
}
t--;
}
}
MemoryStream ms = ExportDataSetToExcel(sourceDs) as MemoryStream;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.ApplicationInstance.CompleteRequest();
//HttpContext.Current.Response.End();
ms.Close();
ms = null;
}
/// <summary>
/// 由DataTable導(dǎo)出Excel
/// </summary>
/// <param name="sourceTable">要導(dǎo)出數(shù)據(jù)的DataTable</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataTableToExcel(DataTable sourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sourceTable.TableName);
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return ms;
}
/// <summary>
/// 由DataTable導(dǎo)出Excel
/// </summary>
/// <param name="sourceTable">要導(dǎo)出數(shù)據(jù)的DataTable</param>
/// <param name="fileName">指定Excel工作表名稱</param>
/// <returns>Excel工作表</returns>
public static void ExportDataTableToExcel(DataTable sourceTable, string fileName)
{
//如數(shù)據(jù)超過65325則分成多個Table導(dǎo)出
if (sourceTable.Rows.Count > ExcelMaxRow)
{
DataSet ds = GetdtGroup(sourceTable);
//導(dǎo)出DataSet
ExportDataSetToExcel(ds, fileName);
}
else
{
MemoryStream ms = ExportDataTableToExcel(sourceTable) as MemoryStream;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.ApplicationInstance.CompleteRequest();
//HttpContext.Current.Response.End();
ms.Close();
ms = null;
}
}
/// <summary>
/// 傳入行數(shù)超過65325的Table，返回DataSet
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static DataSet GetdtGroup(DataTable dt)
{
string tablename = dt.TableName;
DataSet ds = new DataSet();
ds.Tables.Add(dt);
double n = dt.Rows.Count / Convert.ToDouble(ExcelMaxRow);
//創(chuàng)建表
for (int i = 1; i < n; i++)
{
DataTable dtAdd = dt.Clone();
dtAdd.TableName = tablename + "_" + i.ToString();
ds.Tables.Add(dtAdd);
}
//分解數(shù)據(jù)
for (int i = 1; i < ds.Tables.Count; i++)
{
//新表行數(shù)達到最大 或 基表數(shù)量不足
while (ds.Tables[i].Rows.Count != ExcelMaxRow && ds.Tables[0].Rows.Count != ExcelMaxRow)
{
ds.Tables[i].Rows.Add(ds.Tables[0].Rows[ExcelMaxRow].ItemArray);
ds.Tables[0].Rows.RemoveAt(ExcelMaxRow);
}
}
return ds;
}
/// <summary>
/// 由DataTable導(dǎo)出Excel
/// </summary>
/// <param name="sourceTable">要導(dǎo)出數(shù)據(jù)的DataTable</param>
/// <param name="fileName">指定Excel工作表名稱</param>
/// <returns>Excel工作表</returns>
public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName)
{
int rowIndex = 2;//從第二行開始，因為前兩行是模板里面的內(nèi)容
int colIndex = 0;
FileStream file = new FileStream(modelpath + modelName + ".xls", FileMode.Open, FileAccess.Read);//讀入excel模板
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
sheet1.GetRow(0).GetCell(0).SetCellValue("excelTitle"); //設(shè)置表頭
foreach (DataRow row in sourceTable.Rows)
{ //雙循環(huán)寫入sourceTable中的數(shù)據(jù)
rowIndex++;
colIndex = 0;
HSSFRow xlsrow = (HSSFRow)sheet1.CreateRow(rowIndex);
foreach (DataColumn col in sourceTable.Columns)
{
xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString());
colIndex++;
}
}
sheet1.ForceFormulaRecalculation = true;
FileStream fileS = new FileStream(modelpath + fileName + ".xls", FileMode.Create);//保存
hssfworkbook.Write(fileS);
fileS.Close();
file.Close();
}
}
到此這篇關(guān)于c#讀取excel數(shù)據(jù)的兩種方法實現(xiàn)的文章就介紹到這了,更多相關(guān)c#讀取excel內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本文主要介紹了字符串去掉兩端空格，并且將字符串中多個空格替換成一個空格的方法,需要的朋友可以參考下2014-02-02
這篇文章主要介紹了C#中的try catch finally用法,以實例形式分析了try catch finally針對錯誤處理時的不同用法,具有一定的參考借鑒價值,需要的朋友可以參考下2014-12-12
這篇文章主要為大家介紹了Unity的OnOpenAsset實用案例深入解析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-05-05
C#?WinForm實現(xiàn)檢查目標IP端口是否可連接
這篇文章主要為大家詳細介紹了如何基于C#?WinForm實現(xiàn)檢查目標IP端口是否可連接的小工具,感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2025-01-01
C# ODP.NET 調(diào)用Oracle函數(shù)返回值時報錯的一個解決方案
這篇文章主要介紹了C# ODP.NET 調(diào)用Oracle函數(shù)返回值時報錯的一個解決方案，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
這篇文章主要介紹了C#之WinForm跨線程訪問控件,實例講述了跨線程訪問控件的簡單實現(xiàn)方法與用法,需要的朋友可以參考下2014-10-10
Unity基于ShaderLab實現(xiàn)光照系統(tǒng)(著色器代碼實現(xiàn)小結(jié))
這篇文章主要介紹了Unity基于ShaderLab實現(xiàn)光照系統(tǒng),主要總結(jié)unity中shaderlab的著色器代碼實現(xiàn)，本文給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-01-01
這篇文章主要介紹了C#中的定時器Timer類及其垃圾回收機制,講解了Timer相關(guān)的單線程異步工作,需要的朋友可以參考下2016-04-04
這篇文章主要介紹了C#中獲取、生成隨機數(shù)的三種方法,本文講解了Random 類生成法、Guid 類生成法以及RNGCryptoServiceProvider 類生成法,需要的朋友可以參考下2015-07-07
某一天我正在寫一些反射代碼,目的是遍歷所有的程序集來查找一個特定的接口,然后在Startup中調(diào)用其上的一個方法,這篇文章主要介紹了在C#中如何獲取程序集,需要的朋友可以參考下2024-03-03