(1) OleDB 방식
장점: 엑셀을 직접 데이터소스로 취급하고, 내용을 SQL을 통해 직접 읽어 읽기 속도가 더 빠르다.
단점: 데이터를 읽는 방법이 유연하지 않고 특정 셀을 직접 읽을 수 없습니다. 전체 Sheet 페이지를 읽은 후에만(결과는 Datatable임) 이를 기반으로 Datatable에서 지정된 값을 얻을 수 있습니다. 행과 열의 수.
엑셀 데이터의 양이 많을 때. 많은 메모리를 차지하며, 메모리가 충분하지 않으면 메모리 오버플로 예외가 발생합니다.
읽는 코드는 다음과 같습니다.
public DataTable GetExcelTableByOleDB(string strExcelPath, string tableName)
{
try
{
DataTable dtExcel = new DataTable();
//数据表
DataSet ds = new DataSet();
//获取文件扩展名
string strExtension = System.IO.Path.GetExtension(strExcelPath);
string strFileName = System.IO.Path.GetFileName(strExcelPath);
//Excel的连接
OleDbConnection objConn = null;
switch (strExtension)
{
case ".xls":
objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"");
break;
case ".xlsx":
objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"");
break;
default:
objConn = null;
break;
}
if (objConn == null)
{
return null;
}
objConn.Open();
//获取Excel中所有Sheet表的信息
//System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet表名
//string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strSql = "select * from [" + tableName + "]";
//获取Excel指定Sheet表中的信息
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(ds, tableName);//填充数据
objConn.Close();
//dtExcel即为excel文件中指定表中存储的信息
dtExcel = ds.Tables[tableName];
return dtExcel;
}
catch
{
return null;
}
}
다음은 연결 문자열을 설명합니다.
HDR=Yes, 즉 첫 번째 행은 제목이고 데이터로 사용되지 않습니다. (그러나 실제 사용시 첫 번째 행에 복잡한 값이 있으면 읽은 Datatable 열 제목이 자동으로 F1, F2 등으로 설정되어 일관성이 없습니다. 실제 애플리케이션에서는 HDR=No를 통해 모든 콘텐츠를 Datatable로 읽은 다음 첫 번째 행을 수동으로 제목으로 설정했습니다. IMEX(IMport EXport 모드) 설정
IMEX에는 세 가지 모드가 있습니다.
0은 내보내기 모드
1은 가져오기 모드
2는 연결 모드(전체 업데이트 기능)
여기서 특히 설명하고 싶은 것은 IMEX 매개변수입니다. 모드에 따라 읽기 및 쓰기 동작이 다르기 때문입니다. :
IMEX =0이 "내보내기 모드"인 경우 이 모드에서 열린 Excel 파일은 "쓰기" 목적으로만 사용할 수 있습니다.
IMEX=1이면 "가져오기 모드"입니다. 이 모드에서 열린 Excel 파일은 "읽기" 목적으로만 사용할 수 있습니다.
IMEX=2이면 "링크 모드"입니다. 이 모드에서 열린 Excel 파일은 "읽기" 및 "쓰기" 목적을 모두 지원할 수 있습니다.
----------------------
또 Excel2007도 읽어보세요 파일 버전을 8.0에서 12.0으로 변경해야 합니다. 동시에 드라이버는 더 이상 Jet를 사용할 수 없지만 ACE를 사용해야 합니다. "설치 가능한 ISAM을 찾을 수 없음" 오류의 원인입니다.
----------------------
온라인에서도 찾을 수 있습니다. 방법을 사용하면 검색된 시트 테이블 수가 실제 Excel 테이블의 시트 테이블 수보다 클 수 있습니다. 여기에는 두 가지 이유가 있습니다.
1 추출된 이름에는 XL Naming Manager 이름( XL2007의 공식을 참조하세요. 바로 가기 키 Crtl+F3). 추출된 이름에는 XL에서 필터 범위를 기록하는 데 사용되는 FilterDatabase 접미사가 포함되어 있습니다.
첫 번째 사항은 비교적 간단합니다. 기존 이름 지정 관리자에서 콘텐츠를 삭제하면 됩니다. 두 번째 사항은 필터를 삭제한 후에도 계속 유지됩니다. 새 시트를 추가한 다음 원본 시트를 복사하세요. 그러나 실제 상황에서는 모든 Excel에 대해 위의 검사를 수행할 수 없습니다. 필터링 방식은 아래와 같습니다. (이 문제는 저희가 확인했으니 직접 확인해 주세요)
//objConn为读取Excel的链接，下面通过过滤来获取有效的Sheet页名称集合
System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
List<string> lstSheetNames = new List<string>();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
string strSheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (strSheetName.Contains("$") && !strSheetName.Replace("'", "").EndsWith("$"))
{
//过滤无效SheetName完毕....
continue;
}
if (lstSheetNames != null && !lstSheetNames.Contains(strSheetName))
lstSheetNames.Add(strSheetName);
}잘못된 SheetName의 마지막 문자는 일반적으로 $가 아니기 때문입니다. SheetName에 특수 기호가 있으면 읽은 SheetName에 작은따옴표가 자동으로 추가됩니다. 예를 들어 Excel에서 SheetName을 MySheet(1)로 편집하는 경우 읽기 SheetName은 'MySheet(1)$'가 되므로 마지막 문자가 $인지 판단하기 전에 작은따옴표를 필터링하는 것이 가장 좋습니다.
----------------------
(2) Com 컴포넌트 (Microsoft.Office.Interop.Excel 참조를 추가하여 구현)
장점: Excel의 데이터를 매우 유연하게 읽을 수 있으며, 처리를 위한 다양한 기능을 사용자가 유연하게 호출할 수 있습니다.
단점: 셀 기반 처리는 읽기 속도가 느립니다. 이런 식으로 많은 양의 파일을 읽지 않는 것이 가장 좋습니다.
해당 DLL 참조를 추가해야 합니다. 이 참조를 사용하려면 먼저 존재해야 합니다. 웹 사이트가 IIS에 배포된 경우 Excel을 서버 컴퓨터에 설치해야 하며 경우에 따라 IIS 권한이 필요합니다. 구성됩니다.
읽는 코드는 다음과 같습니다.
private Stopwatch wath = new Stopwatch();
/// <summary>
/// 使用COM读取Excel
/// </summary>
/// <param name="excelFilePath">路径</param>
/// <returns>DataTabel</returns>
public System.Data.DataTable GetExcelData(string excelFilePath)
{
Excel.Application app = new Excel.Application();
Excel.Sheets sheets;
Excel.Workbook workbook = null;
object oMissiong = System.Reflection.Missing.Value;
System.Data.DataTable dt = new System.Data.DataTable();
wath.Start();
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);
//将数据读入到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;
//负责列头Start
DataColumn dc;
int ColumnID = 1;
range = (Excel.Range)worksheet.Cells[1, 1];
while (range.Text.ToString().Trim() != "")
{
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = range.Text.ToString().Trim();
dt.Columns.Add(dc);
range = (Excel.Range)worksheet.Cells[1, ++ColumnID];
}
//End
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);
}
wath.Stop();
TimeSpan ts = wath.Elapsed;
//将数据读入到DataTable中——End
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;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <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();
wath.Start();
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);
//将数据读入到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;
//负责列头Start
DataColumn dc;
int ColumnID = 1;
range = (Excel.Range)worksheet.Cells[1, 1];
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";
//判断列名是否重复
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
//数据大于500条，使用多进程进行读取数据
if (iRowCount - 1 > 500)
{
//开始多线程读取数据
//新建线程
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();
//主线程读取剩余数据
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();
//将多个线程读取出来的数据追加至 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);
}
}
wath.Stop();
TimeSpan ts = wath.Elapsed;
//将数据读入到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();
}
}
(3) NPOI 모드에서 Excel 읽기(이 방법은 테스트되지 않았습니다.)
NPOI는 POI 프로젝트의 .NET 버전입니다. POI는 Excel, WORD 및 기타 Microsoft OLE2 구성 요소 문서를 읽고 쓰기 위한 오픈 소스 Java 프로젝트입니다. NPOI를 이용하면 Office가 설치되지 않은 기기나 해당 환경에서 WORD/EXCEL 문서를 읽고 쓸 수 있습니다.
장점: 엑셀 읽기 속도 향상, 유연한 읽기 방식
단점: 해당 플러그인을 다운받아 시스템 레퍼런스에 추가해야 한다.
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
data.Columns.Add(column);
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}C#에서 Excel을 읽는 세 가지 방법과 비교 분석에 대한 더 많은 기사를 보려면 PHP 중국어 웹사이트를 주목하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
