private void buttonSelectExcelOld_Click(object sender, EventArgs e)
{
dataGridViewOldFile.DataSource = ExcelOptions.SelectExcelFile();
dataGridViewOldFile.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
for (int i = 0; i < dataGridViewOldFile.ColumnCount; i++)
{
if (dataGridViewOldFile.Columns[i].HeaderText.Contains("قوم")) // الرقم القومي
{
for (int k = 0; k < dataGridViewOldFile.RowCount; k++)
{
try {
long ID = long.Parse(dataGridViewOldFile[i, k].Value.ToString());
oldNationalId.Add(ID, 1);
}
catch {
buttonSelectExcelNew.Enabled = true;
buttonSelectExcelOld.Enabled = false;
return;
}
}
buttonSelectExcelNew.Enabled = true;
buttonSelectExcelOld.Enabled = false;
return;
}
}
}
public static IDbConnection GetOpenDbConnection(string filePath, ExcelOptions options)
{
// Without the "IMEX=1" Excel returns NULL for all column values except the first column.
OleDbConnection conn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source={0};Extended Properties=\"Excel 8.0; HDR={1}; {2}\";", filePath,
(options & ExcelOptions.HeaderRow) == ExcelOptions.HeaderRow ? "Yes" : "No",
(options & ExcelOptions.Writeable) == ExcelOptions.Writeable ? "" : "IMEX=1"));
try
{
conn.Open();
}
catch (InvalidOperationException ex)
{
if (ex.Message.IndexOf("provider is not registered") != -1)
{
throw new ApplicationException("This operation cannot be run in x64 mode."
+ " Run the process as 32-bit (use CorFlags.exe or a 32-bit machine).", ex);
}
throw;
}
return(conn);
}
private void buttonSelectExcelNew_Click(object sender, EventArgs e)
{
dataGridViewNewFile.DataSource = ExcelOptions.SelectExcelFile();
dataGridViewNewFile.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
for (int i = 0; i < dataGridViewNewFile.ColumnCount; i++)
{
if (dataGridViewNewFile.Columns[i].HeaderText.Contains("قوم")) // الرقم القومي
{
for (int k = 0; k < dataGridViewNewFile.RowCount; k++)
{
try {
long ID = long.Parse(dataGridViewNewFile[i, k].Value.ToString());
if (!oldNationalId.Contains(new KeyValuePair <long, byte>(ID, 1)))
{
oldNationalId.Add(ID, 3);
}
else
{
oldNationalId[ID] = 2; // to make remove true in the dictionary
}
}
catch {
buttonSelectExcelNew.Enabled = false;
//return;
}
}
buttonSelectExcelNew.Enabled = false;
return;
}
}
}
private static HSSFWorkbook createWorkbook(DataTable dt, ExcelOptions options)
{
if (options == null)
{
options = new ExcelOptions();
}
var book = new HSSFWorkbook();
var ds = dt.Slice(options.RowsCountLimit);
var index = 0;
foreach (DataTable t in ds.Tables)
{
var startNumber = options.RowsCountLimit * index + 1;
var sheetName = (string.IsNullOrEmpty(options.SheetName) || options.SheetName == "Sheet1") ? "Sheet" : options.SheetName;
sheetName = $"{sheetName}{ ++index }";
createSheet(ref book,
t,
options.Title,
options.Conditions,
sheetName,
options.HasTime,
options.Headers,
options.ColumnNameMappings,
options.HasNumber,
startNumber,
options.HeaderLeft,
options.HeaderCenter,
options.HeaderRight,
options.FooterLeft,
options.FooterCenter,
options.FooterRight);
}
return(book);
}
public Stream ExportExcel(DataTable dt, ExcelOptions options)
{
if (options == null)
{
options = new ExcelOptions();
}
var ds = dt.Slice(options.RowsCountLimit);
return(ExportExcel(ds, options));
}
/// <summary>
/// 将Excel文件流导入到List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fileName"></param>
/// <param name="options"></param>
/// <returns></returns>
public List <T> ImportExcel <T>(string fileName, ExcelOptions options) where T : new()
{
if (string.IsNullOrEmpty(fileName) || !File.Exists(fileName))
{
return(null);
}
//根据指定路径读取文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
//实例化T数组
List <T> list = new List <T>();
//获取数据
list = ImportExcel <T>(fs, options);
return(list);
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelOutputWriter"/> class.
/// </summary>
/// <param name="outFileName">Name of the out file. If null no excel file will be created</param>
/// <param name="options">Excel startup options</param>
public ExcelOutputWriter(string outFileName, ExcelOptions options)
{
using (Tracer t = new Tracer(myType, "ExcelOutputWriter"))
{
using (new USCulture())
{
mybCloseOnDispose = ((options & ExcelOptions.CloseOnExit) == ExcelOptions.CloseOnExit);
myExcelFileName = outFileName;
myExcel.DisplayAlerts = false;
myExcel.Visible = ((options & ExcelOptions.Visible) == ExcelOptions.Visible);
myWorkbook = myExcel.Workbooks.Add(1);
t.Info("CloseOnDispose: {0}, OutFileName: {1}, Visible: {2}", mybCloseOnDispose, outFileName, myExcel.Visible);
myWriter = new AsyncWriter <List <string> >(WriteLine);
}
}
}
public void ShouldReadExcelFile()
{
var mapper = ExcelTypeMapper.Define <Lead>();
mapper.Property(l => l.BusinessName).ColumnName("BusinessName");
mapper.Property(l => l.TotalEmployees).ColumnName("TotalEmployees");
mapper.Property(l => l.SalesVolume).ColumnName("SalesVolume");
mapper.Property(l => l.NextAdvertisingDate).ColumnName("NextAdvertisingDate");
string fileName = @"TestFiles\test.xlsx";
ExcelOptions options = new ExcelOptions("Leads");
options.IsFirstRecordSchema = false;
options.StartingRow = 3;
options.StartingColumn = "B";
var leads = mapper.Read(fileName, options);
}
/// <summary>
/// 将DataTable分割后的DataSet导出一个多sheet的Excel文件流
/// </summary>
/// <param name="ds"></param>
/// <param name="options">ExcelOptions.RowsCountList此时失效,默认dataset的每个datatable的count小于RowsCountList</param>
/// <returns></returns>
public Stream ExportExcel(DataSet ds, ExcelOptions options)
{
var book = new HSSFWorkbook();
var index = 0;
foreach (DataTable dt in ds.Tables)
{
var startNumber = options.RowsCountLimit * index + 1;
var sheetName = (string.IsNullOrEmpty(options.SheetName) || options.SheetName == "Sheet1") ? "Sheet" : options.SheetName;
sheetName = $"{sheetName}{ ++index }";
createSheet(ref book,
dt,
options.Title,
options.Conditions,
sheetName,
options.HasTime,
options.Headers,
options.ColumnNameMappings,
options.HasNumber,
startNumber,
options.HeaderLeft,
options.HeaderCenter,
options.HeaderRight,
options.FooterLeft,
options.FooterCenter,
options.FooterRight);
}
Stream stream;
if (string.IsNullOrEmpty(options.SaveFileName))
{
stream = new MemoryStream();
}
else
{
stream = new FileStream(options.SaveFileName, FileMode.Create, FileAccess.ReadWrite);
}
book.Write(stream);
stream.Position = 0;
return(stream);
}
private void buttonSelectFile_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = ExcelOptions.SelectExcelFile();
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
columnsIndex[dataGridView1.Columns[i].HeaderText] = i;
comboCoulms.Items.Add(dataGridView1.Columns[i].HeaderText);
listView1.Items.Add(dataGridView1.Columns[i].HeaderText, "Data/boy.jpg");
}
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
dataGridView1.Columns[i].Visible = false;
}
for (int i = 0; i < dataGridView1.RowCount; i++)
{
if ((i & 1) == 1)
{
dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.AliceBlue;
}
}
}
public IServiceCollection Config(IServiceCollection services, ExcelOptions options)
{
services.AddSingleton <IExcelExportHandler, NPOIExcelExportHandler>();
return(services);
}
public void ShouldNotAllowLowercaseCharactersStartingColumn()
{
var options = new ExcelOptions("Sheet1");
options.StartingColumn = "a";
}
public EPPlusExcelHandler(ExcelOptions options, SystemConfigModel systemConfig, ILoginInfo loginInfo, IExcelExportHandler exportHandler) : base(options, systemConfig, loginInfo, exportHandler)
{
}
public void ShouldNotAllowNonCharactersStartingColumn()
{
var options = new ExcelOptions("Sheet1");
options.StartingColumn = "W1";
}
public void ShouldNotAllowWhitespaceCharactersStartingColumn()
{
var options = new ExcelOptions("Sheet1");
options.StartingColumn = " ";
}
public void ShouldAllowNullEndingRow()
{
var options = new ExcelOptions("Sheet1");
options.EndingRow = null;
}
public void ShouldNotAllowBlankStartingColumn()
{
var options = new ExcelOptions("Sheet1");
options.StartingColumn = String.Empty;
}
public void ShouldNotAllowWhitespaceCharactersStartingColumn()
{
var options = new ExcelOptions("Sheet1");
options.StartingColumn = " ";
}
public void ShouldNotAllowNegativeEndingRow()
{
var options = new ExcelOptions("Sheet1");
options.EndingRow = -1;
}
public void ShouldAllowNullStartingRow()
{
var options = new ExcelOptions("Sheet1");
options.StartingRow = null;
}
/// <summary>
/// 将Excel文件流导入到List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fileStream"></param>
/// <param name="options"></param>
/// <returns></returns>
public List <T> ImportExcel <T>(Stream fileStream, ExcelOptions options) where T : new()
{
if (options == null)
{
options = new ExcelOptions();
}
if (options.StartRowIndex < 0)
{
options.StartRowIndex = 0;
}
if (options.StartColumnIndex < 0)
{
options.StartColumnIndex = 0;
}
//创建Excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fileStream);
//如果有指定工作表名称
ISheet sheet = null;
if (!string.IsNullOrEmpty(options.SheetName))
{
sheet = workbook.GetSheet(options.SheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
//实例化T数组
List <T> list = new List <T>();
if (sheet != null)
{
//一行最后一个cell的编号 即总的列数
IRow cellNum = sheet.GetRow(0);
int num = cellNum.LastCellNum;
//获取泛型对象T的所有属性
var propertys = typeof(T).GetProperties();
//每行转换为单个T对象
for (int i = options.StartRowIndex; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
var obj = new T();
var pi = 0;
for (int j = options.StartColumnIndex; j < num; j++)
{
//没有数据的单元格都默认是null
ICell cell = row.GetCell(j);
if (cell != null)
{
var value = row.GetCell(j).ToString();
string str = (propertys[pi].PropertyType).FullName;
if (str == "System.String")
{
propertys[pi].SetValue(obj, value, null);
}
else if (str == "System.DateTime")
{
DateTime pdt = Convert.ToDateTime(value);
propertys[pi].SetValue(obj, pdt, null);
}
else if (str == "System.Boolean")
{
bool pb = Convert.ToBoolean(value);
propertys[pi].SetValue(obj, pb, null);
}
else if (str == "System.Int16")
{
short pi16 = Convert.ToInt16(value);
propertys[pi].SetValue(obj, pi16, null);
}
else if (str == "System.Int32")
{
int pi32 = Convert.ToInt32(value);
propertys[pi].SetValue(obj, pi32, null);
}
else if (str == "System.Int64")
{
long pi64 = Convert.ToInt64(value);
propertys[pi].SetValue(obj, pi64, null);
}
else if (str == "System.Byte")
{
byte pb = Convert.ToByte(value);
propertys[pi].SetValue(obj, pb, null);
}
else if (str == "System.Decimal")
{
var d = Convert.ToDecimal(value);
propertys[pi].SetValue(obj, d, null);
}
else
{
propertys[pi].SetValue(obj, null, null);
}
}
pi++;
}
list.Add(obj);
}
}
return(list);
}
public void ShouldNotAllowNegativeStartingRow()
{
var options = new ExcelOptions("Sheet1");
options.StartingRow = -1;
}
public void ShouldNotAllowLowercaseCharactersStartingColumn()
{
var options = new ExcelOptions("Sheet1");
options.StartingColumn = "a";
}
public void ShouldNotAllowBlankStartingColumn()
{
var options = new ExcelOptions("Sheet1");
options.StartingColumn = String.Empty;
}
public void ShouldNotAllowNonCharactersStartingColumn()
{
var options = new ExcelOptions("Sheet1");
options.StartingColumn = "W1";
}
public Stream ExportExcel <T>(List <T> list, ExcelOptions options) where T : new()
{
var dt = list.ToDataTable();
return(ExportExcel(dt, options));
}
public NPOIExcelExportHandler(ExcelOptions options)
{
_options = options;
}
public TestExcelContext(ExcelOptions options) : base(options)
{
}
