public static MemoryStream CreateExcelSheet(DataSet dataToProcess, ExcelType excelType = ExcelType.Xlsx)
{
MemoryStream stream = new MemoryStream();
try
{
if (dataToProcess != null)
{
switch (excelType)
{
case ExcelType.Xls:
{
stream = CreateXlsDocument(dataToProcess);
break;
}
case ExcelType.Xlsx:
{
stream = CreateXlsxDocument(dataToProcess);
break;
}
}
}
}
catch (Exception error)
{
throw error;
}
return stream;
}
/// <summary>
/// Gets an IExcel instance based on fileName and excelType.
/// </summary>
/// <param name="fileName">Full path of the excel file.</param>
/// <param name="excelType">Defines the library to utilise.</param>
/// <returns>Default is EPPlusExcel but if fileName extension is .xls returns NPOIExcel.</returns>
public static IExcel GetExcel(String fileName, ExcelType excelType = ExcelType.EPPlus)
{
IExcel excel;
if (Path.GetExtension(fileName) == ".xls")
{
excel = new NPOIExcel();
}
else
{
switch (excelType)
{
case ExcelType.EPPlus:
excel = new EPPlusExcel();
break;
case ExcelType.NPOI:
excel = new NPOIExcel();
break;
default:
excel = new EPPlusExcel();
break;
}
}
excel.FileName = fileName;
excel.Workbook = excel.GetWorkbook();
return excel;
}
private static IEnumerable<OpenXmlElement> GetStyleSheetScheme(ExcelType scheme)
{
switch (scheme)
{
case ExcelType.Ceex:
case ExcelType.CeexWithLogo:
return CellStyles.CreateCeexStyle();
case ExcelType.Empty:
return CellStyles.CreateEmptyStyle();
}
throw new NotImplementedException("There is not implementation for selected color scheme");
}
/// <summary>
/// Creates Stylesheet from collections of border styles, fontstyles, color styles etc...
/// </summary>
public static Stylesheet GetStyleSheet(ExcelType scheme)
{
var stylesheet = new Stylesheet();
var fontCollection = new FontCollection();
var fillsCollection = new FillsCollection();
var borderCollection = new BorderCollection();
var cellStyleFormats = new CellStyleFormats();
var cellStyle = new CellFormat();
cellStyle.NumberFormatId = 0;
cellStyle.FontId = 0;
cellStyle.FillId = 0;
cellStyle.BorderId = 0;
cellStyleFormats.Append(cellStyle);
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
var numberingFormats = new CellContentFormat();
var cellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats();
cellFormats.Append(GetStyleSheetScheme(scheme));
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
stylesheet.Append(numberingFormats);
stylesheet.Append(fontCollection);
stylesheet.Append(fillsCollection);
stylesheet.Append(borderCollection);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
var css = new DocumentFormat.OpenXml.Spreadsheet.CellStyles();
var cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
stylesheet.Append(css);
var dfs = new DifferentialFormats();
dfs.Count = 0;
stylesheet.Append(dfs);
var tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
stylesheet.Append(tss);
return stylesheet;
}
/// <summary>
/// 將DataSet中的數據寫入到指定的Excel文件中
/// </summary>
/// <param name="ds">要寫入Excel文檔中的數據</param>
/// <param name="filePath">Excel文檔的完整路徑，即需要包含文件名及後綴名，後綴名只能是.xls或.xlsx</param>
/// <param name="type">選擇要寫入的Excel文件格式</param>
public static void DataSetToExcel(DataSet ds, string filePath, ExcelType type)
{
IWorkbook workbook = CreateWorkbook(type);
for (int i = 0; i < ds.Tables.Count; i++)
{
DataTableToSheet(ref workbook, ds.Tables[i]);
}
using (FileStream fs = File.Create(filePath))
{
workbook.Write(fs);
}
}
/// <summary>
/// 返回Excel 连接字符串
/// </summary>
/// <param name="excelPath">Excel文件 绝对路径</param>
/// <param name="header">是否把第一行作为列名</param>
/// <param name="eType">Excel 版本 </param>
/// <param name="imex">IMEX模式</param>
/// <returns>返回值</returns>
public static string GetExcelConnectstring(string excelPath, bool header, ExcelType eType, IMEXType imex)
{
if (!MyFileHelper.IsExistFile(excelPath))
throw new FileNotFoundException("Excel路径不存在!");
string connectstring;
string hdr = "NO";
if (header)
hdr = "YES";
if (eType == ExcelType.Excel2003)
connectstring = "Provider=Microsoft.Jet.OleDb.4.0; data source=" + excelPath +
";Extended Properties='Excel 8.0; HDR=" + hdr + "; IMEX=" + imex.GetHashCode() + "'";
else
connectstring = "Provider=Microsoft.ACE.OLEDB.12.0; data source=" + excelPath +
";Extended Properties='Excel 12.0 Xml; HDR=" + hdr + "; IMEX=" + imex.GetHashCode() +
"'";
return connectstring;
}
public static IEnumerable <dynamic> Query(this Stream stream, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null)
{
return(ExcelReaderFactory.GetProvider(stream, GetExcelType(stream, excelType)).Query(useHeaderRow, sheetName, configuration));
}
/// <summary>
/// Default SaveAs Xlsx file
/// </summary>
public static void SaveAs(this Stream stream, object value, bool printHeader = true, string sheetName = "Sheet1", ExcelType excelType = ExcelType.XLSX, IConfiguration configuration = null)
{
if (string.IsNullOrEmpty(sheetName))
{
throw new InvalidDataException("Sheet name can not be empty or null");
}
if (excelType == ExcelType.UNKNOWN)
{
throw new InvalidDataException("Please specify excelType");
}
ExcelWriterFactory.GetProvider(stream, excelType).SaveAs(value, sheetName, printHeader, configuration);
}
public static IEnumerable <T> Query <T>(this Stream stream, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null) where T : class, new()
{
return(ExcelReaderFactory.GetProvider(stream, GetExcelType(stream, excelType)).Query <T>(sheetName, configuration));
}
public static List <string> GetExcelTablesName(string excelPath, ExcelType eType)
{
return(GetExcelTablesName(GetExcelConnectstring(excelPath, true, eType)));
}
internal static IExcelTemplateAsync GetProvider(Stream stream, IConfiguration configuration, ExcelType excelType = ExcelType.XLSX)
{
switch (excelType)
{
case ExcelType.XLSX:
return(new ExcelOpenXmlTemplate(stream, configuration));
default:
throw new NotSupportedException($"Please Issue for me");
}
}
public abstract ImportResult Process(byte[] importFileData, ExcelType type, int siteId);
public static DataSet ExcelToDataSet(string excelPath, string table, bool header, ExcelType eType)
{
return(ExcelToDataSet(GetExcelConnectstring(excelPath, header, eType), table));
}
public static async Task <IEnumerable <T> > QueryAsync <T>(this Stream stream, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new()
{
return(await ExcelReaderFactory.GetProvider(stream, ExcelTypeHelper.GetExcelType(stream, excelType), configuration).QueryAsync <T>(sheetName, startCell, cancellationToken).ConfigureAwait(false));
}
public static async Task <IEnumerable <T> > QueryAsync <T>(string path, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new()
{
return(await Task.Run(() => Query <T>(path, sheetName, excelType, startCell, configuration), cancellationToken).ConfigureAwait(false));
}
public static async Task SaveAsAsync(this Stream stream, object value, bool printHeader = true, string sheetName = "Sheet1", ExcelType excelType = ExcelType.XLSX, IConfiguration configuration = null, CancellationToken cancellationToken = default(CancellationToken))
{
await ExcelWriterFactory.GetProvider(stream, value, sheetName, excelType, configuration, printHeader).SaveAsAsync(cancellationToken);
}
public static async Task <IEnumerable <dynamic> > QueryAsync(string path, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null, CancellationToken cancellationToken = default(CancellationToken))
{
return(await Task.Run(() => Query(path, useHeaderRow, sheetName, excelType, startCell, configuration), cancellationToken));
}
public static async Task SaveAsAsync(string path, object value, bool printHeader = true, string sheetName = "Sheet1", ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null, bool overwriteFile = false, CancellationToken cancellationToken = default(CancellationToken))
{
await Task.Run(() => SaveAs(path, value, printHeader, sheetName, excelType, configuration, overwriteFile), cancellationToken).ConfigureAwait(false);
}
private static Drawing GetLogo(ExcelType excelType, WorksheetPart wsp, out uint startIndex)
{
var dp = wsp.AddNewPart<DrawingsPart>();
Drawing drawing;
var sImagePath = GetLogoPath(excelType);
if (string.IsNullOrEmpty(sImagePath))
{
startIndex = 1;
return null;
}
startIndex = 8;
//sorry, I must do it in this way, MEmory stream oesn't work in FeedData. There are several discussions in the internet about it.
ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
var bm = new Bitmap(Resource.CEEX_small);
//var bm = new Bitmap(@"ExcelProvider\_img\CEEX_small.png");
using (var ms = new MemoryStream())
{
bm.Save(ms,ImageFormat.Png);
ms.Seek(0, 0);
imgp.FeedData(ms);
}
var nvdp = new NonVisualDrawingProperties();
nvdp.Id = 1025;
nvdp.Name = "logo";
nvdp.Description = "polymathlogo";
var picLocks = new PictureLocks();
picLocks.NoChangeAspect = true;
picLocks.NoChangeArrowheads = true;
var nvpdp = new NonVisualPictureDrawingProperties();
nvpdp.PictureLocks = picLocks;
var nvpp = new NonVisualPictureProperties();
nvpp.NonVisualDrawingProperties = nvdp;
nvpp.NonVisualPictureDrawingProperties = nvpdp;
var stretch = new Stretch();
stretch.FillRectangle = new FillRectangle();
var blipFill = new BlipFill();
var blip = new Blip();
blip.Embed = dp.GetIdOfPart(imgp);
blip.CompressionState = BlipCompressionValues.Print;
blipFill.Blip = blip;
blipFill.SourceRectangle = new SourceRectangle();
blipFill.Append(stretch);
var t2d = new Transform2D();
var offset = new Offset();
offset.X = 0;
offset.Y = 0;
t2d.Offset = offset;
//http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
//http://stackoverflow.com/questions/1341930/pixel-to-centimeter
//http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
var extents = new Extents();
extents.Cx = bm.Width * (long)(914400 / bm.HorizontalResolution);
extents.Cy = bm.Height * (long)(914400 / bm.VerticalResolution);
bm.Dispose();
t2d.Extents = extents;
var sp = new ShapeProperties();
sp.BlackWhiteMode = BlackWhiteModeValues.Auto;
sp.Transform2D = t2d;
var prstGeom = new PresetGeometry();
prstGeom.Preset = ShapeTypeValues.Rectangle;
prstGeom.AdjustValueList = new AdjustValueList();
sp.Append(prstGeom);
sp.Append(new NoFill());
var picture = new Picture();
picture.NonVisualPictureProperties = nvpp;
picture.BlipFill = blipFill;
picture.ShapeProperties = sp;
var pos = new Position();
pos.X = 0;
pos.Y = 0;
var ext = new Extent();
ext.Cx = extents.Cx;
ext.Cy = extents.Cy;
var anchor = new AbsoluteAnchor();
anchor.Position = pos;
anchor.Extent = ext;
anchor.Append(picture);
anchor.Append(new ClientData());
var wsd = new WorksheetDrawing();
wsd.Append(anchor);
drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imgp);
wsd.Save(dp);
return drawing;
}
private static string GetLogoPath(ExcelType excelType)
{
switch (excelType)
{
case ExcelType.CeexWithLogo: return @"_img\CEEX03_small.png";
case ExcelType.Ceex: return string.Empty;
default: return string.Empty;
}
}
/// <summary>
/// 返回Excel第一个工作表表名
/// </summary>
/// <param name="excelPath">Excel文件 绝对路径</param>
/// <param name="eType">Excel 版本 </param>
/// <returns></returns>
public static string GetExcelFirstTableName(string excelPath, ExcelType eType)
{
string connectstring = GetExcelConnectstring(excelPath, true, eType);
return(GetExcelFirstTableName(connectstring));
}
public static async Task <IEnumerable <dynamic> > QueryAsync(this Stream stream, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null, CancellationToken cancellationToken = default(CancellationToken))
{
TaskCompletionSource <IEnumerable <dynamic> > tcs = new TaskCompletionSource <IEnumerable <dynamic> >();
cancellationToken.Register(() => {
tcs.TrySetCanceled();
});
await Task.Run(() =>
{
try
{
tcs.TrySetResult(Query(stream, useHeaderRow, sheetName, excelType, startCell, configuration));
}
catch (Exception ex)
{
tcs.TrySetException(ex);
}
}, cancellationToken);
return(await tcs.Task);
}
/// <summary>
/// 返回Excel第一个工作表表名
/// </summary>
/// <param name="excelPath">Excel文件 绝对路径</param>
/// <param name="eType">Excel 版本 </param>
/// <returns>返回值</returns>
public static string GetExcelFirstTableName(string excelPath, ExcelType eType)
{
string connectstring = GetExcelConnectstring(excelPath, true, eType);
return GetExcelFirstTableName(connectstring);
}
public static async Task <DataTable> QueryAsDataTableAsync(this Stream stream, bool useHeaderRow = true, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null, CancellationToken cancellationToken = default(CancellationToken))
{
return(await Task.Run(() => QueryAsDataTable(stream, useHeaderRow, sheetName, excelType, startCell, configuration), cancellationToken).ConfigureAwait(false));
}
/// <summary>
/// This method is not recommended, because it'll load all data into memory.
/// </summary>
public static DataTable QueryAsDataTable(this Stream stream, bool useHeaderRow = true, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null)
{
var dt = new DataTable();
dt.TableName = sheetName;
var first = true;
var rows = ExcelReaderFactory.GetProvider(stream, GetExcelType(stream, excelType)).Query(useHeaderRow, sheetName, configuration);
foreach (IDictionary <string, object> row in rows)
{
if (first)
{
foreach (var key in row.Keys)
{
//TODO:base on cell t xml
//var type = row[key]?.GetType() ?? typeof(object);
var type = typeof(object);
dt.Columns.Add(key, type);
}
first = false;
}
dt.Rows.Add(row.Values.ToArray());
}
return(dt);
}
public Dictionary <Category, List <Player> > ReadPlayerList(string path, ExcelType extension)
{
Connect(path, extension);
OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [Sheet1$]", _connection);
DataSet excelDataSet = new DataSet();
objDA.Fill(excelDataSet);
Disconnect();
var result = excelDataSet.Tables[0];
var goldPlayers = new Dictionary <string, Player>();
var silverPlayers = new Dictionary <string, Player>();
var bronzeAPlayers = new Dictionary <string, Player>();
var bronzeBPlayers = new Dictionary <string, Player>();
foreach (DataRow row in result.Rows)
{
for (int i = 0; i < result.Columns.Count; i++)
{
var column = result.Columns[i];
var columnName = column.ToString();
var player = new Player();
var cellValue = row[result.Columns[i]].ToString();
if (cellValue != "")
{
switch (columnName)
{
case "Gold":
player.Category = Category.Gold;
player.FullName = cellValue;
player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString());
goldPlayers.Add(player.FullName, player);
break;
case "Silver":
player.Category = Category.Silver;
player.FullName = cellValue;
player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString());
silverPlayers.Add(player.FullName, player);
break;
case "Bronze A":
player.Category = Category.Bronze_A;
player.FullName = cellValue;
player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString());
bronzeAPlayers.Add(player.FullName, player);
break;
case "Bronze B":
player.Category = Category.Bronze_B;
player.FullName = cellValue;
player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString());
bronzeBPlayers.Add(player.FullName, player);
break;
}
}
}
}
var categories = new Dictionary <Category, List <Player> >
{
{ Category.Gold, goldPlayers.Values.ToList() },
{ Category.Silver, silverPlayers.Values.ToList() },
{ Category.Bronze_A, bronzeAPlayers.Values.ToList() },
{ Category.Bronze_B, bronzeBPlayers.Values.ToList() },
};
return(categories);
}
public static string GetExcelConnectstring(string excelPath, bool header, ExcelType eType)
{
return(GetExcelConnectstring(excelPath, header, eType, IMEXType.ImportMode));
}
public static void SaveAs(string path, object value, bool printHeader = true, string sheetName = "Sheet1", ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null)
{
if (Path.GetExtension(path).ToLowerInvariant() == ".xlsm")
{
throw new NotSupportedException("MiniExcel SaveAs not support xlsm");
}
using (FileStream stream = new FileStream(path, FileMode.CreateNew))
SaveAs(stream, value, printHeader, sheetName, GetExcelType(path, excelType), configuration);
}
internal static IExcelWriter GetProvider(Stream stream, object value, string sheetName, ExcelType excelType, IConfiguration configuration, bool printHeader)
{
if (string.IsNullOrEmpty(sheetName))
{
throw new InvalidDataException("Sheet name can not be empty or null");
}
if (excelType == ExcelType.UNKNOWN)
{
throw new InvalidDataException("Please specify excelType");
}
switch (excelType)
{
case ExcelType.CSV:
return(new CsvWriter(stream, value, configuration, printHeader));
case ExcelType.XLSX:
return(new ExcelOpenXmlSheetWriter(stream, value, sheetName, configuration, printHeader));
default:
throw new NotSupportedException($"Please Issue for me");
}
}
/// <summary>
/// This method is not recommended, because it'll load all data into memory.
/// Note: if first row cell is null value, column type will be string
/// </summary>
public static DataTable QueryAsDataTable(this Stream stream, bool useHeaderRow = true, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null)
{
if (sheetName == null)
{
sheetName = stream.GetSheetNames().First();
}
var dt = new DataTable(sheetName);
var first = true;
var rows = ExcelReaderFactory.GetProvider(stream, GetExcelType(stream, excelType)).Query(useHeaderRow, sheetName, startCell, configuration);
foreach (IDictionary <string, object> row in rows)
{
if (first)
{
foreach (var key in row.Keys)
{
var column = new DataColumn(key, typeof(object))
{
Caption = key
};
dt.Columns.Add(column);
}
dt.BeginLoadData();
first = false;
}
var newRow = dt.NewRow();
foreach (var key in row.Keys)
{
newRow[key] = row[key]; //TODO: optimize not using string key
}
dt.Rows.Add(newRow);
}
dt.EndLoadData();
return(dt);
}
public static void SaveAs(string path, object value, bool printHeader = true, string sheetName = "Sheet1", ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null)
{
using (FileStream stream = new FileStream(path, FileMode.CreateNew))
SaveAs(stream, value, printHeader, sheetName, GetExcelType(path, excelType), configuration);
}
public static ICollection <string> GetColumns(string path, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null)
{
using (var stream = Helpers.OpenSharedRead(path))
return(GetColumns(stream, useHeaderRow, sheetName, excelType, startCell, configuration));
}
public static IEnumerable <T> Query <T>(string path, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null) where T : class, new()
{
using (var stream = Helpers.OpenSharedRead(path))
foreach (var item in Query <T>(stream, sheetName, GetExcelType(path, excelType), configuration))
{
yield return(item);
} //Foreach yield return twice reason : https://stackoverflow.com/questions/66791982/ienumerable-extract-code-lazy-loading-show-stream-was-not-readable
}
public static ICollection <string> GetColumns(this Stream stream, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null)
{
return((Query(stream, useHeaderRow, sheetName, excelType, startCell, configuration).FirstOrDefault() as IDictionary <string, object>)?.Keys);
}
public static IEnumerable <dynamic> Query(string path, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null)
{
using (var stream = Helpers.OpenSharedRead(path))
foreach (var item in Query(stream, useHeaderRow, sheetName, GetExcelType(path, excelType), configuration))
{
yield return(item);
}
}
/// <summary>
/// EXCEL所有工作表导入DataSet
/// </summary>
/// <param name="excelPath">Excel文件 绝对路径</param>
/// <param name="header">是否把第一行作为列名</param>
/// <param name="eType">Excel 版本 </param>
/// <returns>返回Excel第一个工作表中的数据 DataSet </returns>
public static DataSet ExcelToDataSet(string excelPath, bool header, ExcelType eType)
{
string connectstring = GetExcelConnectstring(excelPath, header, eType);
return ExcelToDataSet(connectstring);
}
/// <summary>
/// 导出构造
/// </summary>
/// <param name="type"></param>
/// <param name="Format"></param>
internal Excel(ExcelType type, string Format = "yyyy-MM-dd")
{
EnumType = type;
DateFormat = Format;
}
/// <summary>
/// 获取Excel文件中指定工作表的列
/// </summary>
/// <param name="excelPath">Excel文件 绝对路径</param>
/// <param name="table">名称 excel table 例如：Sheet1$</param>
/// <returns></returns>
public static List<string> GetColumnsList(string excelPath, ExcelType eType, string table)
{
List<string> list = new List<string>();
DataTable tableColumns = null;
string connectstring = GetExcelConnectstring(excelPath, true, eType);
using (OleDbConnection conn = new OleDbConnection(connectstring))
{
conn.Open();
tableColumns = GetReaderSchema(table, conn);
}
foreach (DataRow dr in tableColumns.Rows)
{
string columnName = dr["ColumnName"].ToString();
string datatype = ((OleDbType)dr["ProviderType"]).ToString();//对应数据库类型
string netType = dr["DataType"].ToString();//对应的.NET类型，如System.String
list.Add(columnName);
}
return list;
}
/// <summary>
/// EXCEL所有工作表导入DataSet
/// </summary>
/// <param name="excelPath">Excel文件 绝对路径</param>
/// <param name="header">是否把第一行作为列名</param>
/// <param name="eType">Excel 版本 </param>
/// <returns>返回Excel第一个工作表中的数据 DataSet </returns>
public static DataSet ExcelToDataSet(string excelPath, bool header, ExcelType eType)
{
string connectstring = GetExcelConnectstring(excelPath, header, eType);
return(ExcelToDataSet(connectstring));
}
/// <summary>
/// This method is not recommended, because it'll load all data into memory.
/// </summary>
public static DataTable QueryAsDataTable(string path, bool useHeaderRow = true, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null)
{
using (var stream = Helpers.OpenSharedRead(path))
return(QueryAsDataTable(stream, useHeaderRow, sheetName, GetExcelType(path, excelType), configuration));
}
/// <summary>
/// 获取Excel文件中指定工作表的列
/// </summary>
/// <param name="excelPath">Excel文件 绝对路径</param>
/// <param name="eType">ExcelType</param>
/// <param name="table">名称 excel table 例如：Sheet1$</param>
/// <returns>返回值</returns>
public static List<string> GetColumnsList(string excelPath, ExcelType eType, string table)
{
DataTable tableColumns;
string connectstring = GetExcelConnectstring(excelPath, true, eType);
using (var conn = new OleDbConnection(connectstring))
{
conn.Open();
tableColumns = GetReaderSchema(table, conn);
}
return (from DataRow dr in tableColumns.Rows let columnName = dr["ColumnName"].ToString() let datatype = ((OleDbType) dr["ProviderType"]).ToString() let netType = dr["DataType"].ToString() select columnName).ToList();
}
internal static void GetExcelInputsAndOutputs(string xlFilename, Action <string, string, ExcelType, List <int> > addOutput, Action <string, string, string, ExcelType, List <int> > addInput, Action done)
{
int REGDB_E_CLASSNOTREG = unchecked ((int)0x80040154);
Excel.Application excelApp;
try
{
var excelType = Type.GetTypeFromProgID("Excel.Application");
if (excelType == null)
{
throw new COMException("No type Excel.Application", REGDB_E_CLASSNOTREG);
}
excelApp = (Excel.Application)Activator.CreateInstance(excelType);
}
catch (COMException e)
{
if (e.ErrorCode == REGDB_E_CLASSNOTREG)
{
throw new ApplicationException("Excel is not installed");
}
else
{
throw;
}
}
// Create a copy of the file, since excel opens with ShareMode: None and we may want to run in parallel
string xlFilenameCopy = Path.Combine(Path.GetDirectoryName(xlFilename), Path.GetFileNameWithoutExtension(xlFilename) + "_" + GetCurrentThreadId().ToString() +
"_" + Process.GetCurrentProcess().Id + Path.GetExtension(xlFilename));
try
{
System.IO.File.Copy(xlFilename, xlFilenameCopy, true);
}
catch (IOException e)
{
throw new ApplicationException(String.Format("Could not copy \"{0}\": {1}", xlFilename, e.Message), e);
}
try
{
// n.b. Excel needs an absolute path
using (var workbooks = excelApp.Workbooks.WithComCleanup())
using (var workbook = workbooks.Resource.Open(xlFilenameCopy).WithComCleanup())
{
// for (var workbook in excelApp.Workbooks
foreach (Excel.Name name in ((IEnumerable)workbook.Resource.Names).ComLinq <object>())
{
string nameName = name.Name;
dynamic rt = null;
dynamic range = null;
dynamic val = null;
dynamic formula = null;
try
{
rt = name.RefersToLocal;
// FIXME name.RefersToLocal may be c:\path\asdf\[proj.xlsx]Sheet!A2
// skip Hidden names
if (name.Visible == false || !(rt is string))
{
continue;
}
try
{
range = name.RefersToRange;
}
catch
{
try
{
range = excelApp.Range[rt];
}
catch
{
continue;
}
}
val = range.Value;
ExcelType type = ExcelType.Float;
List <int> dims = new List <int>();
bool output = false;
if (val is Array)
{
type = ExcelType.FloatArray;
foreach (Object o in val)
{
if (excelApp.WorksheetFunction.IsText(o))
{
type = ExcelType.StrArray;
break;
}
}
// TODO if (val == true || val == false)
foreach (Object o in range.Formula)
{
if (o is string && ((string)o).StartsWith("="))
{
output = true;
break;
}
}
dims.Add(val.GetLength(1));
dims.Add(val.GetLength(0));
}
else
{
type = ExcelType.Float;
if (excelApp.WorksheetFunction.IsText(val))
{
type = ExcelType.Str;
}
// TODO if (val == true || val == false)
formula = range.Formula;
if (formula is string && ((string)formula).StartsWith("="))
{
output = true;
}
}
if (output)
{
addOutput(nameName, rt, type, dims);
}
else
{
addInput(nameName, rt, val.ToString(), type, dims);
}
}
finally
{
ComCleanupExtensions.ReleaseComObject((object)rt);
ComCleanupExtensions.ReleaseComObject((object)range);
ComCleanupExtensions.ReleaseComObject((object)val);
ComCleanupExtensions.ReleaseComObject((object)formula);
}
}
done();
}
}
finally
{
foreach (Excel.Workbook workbook in ((IEnumerable)excelApp.Workbooks).ComLinq <object>())
{
workbook.Close(false);
}
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
File.Delete(xlFilenameCopy);
}
}
/// <summary>
/// 返回Excel 连接字符串 [IMEX=1]
/// </summary>
/// <param name="excelPath">Excel文件 绝对路径</param>
/// <param name="header">是否把第一行作为列名</param>
/// <param name="eType">Excel 版本 </param>
/// <returns>返回值</returns>
public static string GetExcelConnectstring(string excelPath, bool header, ExcelType eType)
{
return GetExcelConnectstring(excelPath, header, eType, IMEXType.ImportMode);
}
/// <summary>
/// 將DataTable中的數據寫入到指定的Excel文件中
/// </summary>
/// <param name="dt">要寫入Excel文檔中的數據</param>
/// <param name="filePath">Excel文檔的完整路徑，即需要包含文件名及後綴名，後綴名只能是.xls或.xlsx</param>
/// <param name="type">選擇要寫入的Excel文件格式</param>
public static void DataTableToExcel(DataTable dt, string filePath, ExcelType type)
{
IWorkbook workbook = CreateWorkbook(type);
DataTableToSheet(ref workbook, dt);
using (FileStream fs = File.Create(filePath))
{
workbook.Write(fs);//如果文件存在，會直接覆蓋。
}
}
/// <summary>
/// 返回Excel工作表名
/// </summary>
/// <param name="excelPath">Excel文件 绝对路径</param>
/// <param name="eType">Excel 版本 </param>
/// <returns>返回值</returns>
public static List<string> GetExcelTablesName(string excelPath, ExcelType eType)
{
string connectstring = GetExcelConnectstring(excelPath, true, eType);
return GetExcelTablesName(connectstring);
}
/// <summary>
/// 根據傳入的Excel類型標記，創建並返回對應的IWorkbook對象
/// </summary>
/// <param name="type">Excel類型標記</param>
/// <returns>如果標記是Excel2003則返回HSSFWorkbook對象，如果標記是Excel2007則返回XSSFWorkbook對象</returns>
private static IWorkbook CreateWorkbook(ExcelType type)
{
if (type == ExcelType.Excel2003)
return new HSSFWorkbook();
else
return new XSSFWorkbook();
}
