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 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 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) { }