C# NETExcel操作辅助类无需VBA引用.docx
- 文档编号:18240092
- 上传时间:2023-08-14
- 格式:DOCX
- 页数:21
- 大小:18.66KB
C# NETExcel操作辅助类无需VBA引用.docx
《C# NETExcel操作辅助类无需VBA引用.docx》由会员分享,可在线阅读,更多相关《C# NETExcel操作辅助类无需VBA引用.docx(21页珍藏版)》请在冰点文库上搜索。
C#NETExcel操作辅助类无需VBA引用
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Data.OleDb;
usingSystem.IO;
usingSystem.Windows.Forms;
usingWHC.OrderWater.Commons;
namespaceCommons.File
{
///
///Excel操作辅助类(无需VBA引用)
///
publicclassExcelHelper
{
///
///Excel版本
///
publicenumExcelType
{
Excel2003Excel2007
}
///
///IMEX三种模式。
///IMEX是用来告诉驱动程序使用Excel文件的模式,其值有0、1、2三种,分别代表导出、导入、混合模式。
///
publicenumIMEXType
{
ExportMode=0ImportMode=1lixxxxnkedMode=2
}
#region获取Excel连接字符串
///
///返回Excel连接字符串[IMEX=1]
///
///
///
///
///
publicstaticstringGetExcelConnectstring(stringexcelPathboolheaderExcelTypeeType)
{
returnGetExcelConnectstring(excelPathheadereTypeIMEXType.ImportMode);
}
///
///返回Excel连接字符串
///
///
///
///
///
///
publicstaticstringGetExcelConnectstring(stringexcelPathboolheaderExcelTypeeTypeIMEXTypeimex)
{
if(!
FileUtil.IsExistFile(excelPath))
thrownewFileNotFoundException("Excel路径不存在!
");
stringconnectstring=string.Empty;
stringhdr="NO";
if(header)
hdr="YES";
if(eType==ExcelType.Excel2003)
connectstring="Provider=Microsoft.Jet.OleDb.4.0;datasource="+excelPath+";ExtendedProperties='Excel8.0;HDR="+hdr+";IMEX="+imex.GetHashCode()+"'";
else
connectstring="Provider=Microsoft.ACE.OLEDB.12.0;datasource="+excelPath+";ExtendedProperties='Excel12.0xmxxxxl;HDR="+hdr+";IMEX="+imex.GetHashCode()+"'";
returnconnectstring;
}
#endregion
#region获取Excel工作表名
///
///返回Excel工作表名
///
///
///
///
publicstaticList
{
stringconnectstring=GetExcelConnectstring(excelPathtrueeType);
returnGetExcelTablesName(connectstring);
}
///
///返回Excel工作表名
///
///
///
publicstaticList
{
using(OleDbConnectionconn=newOleDbConnection(connectstring))
{
returnGetExcelTablesName(conn);
}
}
///
///返回Excel工作表名
///
///
///
publicstaticList
{
List
if(connection.State==ConnectionState.Closed)
connection.Open();
DataTabledt=connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tablesnull);
if(dt!
=null&&dt.Rows.Count>0)
{
for(inti=0;i
{
list.Add(ConvertHelper.ConvertTo
}
}
returnlist;
}
///
///返回Excel第一个工作表表名
///
///
///
///
publicstaticstringGetExcelFirstTableName(stringexcelPathExcelTypeeType)
{
stringconnectstring=GetExcelConnectstring(excelPathtrueeType);
returnGetExcelFirstTableName(connectstring);
}
///
///返回Excel第一个工作表表名
///
///
///
publicstaticstringGetExcelFirstTableName(stringconnectstring)
{
using(OleDbConnectionconn=newOleDbConnection(connectstring))
{
returnGetExcelFirstTableName(conn);
}
}
///
///返回Excel第一个工作表表名
///
///
///
publicstaticstringGetExcelFirstTableName(OleDbConnectionconnection)
{
stringtableName=string.Empty;
if(connection.State==ConnectionState.Closed)
connection.Open();
DataTabledt=connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tablesnull);
if(dt!
=null&&dt.Rows.Count>0)
{
tableName=ConvertHelper.ConvertTo
}
returntableName;
}
///
///获取Excel文件中指定工作表的列
///
///
///
Sheet1$
///
publicstaticList
{
List
DataTabletableColumns=null;
stringconnectstring=GetExcelConnectstring(excelPathtrueeType);
using(OleDbConnectionconn=newOleDbConnection(connectstring))
{
conn.Open();
tableColumns=GetReaderSchema(tableconn);
}
foreach(DataRowdrintableColumns.Rows)
{
stringcolumnName=dr["ColumnName"].ToString();
stringdatatype=((OleDbType)dr["ProviderType"]).ToString();//对应数据库类型
stringnetType=dr["DataType"].ToString();//对应的.NET类型,如System.String
list.Add(columnName);
}
returnlist;
}
privatestaticDataTableGetReaderSchema(stringtableNameOleDbConnectionconnection)
{
DataTableschemaTable=null;
IDbCommandcmd=newOleDbCommand();
cmd.CommandText=string.Format("select*from[{0}]"tableName);
cmd.Connection=connection;
using(IDataReaderreader=cmd.ExecuteReader(CommandBehavior.KeyInfo|CommandBehavior.SchemaOnly))
{
schemaTable=reader.GetSchemaTable();
}
returnschemaTable;
}
#endregion
#regionEXCEL导入DataSet
///
///EXCEL导入DataSet
///
///
///
Sheet1$
///
///
///
publicstaticDataSetExcelToDataSet(stringexcelPathstringtableboolheaderExcelTypeeType)
{
stringconnectstring=GetExcelConnectstring(excelPathheadereType);
returnExcelToDataSet(connectstringtable);
}
///
///判断工作表名是否存在
///
///
///
Sheet1$
///
privatestaticboolisExistExcelTableName(OleDbConnectionconnectionstringtable)
{
List
foreach(stringtNameinlist)
{
if(tName.ToLower()==table.ToLower())
returntrue;
}
returnfalse;
}
///
///EXCEL导入DataSet
///
///
///
Sheet1$
///
publicstaticDataSetExcelToDataSet(stringconnectstringstringtable)
{
using(OleDbConnectionconn=newOleDbConnection(connectstring))
{
DataSetds=newDataSet();
//判断该工作表在Excel中是否存在
if(isExistExcelTableName(conntable))
{
OleDbDataAdapteradapter=newOleDbDataAdapter("SELECT*FROM["+table+"]"conn);
adapter.Fill(dstable);
}
returnds;
}
}
///
///EXCEL所有工作表导入DataSet
///
///
///
///
///
publicstaticDataSetExcelToDataSet(stringexcelPathboolheaderExcelTypeeType)
{
stringconnectstring=GetExcelConnectstring(excelPathheadereType);
returnExcelToDataSet(connectstring);
}
///
///EXCEL所有工作表导入DataSet
///
///
///
publicstaticDataSetExcelToDataSet(stringconnectstring)
{
using(OleDbConnectionconn=newOleDbConnection(connectstring))
{
DataSetds=newDataSet();
List
foreach(stringtableNameintableNames)
{
OleDbDataAdapteradapter=newOleDbDataAdapter("SELECT*FROM["+tableName+"]"conn);
adapter.Fill(dstableName);
}
returnds;
}
}
#endregion
///
///把一个数据集中的数据导出到Excel文件中(xmxxxxl格式操作)
///
///
///
publicstaticvoidDataSetToExcel(DataSetsourcestringfileName)
{
#regionExcel格式内容
varexcelDoc=newStreamWriter(fileName);
conststringstartExcelxmxxxxl=" "xmxxxxlns=\"urn: schemas-microsoft-com: office: spreadsheet\"\r\n"+ "xmxxxxlns: o=\"urn: schemas-microsoft-com: office: office\"\r\n"+ "xmxxxxlns: x=\"urn: schemas-microsoft-com: office: "+ "excel\"\r\nxmxxxxlns: ss=\"urn: schemas-microsoft-com: "+ "office: spreadsheet\">\r\n " ID=\"Default\"ss: Name=\"Normal\">\r\n"+ " Vertical=\"Bottom\"/>\r\n "\r\n\r\n "\r\n " ID=\"BoldColumn\">\r\n "x: Family=\"Swiss\"ss: Bold=\"1\"/>\r\n\r\n"+ " ID=\"StringLiteral\">\r\n "ss: Format=\"@\"/>\r\n\r\n\r\n"+ " ID=\"Integer\">\r\n "ss: Format=\"0\"/>\r\n\r\n\r\n"+ "\r\n"; conststringendExcelxmxxxxl="
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- C# NET Excel操作辅助类无需VBA引用 Excel 操作 辅助 无需 VBA 引用