/// <summary>
/// 将集合写入Excel字节数组
/// </summary>
/// <param name="enumerable">集合对象</param>
/// <param name="excelVersion">Excel版本</param>
/// <param name="titles">标题列表</param>
/// <returns>字节数组</returns>
public static byte[] WriteStream <T>(IEnumerable <T> enumerable, ExcelVersion excelVersion, string[] titles = null)
{
T[] array = enumerable == null ? new T[0] : enumerable.ToArray();
#region # 验证
if (!array.Any())
{
throw new ArgumentNullException(nameof(enumerable), $"源{typeof(T).Name}类型集合不可为空！");
}
#endregion
string extensionName = ExcelConductor.ExcelVersions[excelVersion];
IWorkbook workbook = CreateWorkbook(extensionName, array, titles);
//写入内存流
using (MemoryStream stream = new MemoryStream())
{
workbook.Write(stream);
byte[] buffer = stream.ToArray();
return(buffer);
}
}
/// <summary>
/// 从Excel文件中导入数据
/// </summary>
public void Import()
{
try
{
using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
IWorkbook workbook; // = WorkbookFactory.Create((Stream)fs);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
excelVersion = ExcelVersion.XLSX;
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
excelVersion = ExcelVersion.XLS;
}
else
{
throw new FapException("excel格式错误");
}
SheetMetadata sheetMetadata = this.CollectMetadata(workbook);
this.Import(workbook, sheetMetadata);
}
}
catch (Exception ex)
{
throw new FapException(ex.Message, ex);
}
}
static void AddValidations(ISheet sheet, ExcelVersion version, params DataValidation[] validations)
{
IDataValidationHelper helper = sheet.GetDataValidationHelper();
foreach (DataValidation validation in validations)
{
if ((validation.List == null || validation.List.Count == 0) && validation.Name == null)
{
throw new InvalidOperationException("Validation is invalid");
}
IDataValidationConstraint constraint = validation.Name != null?
helper.CreateFormulaListConstraint(validation.Name) :
helper.CreateExplicitListConstraint(validation.List.ToArray());
var range = new CellRangeAddressList(
validation.Range.RowStart ?? 0,
validation.Range.RowEnd ?? ExcelHelper.GetRowMax(version) - 1,
validation.Range.ColumnStart ?? 0,
validation.Range.ColumnEnd ?? ExcelHelper.GetColumnMax(version) - 1);
IDataValidation dataValidation = helper.CreateValidation(constraint, range);
sheet.AddValidationData(dataValidation);
}
}
static IWorkbook CreateWorkbook(Workbook workbook, ExcelVersion version)
{
IWorkbook iWorkbook;
switch (version)
{
case ExcelVersion.Xls:
iWorkbook = new HSSFWorkbook();
break;
case ExcelVersion.Xlsx:
iWorkbook = new XSSFWorkbook();
break;
default: throw new InvalidEnumArgumentException("version", (int)version, version.GetType());
}
AddNames(iWorkbook, version, workbook.Names.ToArray());
foreach (Worksheet worksheet in workbook.Worksheets)
{
ISheet sheet = iWorkbook.CreateSheet(worksheet.Name);
AddValidations(sheet, version, worksheet.Validations.ToArray());
AddRows(sheet, worksheet.Rows.ToArray());
if (worksheet.IsHidden)
{
iWorkbook.SetSheetHidden(worksheet.Index, SheetState.Hidden);
}
}
return(iWorkbook);
}
public ExcelBook(byte[] bytes, ExcelVersion version)
{
Version = version;
var memory = new MemoryStream(bytes);
MapedWorkbook = Open(memory, version);
}
public static string RangeToString(Range range, ExcelVersion version)
{
string sheetName = range.SheetName != null ? range.SheetName + "!" : "";
Func <object, string> prepend = s => (sheetName.Length > 0 ? "$" : "") + s;
Func <int?, int?, Func <int, ExcelVersion, string>, RowColumn, Tuple <string, string> > getCellString = (start, end, convert, rowColumn) =>
{
string startString = "", endString = "";
if (start != null || end != null)
{
startString = prepend(convert(start ?? 0, version));
endString = prepend(convert(end ?? GetMax(rowColumn, version) - 1, version));
}
return(new Tuple <string, string>(startString, endString));
};
Tuple <string, string> row = getCellString(range.RowStart, range.RowEnd, ConvertIndexToRowNumber, RowColumn.Row);
Tuple <string, string> column = getCellString(range.ColumnStart, range.ColumnEnd, ConvertIndexToColumnLetters, RowColumn.Column);
string first = column.Item1 + row.Item1;
string second = column.Item2 + row.Item2;
string rangeString = first == second ? first : string.Format("{0}:{1}", first, second);
return(sheetName + rangeString);
}
public override bool Load(string path, ExcelVersion versin = ExcelVersion.Excel2003)
{
Application excel = new Application
{
Visible = false,
UserControl = true,
DisplayAlerts = false,
AlertBeforeOverwriting = false
};
object missing = System.Reflection.Missing.Value;
Workbook wb = excel.Application.Workbooks.Open(path, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
tables = new List <object[, ]>();
for (int i = 0; i < wb.Worksheets.Count; i++)
{
// get the values in the range, excluding titles.
Worksheet ws = (Worksheet)wb.Worksheets.get_Item(i + 1);
int rows = ws.UsedRange.Cells.Rows.Count; //得到行数
int columns = ws.UsedRange.Cells.Columns.Count; //得到列数
string lefttop = "A1";
string rightbottom = new Cell(rows, columns).ToString();
tables.Add((object[, ])ws.Cells.get_Range(lefttop, rightbottom).Value2);
}
wb.Close();
return(true);
}
public static int?ConvertColumnLettersToIndex(string columnLetters, ExcelVersion version)
{
if (columnLetters == "")
{
return(null);
}
int columnMax = GetColumnMax(version);
int column = 0;
for (int i = 0; i < columnLetters.Length; i++)
{
int num = columnLetters[columnLetters.Length - 1 - i] - 64;
column += num * (int)Math.Pow(26, i);
//if (column > columnMax) return null;
if (column > columnMax)
{
throw new InvalidOperationException("column letters must be less than or equal to " + ConvertIndexToColumnLetters(columnMax - 1, version));
}
}
int index = column - 1;
return(index);
}
public static string ConvertIndexToColumnLetters(int index, ExcelVersion version)
{
if (index < 0)
{
throw new InvalidOperationException("column index must be greater than or equal to zero");
}
int columnNumber = index + 1;
int columnMax = GetColumnMax(version);
if (columnNumber > columnMax)
{
throw new InvalidOperationException("column index must be less than " + columnMax);
}
var columnLetters = new StringBuilder();
do
{
int rem = columnNumber % 26;
columnLetters.Append((char)(rem + 64));
} while ((columnNumber = columnNumber / 26) != 0);
char[] charArray = columnLetters.ToString().ToCharArray();
Array.Reverse(charArray);
return(new string(charArray));
}
public static DataTable ReadExcel(string filePath, ExcelVersion excelType)
{
string connStr;
switch (excelType)
{
case ExcelVersion.Excel2007:
connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;'", filePath);
break;
default:
connStr = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", filePath);
break;
}
OleDbConnection conn = new OleDbConnection(connStr);
conn.Open();
DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
conn.Close();
DataTable dt = new DataTable();
if (table.Rows.Count > 0)
{
OleDbDataAdapter da = new OleDbDataAdapter(string.Empty, conn);
foreach (DataRow dr in table.Rows)
{
string sheetName = dr[2].ToString();
da.SelectCommand.CommandText = "Select * From [" + sheetName + "]";
da.Fill(dt);
}
}
return(dt);
}
/// <summary>
/// 尝试打开Excel连接
/// </summary>
/// <param name="filePath"></param>
/// <param name="engines">Excel对象接口引擎</param>
/// <param name="version">Excel对象文件版本</param>
/// <returns>文件打开状态</returns>
public void OpenExcel(string filePath, ExcelEngines engines, ExcelVersion version)
{
StringBuilder connectBuilder = new StringBuilder();
if (engines == ExcelEngines.JET)
{
connectBuilder.Append("Provider=Microsoft.Jet.OleDb.4.0;");
}
if (engines == ExcelEngines.ACE)
{
connectBuilder.Append("Provider=Microsoft.Ace.OleDb.12.0;");
}
connectBuilder.Append($"Data Source={filePath};");
if (version == ExcelVersion.Office1997 || version == ExcelVersion.Office2000 || version == ExcelVersion.Office2002 || version == ExcelVersion.Office2003)
{
connectBuilder.Append($"Extended Properties='Excel 8.0; HDR=Yes; IMEX=1;'");
}
if (version == ExcelVersion.Office2007)
{
connectBuilder.Append($"Extended Properties='Excel 12.0; HDR=Yes; IMEX=1;'");
}
m_dbConnection = new OleDbConnection(connectBuilder.ToString());
try { m_dbConnection.Open(); }
catch (Exception) { throw; }
}
/// <typeparam name="T">泛型实体类</typeparam>
/// <param name="data">泛型列表对象</param>
/// <param name="FileName">保存的路径</param>
/// <param name="OpenPassword">创建Excel打开密码</param>
public static byte[] ToExcel(IList <T> data, ExcelVersion excelVersion = ExcelVersion.xlsx)
{
//获取泛型实体类的所有列头
List <ExcelParameterVo> excelParameters = GetExcelParameters();
return(ToExcelbyByte(data, excelParameters));
}
/// <summary>
/// 创建Excel并保存到服务器
/// </summary>
/// <typeparam name="T">泛型实体类</typeparam>
/// <param name="data">泛型列表对象</param>
/// <param name="FileName">保存的路径</param>
/// <param name="OpenPassword">创建Excel打开密码</param>
public static void SaveExcel(IList <T> data, string FileName, ExcelVersion excelVersion = ExcelVersion.xlsx)
{
//获取泛型实体类的所有列头
List <ExcelParameterVo> excelParameters = GetExcelParameters();
SaveExcel(data, excelParameters, FileName);
}
public static int?ConvertRowNumberToIndex(string rowNumber, ExcelVersion version)
{
if (rowNumber == "")
{
return(null);
}
int row = int.Parse(rowNumber);
if (row < 1)
{
throw new InvalidOperationException("row number must be greater than zero");
}
//if (row > GetRowMax(version)) return null;
int rowMax = GetRowMax(version);
if (row > rowMax)
{
throw new InvalidOperationException("row number must be less than or equal to " + rowMax);
}
int index = row - 1;
return(index);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="filePath">物理文件路径, 只支持xls和xlsx格式</param>
/// <param name="propertyNames">需要导出的字段名称</param>
/// <param name="headers">标题名</param>
///<param name="sheetName"></param>
public NPOIHelper(string filePath, string[] propertyNames, string[] headers = null, string sheetName = "sheet1")
{
this.filePath = filePath;
FileInfo fi = new FileInfo(filePath);
switch (fi.Extension.ToLower())
{
case ".xls":
version = ExcelVersion.xls;
break;
case ".xlsx":
version = ExcelVersion.xlsx;
break;
default:
throw new ArgumentOutOfRangeException("filePath", "只支持excel文件导出!文件扩展名仅允许xls和xlsx");
}
this.propertyNames = propertyNames;
this.sheetName = sheetName;
maxWidths = new int[propertyNames.Length];
if (headers != null)
{
WriteHeader(headers);
currentRowIndex++;
}
setInfo();
}
private static IWorkbook GetWorkbookInstance(ExcelVersion version, FileStream fileStream = null)
{
if (version == ExcelVersion.V2007)
{
return(fileStream == null ? new XSSFWorkbook() : new XSSFWorkbook(fileStream));
}
return(fileStream == null ? new HSSFWorkbook() : new HSSFWorkbook(fileStream));
}
/// <summary>
/// 初始化一个<see cref="ExportBase"/>类型的实例
/// </summary>
/// <param name="version">版本</param>
protected ExportBase(ExcelVersion version)
{
Sheet = new WorkSheet();
_version = version;
_headStyle = CellStyle.Head();
_bodyStyle = CellStyle.Body();
_footStyle = CellStyle.Foot();
}
public DataSet GetDataSet(ExcelVersion version, string filePath, bool isFirstRowAsColumnNames = true)
{
var reader = GetExcelDataReader(version, filePath, isFirstRowAsColumnNames);
var result = reader.AsDataSet();
reader.Close();
return(result);
}
static void AddNames(IWorkbook workbook, ExcelVersion version, params NamedRange[] names)
{
foreach (NamedRange namedRange in names)
{
IName name = workbook.CreateName();
name.NameName = namedRange.Name;
name.RefersToFormula = ExcelHelper.RangeToString(namedRange.Range, version);
}
}
public static void OutputDataTableToStream(DataTable table, Stream stream,
ExcelVersion version = ExcelVersion.V2007)
{
var workbook = GetWorkbookInstance(version);
var maxRowNumber = GetMaxRows(version);
OutputDataTableToStream(table, stream, workbook, maxRowNumber);
workbook.Write(stream);
}
public static Stream RenderDataTableToExcel(DataTable table, ExcelVersion version = ExcelVersion.V2007)
{
using (var ms = new MemoryStream())
{
OutputDataTableToStream(table, ms);
ms.Flush();
return(ms);
}
}
public static IComment CreateComment(ISheet sheet, string text, ExcelVersion version)
{
var patr = sheet.CreateDrawingPatriarch();
IComment comment;
comment = patr.CreateCellComment(CreateClientAnchor(0, 0, 0, 0, 1, 2, 4, 6));
comment.String = CreateRichTextString(text, version);
return(comment);
}
public IExcelDataReader GetExcelDataReader(ExcelVersion version, string filePath, bool isFirstRowAsColumnNames = true)
{
var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
var excelReader = version == ExcelVersion.Version_97_2003 ?
ExcelReaderFactory.CreateBinaryReader(stream) :
ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = isFirstRowAsColumnNames;
return(excelReader);
}
/// <summary>
/// 返回指定文件所包含的工作簿列表;如果有WorkSheet，就返回以工作簿名字命名的ArrayList，否则返回空
/// </summary>
/// <param name="filePath">要获取的Excel</param>
/// <param name="excelVersion">文档版本</param>
/// <returns>如果有WorkSheet，就返回以工作簿名字命名的string[]，否则返回空</returns>
private static string[] GetExcelWorkSheets(string filePath, ExcelVersion excelVersion)
{
List <string> alTables = new List <string>();
string connectionString = string.Format(GetConnectionString(excelVersion, ImportOrExportType.Import),
filePath, "Yes");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
DataTable dt = new DataTable();
dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
throw new Exception("无法获取指定Excel的架构。");
}
foreach (DataRow dr in dt.Rows)
{
string tempName = dr["Table_Name"].ToString();
int iDolarIndex = tempName.IndexOf('$');
if (iDolarIndex > 0)
{
tempName = tempName.Substring(0, iDolarIndex);
}
//修正Excel2003中某些工作薄名称为汉字的表无法正确识别的BUG。
if (tempName[0] == '\'')
{
if (tempName[tempName.Length - 1] == '\'')
{
tempName = tempName.Substring(1, tempName.Length - 2);
}
else
{
tempName = tempName.Substring(1, tempName.Length - 1);
}
}
if (!alTables.Contains(tempName))
{
alTables.Add(tempName);
}
}
}
if (alTables.Count == 0)
{
return(null);
}
return(alTables.ToArray());
}
public static IRichTextString CreateRichTextString(string str, ExcelVersion version = ExcelVersion.Excel2007)
{
if (ExcelVersion.Excel2003 == version)
{
return(new HSSFRichTextString(str));
}
else
{
return(new XSSFRichTextString(str));
}
}
public func_excel_read_ole(ExcelVersion ver)
{
if (ver == ExcelVersion.e2003)
{
sConnstring = sConnstring2003;
}
else
{
sConnstring = sConnstring2007;
}
}
public ExcelBook(string path, ExcelVersion version)
{
Version = version;
FilePath = path;
if (!File.Exists(path))
{
throw new FileNotFoundException();
}
using (var file = new FileStream(path, FileMode.Open, FileAccess.ReadWrite))
MapedWorkbook = Open(file, version);
}
/// <summary>
/// 创建导出器
/// </summary>
/// <param name="version">Excel版本</param>
/// <returns></returns>
public IExport Create(ExcelVersion version)
{
switch (version)
{
case ExcelVersion.Xlsx:
return(new Excel2007Export());
case ExcelVersion.Xls:
return(new Excel2003Export());
}
throw new NotImplementedException();
}
/// <summary>
/// 从指定的流中创建一个 Workbook，用于读取 Excel 中的数据.
/// </summary>
/// <param name="stream">Excel 文件流</param>
/// <param name="version">要生成 Workbook 版本</param>
public static IWorkbook Create(Stream stream, ExcelVersion version)
{
switch (version)
{
case ExcelVersion.Excel2003:
return new HSSFWorkbook(stream);
case ExcelVersion.Excel2007:
return new XSSFWorkbook(stream);
default:
throw new InvalidOperationException($"The file extension is out of ExcelVersion.");
}
}
/// <summary>
/// 创建导入器
/// </summary>
/// <param name="version">Excel格式</param>
/// <returns></returns>
public IImport Create(ExcelVersion version)
{
switch (version)
{
case ExcelVersion.Xlsx:
return(CreateExcel2007Import(_path, _sheetName));
case ExcelVersion.Xls:
return(CreateExcel2003Import(_path, _sheetName));
}
throw new NotImplementedException();
}
private string getConn(ExcelVersion version)
{
string sconn;
if (version == ExcelVersion.v2007)
{
sconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml;";
}
else
{
sconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";
}
return sconn;
}
public static string ToExcelFile(DataTable table, string filePath, string fileName, ref int processIndex, string title = null,
List <string> computeColumns = null, ExcelVersion version = ExcelVersion.Excel2007)
{
if (string.IsNullOrEmpty(table.TableName))
{
table.TableName = DateTime.Now.ToString("yyyyMMddHHmmss");
}
;
DataSet ds = new DataSet(table.TableName);
ds.Tables.Add(table);
return(ToExcelFile(ds, filePath, fileName, ref processIndex, title, computeColumns, version));
}
/// <summary>
/// 初始化<c>Excel</c>实例
/// </summary>
/// <param name="version">Excel 版本号</param>
protected ExcelRecord(ExcelVersion version)
{
switch (version)
{
case ExcelVersion.Excel2003:
this._workbook = new HSSFWorkbook();
break;
case ExcelVersion.Excel2007:
this._workbook = new XSSFWorkbook();
break;
default:
throw new InvalidEnumArgumentException("The Excel version is not matched.");
}
}
//private static readonly int m_maxSheelSize = 65000;
#region 公用静态方法
#region 从Excel读数据
/// <summary>
/// 从Excel读数据
/// </summary>
/// <param name="filePath">excel文档路径</param>
/// <param name="excelVersion">文档版本</param>
/// <param name="pHDR">第一行是否标题</param>
/// <param name="bMerge">
/// 如果有多页，是否合并数据，合并时必须保证多页的表结构一致
/// </param>
/// <returns>DataTable集</returns>
public static DataTable[] GetExcelData(string filePath, ExcelVersion excelVersion, HeadRowType pHDR, bool bMerge)
{
List<DataTable> dtResult = new List<DataTable>();
string connectionString = string.Format(GetConnectionString(excelVersion, ImportOrExportType.Import),
filePath, pHDR);
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
string[] sheels = GetExcelWorkSheets(filePath, excelVersion);
foreach (string sheelName in sheels)
{
try
{
DataTable dtExcel = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + sheelName + "$]", con);
adapter.FillSchema(dtExcel, SchemaType.Mapped);
adapter.Fill(dtExcel);
dtExcel.TableName = sheelName;
dtResult.Add(dtExcel);
}
catch
{
//容错处理：取不到时，不报错，结果集为空即可。
}
}
//如果需要合并数据，则合并到第一张表
if (bMerge)
{
for (int i = 1; i < dtResult.Count; i++)
{
//如果不为空才合并
if (dtResult[0].Columns.Count == dtResult[i].Columns.Count &&
dtResult[i].Rows.Count > 0)
{
dtResult[0].Load(dtResult[i].CreateDataReader());
}
}
}
}
return dtResult.ToArray();
}
/// <summary>
/// Builds the SQL Statement based upon the expression
/// </summary>
/// <param name="expression">Expression tree being used</param>
/// <param name="columnMapping">
/// Property to column mapping.
/// Properties are the dictionary keys and the dictionary values are the corresponding column names.
/// </param>
/// <param name="worksheetName">Name of the Excel worksheet</param>
/// <returns>Returns an SQL statement based upon the expression</returns>
internal void BuildSQLStatement(Expression expression, Dictionary<string, string> columnMapping, string worksheetName, string fileName, ExcelVersion fileType)
{
_params = new List<OleDbParameter>();
_map = columnMapping;
_sql = new StringBuilder();
string tableName;
if (fileType == ExcelVersion.Csv)
tableName = Path.GetFileName(fileName);
else
tableName = (String.IsNullOrEmpty(worksheetName)) ?
"[Sheet1$]" :
"[" + worksheetName + "$]";
_sql.Append(string.Format("SELECT * FROM {0}", tableName));
this.Visit(expression);
this.SQLStatement = _sql.ToString();
this.Parameters = _params;
}
public DataSet GetDataSet(ExcelVersion version, string filePath, bool isFirstRowAsColumnNames = true)
{
var reader = GetExcelDataReader(version, filePath, isFirstRowAsColumnNames);
var result = reader.AsDataSet();
reader.Close();
return result;
}
public DataReaderExcelWriter(ExcelVersion version)
{
_excelWriter = new ExcelWriter(version);
}
/// <summary>
/// 创建基于集合的 Excel 写入器
/// </summary>
/// <param name="version">要创建的 Excel 读取器版本, 默认 2007+</param>
/// <returns></returns>
public static CollectionExcelWriter CreateCollectionWriter(ExcelVersion version = ExcelVersion.Excel2007)
{
return new CollectionExcelWriter(version);
}
public ExcelOLEDB(string file, ExcelVersion version)
{
string sconn = string.Format(this.getConn(version), file);
conn = new OleDbConnection(sconn);
}
/// <summary>
/// 创建基于 <see cref="System.Data.IDataReader"/> 的 Excel 写入器
/// </summary>
/// <param name="version">要创建的 Excel 读取器版本, 默认 2007+</param>
/// <returns></returns>
public static DataReaderExcelWriter CreateDataReaderWriter(ExcelVersion version = ExcelVersion.Excel2007)
{
return new DataReaderExcelWriter(version);
}
/// <summary>
/// Executes the query based upon the Linq statement against the Excel worksheet
/// </summary>
/// <param name="expression">Expression created from the Linq statement</param>
/// <param name="fileName">File path to the Excel workbook</param>
/// <param name="columnMapping">
/// Property to column mapping.
/// Properties are the dictionary keys and the dictionary values are the corresponding column names.
/// </param>
/// <param name="worksheetName">Name of the Excel worksheet</param>
/// <returns>Returns the results from the query</returns>
public object ExecuteQuery(Expression expression, Type dataType, string fileName, ExcelVersion fileType, Dictionary<string, string> columnMapping, string worksheetName)
{
//Build the SQL string
SQLExpressionVisitor sql = new SQLExpressionVisitor();
sql.BuildSQLStatement(expression, columnMapping, worksheetName, fileName, fileType);
PropertyInfo[] props = sql.SheetType.GetProperties();
//string connString = (fileType == ExcelVersion.Csv) ?
// string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""text;HDR=Yes;FMT=Delimited;""",
// Path.GetDirectoryName(fileName)) :
// string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", fileName);
string connString = null;
switch (fileType)
{
case ExcelVersion.Csv:
connString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""text;HDR=Yes;FMT=Delimited;""", Path.GetDirectoryName(fileName));
break;
case ExcelVersion.PreExcel2007:
connString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", fileName);
break;
case ExcelVersion.Excel2007:
connString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""", fileName);
break;
}
object results = Activator.CreateInstance(typeof(List<>).MakeGenericType(sql.SheetType));
using (OleDbConnection conn = new OleDbConnection(connString))
using (OleDbCommand command = conn.CreateCommand())
{
conn.Open();
command.CommandText = sql.SQLStatement;
command.Parameters.Clear();
command.Parameters.AddRange(sql.Parameters.ToArray());
OleDbDataReader data = command.ExecuteReader();
//Get the excel column names
List<string> columns = new List<string>();
DataTable sheetSchema = data.GetSchemaTable();
foreach (DataRow row in sheetSchema.Rows)
columns.Add(row["ColumnName"].ToString());
if (sql.SheetType == typeof(Row))
{
Dictionary<string, int> columnIndexMapping = new Dictionary<string, int>();
for (int i = 0; i < columns.Count; i++)
columnIndexMapping[columns[i]] = i;
while (data.Read())
{
IList<Cell> cells = new List<Cell>();
for (int i = 0; i < columns.Count; i++)
cells.Add(new Cell(data[i]));
results.CallMethod("Add", new Row(cells, columnIndexMapping));
}
}
else
{
while (data.Read())
{
object result = Activator.CreateInstance(sql.SheetType);
foreach (PropertyInfo prop in props)
{
//Set the column name to the property mapping if there is one, else use the property name for the column name
string columnName = (columnMapping.ContainsKey(prop.Name)) ? columnMapping[prop.Name] : prop.Name;
if (columns.Contains(columnName))
result.SetProperty(prop.Name, Convert.ChangeType(data[columnName], prop.PropertyType));
}
results.CallMethod("Add", result);
}
}
}
return results;
}
public OleDBIncomingDataParser(ExcelVersion excelVersion)
: this(excelVersion, true)
{
}
public void SetFormat(ExcelVersion version)
{
ExcelVersion = version;
}
/// <summary>
/// 创建 Excel 读取器
/// </summary>
/// <param name="version">要创建的 Excel 读取器版本, 默认 2007+</param>
/// <returns></returns>
public static IExcelReader CreateReader(Stream stream, ExcelVersion version = ExcelVersion.Excel2007)
{
var workBook = WorkBookFactory.Create(stream, version);
return new JsonExcelReader(workBook);
}
/// <summary>
/// 获得连接
/// </summary>
/// <param name="excelVersion"></param>
/// <returns></returns>
private static string GetConnectionString(ExcelVersion excelVersion, ImportOrExportType etype)
{
if (etype == ImportOrExportType.Import)
{
if (excelVersion == ExcelVersion.Excel12)
{
return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR={1};IMEX=1'";
}
else if (excelVersion == ExcelVersion.Excel3)
{
return "Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}; Extended Properties='Excel 3.0;HDR={1};IMEX=1'";
}
else if (excelVersion == ExcelVersion.Excel4)
{
return "Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}; Extended Properties='Excel 4.0;HDR={1};IMEX=1'";
}
else if (excelVersion == ExcelVersion.Excel5)
{
return "Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}; Extended Properties='Excel 5.0;HDR={1};IMEX=1'";
}
else
{
return "Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}; Extended Properties='Excel 8.0;HDR={1};IMEX=1'";
}
}
else
{
if (excelVersion == ExcelVersion.Excel12)
{
return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR={1}'";
}
else if (excelVersion == ExcelVersion.Excel3)
{
return "Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}; Extended Properties='Excel 3.0;HDR={1}'";
}
else if (excelVersion == ExcelVersion.Excel4)
{
return "Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}; Extended Properties='Excel 4.0;HDR={1};'";
}
else if (excelVersion == ExcelVersion.Excel5)
{
return "Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}; Extended Properties='Excel 5.0;HDR={1};'";
}
else
{
return "Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}; Extended Properties='Excel 8.0;HDR={1};'";
}
}
}
/// <summary>
/// 返回指定文件所包含的工作簿列表;如果有WorkSheet，就返回以工作簿名字命名的ArrayList，否则返回空
/// </summary>
/// <param name="filePath">要获取的Excel</param>
/// <param name="excelVersion">文档版本</param>
/// <returns>如果有WorkSheet，就返回以工作簿名字命名的string[]，否则返回空</returns>
private static string[] GetExcelWorkSheets(string filePath, ExcelVersion excelVersion)
{
List<string> alTables = new List<string>();
string connectionString = string.Format(GetConnectionString(excelVersion, ImportOrExportType.Import),
filePath, "Yes");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
DataTable dt = new DataTable();
dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
throw new Exception("无法获取指定Excel的架构。");
}
foreach (DataRow dr in dt.Rows)
{
string tempName = dr["Table_Name"].ToString();
int iDolarIndex = tempName.IndexOf('$');
if (iDolarIndex > 0)
{
tempName = tempName.Substring(0, iDolarIndex);
}
//修正Excel2003中某些工作薄名称为汉字的表无法正确识别的BUG。
if (tempName[0] == '\'')
{
if (tempName[tempName.Length - 1] == '\'')
{
tempName = tempName.Substring(1, tempName.Length - 2);
}
else
{
tempName = tempName.Substring(1, tempName.Length - 1);
}
}
if (!alTables.Contains(tempName))
{
alTables.Add(tempName);
}
}
}
if (alTables.Count == 0)
{
return null;
}
return alTables.ToArray();
}
public ExcelEngine()
{
ExcelVersion = ExcelVersion.XLSX;
}
public IExcelDataReader GetExcelDataReader(ExcelVersion version, string filePath, bool isFirstRowAsColumnNames = true)
{
var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
var excelReader = version == ExcelVersion.Version_97_2003 ?
ExcelReaderFactory.CreateBinaryReader(stream) :
ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = isFirstRowAsColumnNames;
return excelReader;
}
/// <summary>
/// 初始化<c>Excel</c>实例
/// </summary>
/// <param name="version">Excel 版本号</param>
public ExcelWriter(ExcelVersion version)
{
_workbook = WorkBookFactory.Create(version);
}
public OleDBIncomingDataParser(ExcelVersion excelVersion, bool withHeader)
: this("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$1;Extended Properties=\"Excel " +
((int)excelVersion).ToString(CultureInfo.InvariantCulture) + ".0;HDR=" + (withHeader ? "Yes" : "No") + "\"")
{
}
