C#2.0解析Excel电子表格的最快方法 [英] C# 2.0 Fastest way to parse Excel spreadsheet
本文介绍了C#2.0解析Excel电子表格的最快方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
可能重复:结果
的从C#阅读Excel文件
块引用>
什么是最快的方式从Csharp的读取Excel中的大型数据集。示例代码将是巨大的。 。
解决方案公共类ExcelHeaderValues
{
公共静态字符串CUSIP =CUSIP;
公共静态字符串ORIG =弊;
公共静态字符串价=价格;
公共静态INT列数= 3;
}
公共类ExcelParser
{
私人ArrayList的collOutput = NULL;
串sSheetName =的String.Empty;
的String [] strValidColumn;
INT validRowCnt = 0;
串sColumnPositions =的String.Empty;
的OleDbCommand ExcelCommand;
OleDbDataAdapter的ExcelAdapter;
OleDbConnection的ExcelConnection = NULL;
的DataSet dsSheet = NULL;
路径字符串=的String.Empty;
字符串标识=的String.Empty;
公共ExcelParser()
{
collOutput =新的ArrayList();
}
公共无效提取物()
{
布尔headermatch = FALSE;
串strCusip =的String.Empty,strOrig =的String.Empty,strPrice =的String.Empty,strData是=的String.Empty;
串strCusipPos =的String.Empty,strPricePos =的String.Empty,strOrigPos =的String.Empty;
串strColumnHeader =的String.Empty;
INT reqColcount = 0;
的String [] strTemp;
布尔bTemp = FALSE;
布尔validRow = FALSE;
的DataTable schemaTable =的GetSchemaTable();
validRowCnt = 0;
的foreach(在schemaTable.Rows的DataRow DR)
{
如果(dsSheet!= NULL)
{
dsSheet.Reset();
dsSheet = NULL;
}
strCusipPos =的String.Empty;
strOrigPos =的String.Empty;
strPricePos =的String.Empty;
如果(isValidSheet(DR))
{
sColumnPositions =的String.Empty;
validRowCnt = 0;
的foreach(DataRow的数据行的dsSheet.Tables [0] .Rows)
{
sColumnPositions =的String.Empty;
如果(headermatch ==假)
{
sColumnPositions =的String.Empty;
的foreach(在dsSheet.Tables的DataColumn列[0] .Columns)
{
strColumnHeader =数据行[column.ColumnName]的ToString()ToUpper的()修剪()。
strColumnHeader = strColumnHeader.ToUpper();
如果(strColumnHeader == ExcelHeaderValues.ORIG.ToUpper()|| strColumnHeader == ExcelHeaderValues.CUSIP.ToUpper()|| strColumnHeader == ExcelHeaderValues.PRICE.ToUpper())
{
bTemp =真;
validRow = TRUE;
reqColcount = ExcelHeaderValues.COLUMNCOUNT;
}
如果(bTemp)
{
bTemp = FALSE;
sColumnPositions + = column.ColumnName +^+ strColumnHeader +;;
}
}
strValidColumn = sColumnPositions.Trim()分割。(';');
如果(validRow ==真&放大器;&安培; reqColcount == strValidColumn.Length - 1)
{
headermatch = TRUE;
中断;
}
validRowCnt ++;
}
}
如果(headermatch ==真)
{
试
{
如果(dsSheet.Tables [0] .Rows .Count之间的>若0)
{
(strValidColumn.Length大于0)
{
的for(int i = 0; I< strValidColumn.Length - 1;我++如果)
{
(strValidColumn [I] .ToUpper()。包含(CUSIP))
{
strTemp = strValidColumn [I]的ToString()。斯普利特( '^');
strCusipPos = strTemp [0]的ToString();
strTemp = NULL;
}
如果(strValidColumn [I] .ToUpper()。包含(价格))
{
strTemp = strValidColumn [I]的ToString() .Split('^');
strPricePos = strTemp [0]的ToString();
strTemp = NULL;
}
如果(strValidColumn [I] .ToUpper()。包含(弊))
{
strTemp = strValidColumn [I]的ToString() .Split('^');
strOrigPos = strTemp [0]的ToString();
strTemp = NULL;
}
}
}
为(INT IDATA = validRowCnt; IDATA< dsSheet.Tables [0] .Rows.Count; IDATA ++)
{
如果(strCusipPos.Trim()=!)
strCusip = dsSheet.Tables [0] .Rows [IDATA] [strCusipPos]的ToString()修剪();
如果(strOrigPos.Trim()=!)
strOrig = dsSheet.Tables [0] .Rows [IDATA] [strOrigPos]的ToString()修剪();
如果(strPricePos.Trim()=!)
strPrice = dsSheet.Tables [0] .Rows [IDATA] [strPricePos]的ToString()修剪()ToUpper的()。;
strData是=;
如果(strCusip.Length == 9和;&放大器;!strCusip =与&&放大器;!strPrice =与&&放大器;!strOrig =与&&放大器;!strPrice.ToUpper()。包含(卖))
strData是= strCusip +| + Convert.ToDouble(strOrig)* 1000000 +| + strPrice +|||||
如果(strData是= NULL&放大器;!&安培; strData是=!)
collOutput.Add(strData是);
strCusip =的String.Empty;
strOrig =的String.Empty;
strPrice =的String.Empty;
strData是=的String.Empty;
}
}
}
赶上(异常前)
{
罚球前;
}
}
headermatch = FALSE;
sColumnPositions =的String.Empty;
strColumnHeader =的String.Empty;
}
}
}
私人布尔isValidSheet(DataRow的DR)
{
布尔isValidSheet = FALSE;
sSheetName =博士[2]的ToString()ToUpper的();
如果((sSheetName.Contains($ _))及!&放大器;!(sSheetName.Contains($_))及&放大器;!(sSheetName.Contains( Print_Titles.ToUpper()))及及(博士[3]的ToString()==TABLE和放大器;&安培;!((sSheetName.Contains(Print_Area.ToUpper()))))及&放大器;!(sSheetName.ToUpper()==DLOFFERLOOKUP))
{
如果(sSheetName.Trim()ToUpper的()=声明$ToUpper的()及!&放大器; sSheetName.Trim()ToUpper的()=摘要$ToUpper的()及!&放大器;!sSheetName.Trim()ToUpper的()=公式$)
{
串sQry =的String.Empty;
sQry =SELECT * FROM [+ sSheetName +];
ExcelCommand =新的OleDbCommand(sQry,ExcelConnection);
dsSheet =新的DataSet();
ExcelAdapter =新OleDbDataAdapter的(ExcelCommand);
isValidSheet = FALSE;
试
{
ExcelAdapter.Fill(dsSheet,sSheetName);
isValidSheet = TRUE;
}
赶上(异常前)
{
isValidSheet = FALSE;
抛出新的异常(ex.Message.ToString());
}
终于
{
ExcelAdapter = NULL;
ExcelCommand = NULL;
}
}
}
返回isValidSheet;
}
私人数据表的GetSchemaTable()
{
DataTable的DT = NULL;
字符串的connectionString =的String.Empty;
的connectionString = GetConnectionString();
ExcelConnection =新的OleDbConnection(的connectionString);
试
{
ExcelConnection.Open();
DT = ExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,新的对象[] {NULL,NULL,NULL,TABLE});
}
赶上(异常前)
{
罚球前;
}
返回DT;
}
私人字符串GetConnectionString()
{
串connStr =的String.Empty;
试
{
如果(path.ToLower()。包含(XLSX))
{
connStr =供应商=微软。 ACE.OLEDB.12.0; +数据源='+路径+; +扩展属性='Excel的12.0; HDR =无; IMEX = 1;';
}
,否则如果(path.ToLower()包含(XLSM))
{
connStr =供应商= Microsoft.ACE.OLEDB.12.0; +数据源='+路径+; +扩展属性='Excel的12.0宏; HDR =无; IMEX = 1;';
}
,否则如果(path.ToLower()。包含(XLS))
{
connStr =供应商= Microsoft.Jet.OLEDB。 4.0;数据源='+路径+;扩展属性='Excel的8.0; HDR =无; IMEX = 1;';
}
,否则
{connStr =供应商= Microsoft.ACE.OLEDB.12.0; +数据源='+路径+; +扩展属性='HTML导入; IMEX = 1; HDR =无;';
}
}
赶上(异常前)
{
罚球前;
}
返回connStr;
}
}
Possible Duplicate:
Reading Excel files from C#What is the fastest way to read large sets of data from excel from Csharp. Example code would be great . .
解决方案public class ExcelHeaderValues { public static string CUSIP = "CUSIP"; public static string ORIG = "ORIG"; public static string PRICE = "PRICE"; public static int COLUMNCOUNT = 3; } public class ExcelParser { private ArrayList collOutput = null; string sSheetName = String.Empty; string[] strValidColumn; int validRowCnt = 0; string sColumnPositions = String.Empty; OleDbCommand ExcelCommand; OleDbDataAdapter ExcelAdapter; OleDbConnection ExcelConnection = null; DataSet dsSheet = null; string path = string.Empty; string identifier = string.Empty; public ExcelParser() { collOutput = new ArrayList(); } public void Extract() { bool headermatch = false; string strCusip = string.Empty, strOrig = string.Empty, strPrice = string.Empty, strData = string.Empty; string strCusipPos = string.Empty, strPricePos = string.Empty, strOrigPos = string.Empty; string strColumnHeader = String.Empty; int reqColcount = 0; string[] strTemp; bool bTemp = false; bool validRow = false; DataTable schemaTable = GetSchemaTable(); validRowCnt = 0; foreach (DataRow dr in schemaTable.Rows) { if (dsSheet != null) { dsSheet.Reset(); dsSheet = null; } strCusipPos = string.Empty; strOrigPos = string.Empty; strPricePos = string.Empty; if (isValidSheet(dr)) { sColumnPositions = string.Empty; validRowCnt = 0; foreach (DataRow dataRow in dsSheet.Tables[0].Rows) { sColumnPositions = string.Empty; if (headermatch == false) { sColumnPositions = string.Empty; foreach (DataColumn column in dsSheet.Tables[0].Columns) { strColumnHeader = dataRow[column.ColumnName].ToString().ToUpper().Trim(); strColumnHeader = strColumnHeader.ToUpper(); if (strColumnHeader == ExcelHeaderValues.ORIG.ToUpper() || strColumnHeader == ExcelHeaderValues.CUSIP.ToUpper() || strColumnHeader == ExcelHeaderValues.PRICE.ToUpper()) { bTemp = true; validRow = true; reqColcount = ExcelHeaderValues.COLUMNCOUNT; } if (bTemp) { bTemp = false; sColumnPositions += column.ColumnName + "^" + strColumnHeader + ";"; } } strValidColumn = sColumnPositions.Trim().Split(';'); if (validRow == true && reqColcount == strValidColumn.Length - 1) { headermatch = true; break; } validRowCnt++; } } if (headermatch == true) { try { if (dsSheet.Tables[0].Rows.Count > 0) { if (strValidColumn.Length > 0) { for (int i = 0; i < strValidColumn.Length - 1; i++) { if (strValidColumn[i].ToUpper().Contains("CUSIP")) { strTemp = strValidColumn[i].ToString().Split('^'); strCusipPos = strTemp[0].ToString(); strTemp = null; } if (strValidColumn[i].ToUpper().Contains("PRICE")) { strTemp = strValidColumn[i].ToString().Split('^'); strPricePos = strTemp[0].ToString(); strTemp = null; } if (strValidColumn[i].ToUpper().Contains("ORIG")) { strTemp = strValidColumn[i].ToString().Split('^'); strOrigPos = strTemp[0].ToString(); strTemp = null; } } } for (int iData = validRowCnt; iData < dsSheet.Tables[0].Rows.Count; iData++) { if (strCusipPos.Trim() != "") strCusip = dsSheet.Tables[0].Rows[iData][strCusipPos].ToString().Trim(); if (strOrigPos.Trim() != "") strOrig = dsSheet.Tables[0].Rows[iData][strOrigPos].ToString().Trim(); if (strPricePos.Trim() != "") strPrice = dsSheet.Tables[0].Rows[iData][strPricePos].ToString().Trim().ToUpper(); strData = ""; if (strCusip.Length == 9 && strCusip != "" && strPrice != "" && strOrig != "" && !strPrice.ToUpper().Contains("SOLD")) strData = strCusip + "|" + Convert.ToDouble(strOrig) * 1000000 + "|" + strPrice + "|||||"; if (strData != null && strData != "") collOutput.Add(strData); strCusip = string.Empty; strOrig = string.Empty; strPrice = string.Empty; strData = string.Empty; } } } catch (Exception ex) { throw ex; } } headermatch = false; sColumnPositions = string.Empty; strColumnHeader = string.Empty; } } } private bool isValidSheet(DataRow dr) { bool isValidSheet = false; sSheetName = dr[2].ToString().ToUpper(); if (!(sSheetName.Contains("$_")) && !(sSheetName.Contains("$'_")) && (!sSheetName.Contains("Print_Titles".ToUpper())) && (dr[3].ToString() == "TABLE" && ((!sSheetName.Contains("Print_Area".ToUpper())))) && !(sSheetName.ToUpper() == "DLOFFERLOOKUP")) { if (sSheetName.Trim().ToUpper() != "Disclaimer$".ToUpper() && sSheetName.Trim().ToUpper() != "Summary$".ToUpper() && sSheetName.Trim().ToUpper() != "FORMULAS$" ) { string sQry = string.Empty; sQry = "SELECT * FROM [" + sSheetName + "]"; ExcelCommand = new OleDbCommand(sQry, ExcelConnection); dsSheet = new DataSet(); ExcelAdapter = new OleDbDataAdapter(ExcelCommand); isValidSheet = false; try { ExcelAdapter.Fill(dsSheet, sSheetName); isValidSheet = true; } catch (Exception ex) { isValidSheet = false; throw new Exception(ex.Message.ToString()); } finally { ExcelAdapter = null; ExcelCommand = null; } } } return isValidSheet; } private DataTable GetSchemaTable() { DataTable dt = null; string connectionString = String.Empty; connectionString = GetConnectionString(); ExcelConnection = new OleDbConnection(connectionString); try { ExcelConnection.Open(); dt = ExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); } catch (Exception ex) { throw ex; } return dt; } private string GetConnectionString() { string connStr = String.Empty; try { if (path.ToLower().Contains(".xlsx")) { connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source='" + path + "';" + "Extended Properties='Excel 12.0;HDR=No;IMEX=1;'"; } else if (path.ToLower().Contains(".xlsm")) { connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source='" + path + "';" + "Extended Properties='Excel 12.0 Macro;HDR=No;IMEX=1;'"; } else if (path.ToLower().Contains(".xls")) { connStr = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties='Excel 8.0;HDR=No;IMEX=1;'"; } else {connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source='" + path + "';" + "Extended Properties='HTML Import;IMEX=1;HDR=No;'"; } } catch (Exception ex) { throw ex; } return connStr; } }
这篇关于C#2.0解析Excel电子表格的最快方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文