public bool FileToEntity(HttpPostedFile file, string modelType) { bool isSuccess = true; if (string.Empty.Equals(file.FileName) || ".xlsx" != Path.GetExtension(file.FileName)) { isSuccess = false; throw new ArgumentException("当前文件格式不正确,请确保正确的Excel文件格式!"); } var severPath = this.Server.MapPath("/files/"); //获取当前虚拟文件路径 var savePath = Path.Combine(severPath, file.FileName); //拼接保存文件路径 try { file.SaveAs(savePath); if (string.Equals(modelType, "app")) { appEntityList = ExcelExtension.LoadFromExcel <RepairApplication>(savePath).ToList(); } if (string.Equals(modelType, "dev")) { devEntityList = ExcelExtension.LoadFromExcel <Device>(savePath).ToList(); } } catch { isSuccess = false; } finally { System.IO.File.Delete(savePath);//每次上传完毕删除文件 } return(isSuccess); }
public void Execute() { var workbook = ExcelExtension.LoadExcel(Xlsx); var sheet = workbook.GetSheetAt(0); for (var i = BeginRow - 1; i < EndRow; i++) { var row = sheet.GetRow(i); var xzqh = row.Cell(XzqhCol).Value(); if (!string.IsNullOrEmpty(row.Cell(DwmcCol).Value())) { continue; } var dwmc = ""; Match match = null; foreach (var regex in regex) { match = Regex.Match(xzqh, regex); if (match.Success) { dwmc = match.Groups[1].Value; break; } } WriteLine($"{i} {xzqh} => {dwmc}"); row.Cell(DwmcCol).SetValue(dwmc); } workbook.Save(Util.StringEx.AppendToFileName(Xlsx, ".upd")); }
static string ConnectionString(string fileName, bool hasHeaders, ExcelExtension excelExtension) { string HDR = hasHeaders ? "Yes" : "No"; fileName = string.Format("{0}.{1}", fileName, excelExtension); return excelExtension == ExcelExtension.XLSX ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"" : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\""; }
static string ConnectionString(string fileName, bool hasHeaders, ExcelExtension excelExtension) { string HDR = hasHeaders ? "Yes" : "No"; fileName = string.Format("{0}.{1}", fileName, excelExtension); return(excelExtension == ExcelExtension.XLSX ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"" : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\""); }
public void Open(string filePath, Project project) { PartsManagerment = new MDPartsManagement(); PartsDataBase = PartsManagerment.OpenDatabase(); FilePath = filePath; CurrentProject = project; ExcelExtension = new ExcelExtension(FilePath, true); PartSheets = ExcelExtension.ExcelPackage.Workbook.Worksheets; }
public void Execute() { using var db = new Context(); var saveXlsx = $@"D:\参保管理\参保全覆盖\雨湖区未参保落实台账{Util.DateTime.FormatedDate()}.xlsx"; if (!string.IsNullOrEmpty(FileName)) { saveXlsx = $@"D:\参保管理\参保全覆盖\{FileName}"; } var sql = "SELECT * FROM fc_yxfsj "; if (!string.IsNullOrEmpty(Where)) { sql += $" where {Where} "; } sql += " ORDER BY CONVERT(dwmc USING gbk), " + "FIELD(SUBSTRING(xfpc,2,1),'一','二','三','四','五','六','七','八','九'), no"; // WriteLine($"{sql}, ${saveXlsx}"); return; IQueryable <Yxfsj> data = db.Yxfsjs.FromSqlRaw(sql); WriteLine($"开始导出未参保落实台账: =>{saveXlsx}"); var workbook = ExcelExtension.LoadExcel(tmplXlsx); var sheet = workbook.GetSheetAt(0); int startRow = 2, currentRow = 2; foreach (var d in data) { var index = currentRow - startRow + 1; WriteLine($"{index} {d.Idcard} {d.Name}"); var row = sheet.GetOrCopyRow(currentRow++, startRow); row.Cell("A").SetValue(index); row.Cell("B").SetValue(d.Dwmc); row.Cell("C").SetValue(d.Xfpc); row.Cell("D").SetValue(d.No); row.Cell("E").SetValue(d.Name); row.Cell("F").SetValue(d.Idcard); row.Cell("G").SetValue(d.Tcq); row.Cell("H").SetValue(d.Xzj); row.Cell("I").SetValue(d.Csq); row.Cell("J").SetValue(d.Sfycb); row.Cell("K").SetValue(d.Cbsj); row.Cell("L").SetValue(d.Wcbyy); } workbook.Save(saveXlsx); WriteLine($"结束导出未参保落实台账: =>{saveXlsx}"); }
public static ExcelData Create(IEnumerable <ExcelInputData> datas, ExcelExtension extension) { if (datas == null) { return(null); } using (var fs = new MemoryStream()) { IWorkbook workbook = new XSSFWorkbook(); int sheetIndex = 1; foreach (var data in datas) { if (data == null || data.Header == null || data.Contents == null) { continue; } ISheet sheet = workbook.CreateSheet(data.SheetName ?? $"Sheet{sheetIndex}"); var rowIndex = 0; //Create header var header = sheet.CreateRow(rowIndex); for (int i = 0; i < data.Header.Length; i++) { header.CreateCell(i).SetCellValue(data.Header[i]); sheet.AutoSizeColumn(i); } rowIndex++; //Create Content foreach (var content in data.Contents) { if (content != null) { var row = sheet.CreateRow(rowIndex); for (int i = 0; i < content.Length; i++) { row.CreateCell(i).SetCellValue(content[i]); } rowIndex++; } } sheetIndex++; } workbook.Write(fs); return(new ExcelData() { Data = fs.GetBuffer(), ContentType = GetContentType(extension), Extension = extension }); } }
public void CreateWorkbook() { var wb = ExcelExtension.CreateWorkbook(); Assert.IsType <HSSFWorkbook>(wb); wb = ExcelExtension.CreateWorkbook(WorkbookStyle.HSSFWorkbook); Assert.IsType <HSSFWorkbook>(wb); wb = ExcelExtension.CreateWorkbook(WorkbookStyle.XSSFWorkbook); Assert.IsType <XSSFWorkbook>(wb); }
public static void ImportExcelXLS(this DataSet output, string[] fileNames, ExcelExtension excelExtension = ExcelExtension.XLS, bool hasHeaders = true) { foreach (var fileName in fileNames) { var strConn = ConnectionString(fileName, hasHeaders, excelExtension); using (var conn = new OleDbConnection(strConn)) { conn.Open(); DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); ImportExcelXLS(output, fileName, schemaTable, conn); } } }
private static string GetContentType(ExcelExtension extension) { switch (extension) { case ExcelExtension.xlsx: return("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); default: return(string.Empty); } }
public static void TestExcel() { var workbook = ExcelExtension.LoadExcel(@"D:\精准扶贫\雨湖区精准扶贫底册模板.xlsx"); var sheet = workbook.GetSheetAt(0); var row = sheet.Row(2); row.Cell("U").SetValue("属于贫困人员"); row.Cell("V").SetValue("认定身份"); workbook.Save($@"D:\精准扶贫\雨湖区精准扶贫底册模板{DateTime.FormatedDate()}.xlsx"); }
public void Execute() { var workbook = ExcelExtension.LoadExcel(SourceExcel); var sheet = workbook.GetSheetAt(0); WriteLine("生成分组映射表"); var map = new Dictionary <string, List <int> >(); for (var index = BeginRow - 1; index < EndRow; index++) { var xzj = sheet.Row(index).Cell(DistCol).Value(); if (Regex.IsMatch(xzj, DistPattern)) { if (!map.ContainsKey(xzj)) { map[xzj] = new List <int>(); } map[xzj].Add(index); } } WriteLine("生成分组数据表"); foreach (var xzj in map.Keys) { var count = map[xzj].Count; WriteLine($"{xzj}: {count}"); if (count <= 0) { continue; } var outWorkbook = ExcelExtension.LoadExcel(TemplateExcel); var outSheet = outWorkbook.GetSheetAt(0); int startRow = 1, currentRow = startRow; (int Begin, int End)cols = (1, 7); map[xzj].ForEach(rowIndex => { var index = currentRow - startRow + 1; var inRow = sheet.Row(rowIndex); var outRow = outSheet.GetOrCopyRow(currentRow++, startRow); outRow.Cell("A").SetValue(index); for (var col = cols.Begin; col <= cols.End; col++) { outRow.Cell(col).SetValue(inRow.Cell(col).Value()); } }); outWorkbook.Save( Path.Join(OutDir, $"{xzj}{map[xzj].Count}.xls")); //break; } }
/// <summary>将Excel表格导入数据库</summary> /// /// <param name="startRow">开始行(从1开始)</param> /// <param name="endRow">结束行(包含)</param> /// public static int LoadExcel<T>( this DbContext context, string fileName, int startRow, int endRow, List<string> fields, List<string> noQuotes = null, bool printSql = false, string ident = "", int tableIndex = 0) where T : class { var workbook = ExcelExtension.LoadExcel(fileName); var sheet = workbook.GetSheetAt(tableIndex); var regex = new Regex("^[A-Z]+$", RegexOptions.IgnoreCase); var builder = new StringBuilder(); for (var index = startRow - 1; index < endRow; index++) { try { var values = new List<string>(); foreach (var row in fields) { string value = row; if (regex.IsMatch(row)) { value = sheet.Row(index).Cell(row).Value(); if (noQuotes == null || !noQuotes.Contains(row)) value = $"'{value}'"; } values.Add(value); } builder.Append(string.Join(',', values)); builder.Append("\n"); } catch (Exception ex) { throw new Exception($"LoadExcel error at row {index + 1}", ex); } } var tmpFileName = Path.GetTempFileName(); File.AppendAllText(tmpFileName, builder.ToString()); var cvsFileName = new Uri(tmpFileName).AbsolutePath; var tableName = context.GetTableName<T>(); var sql = $@"load data infile '{cvsFileName}' into table `{tableName}` " + @"CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY " + @"ENCLOSED BY '\'' LINES TERMINATED BY '\n';"; var result = context.ExecuteSql(sql, printSql, ident); if (File.Exists(tmpFileName)) File.Delete(tmpFileName); return result; }
public static void ImportExcelXLS(this DataSet output, string[] fileNames, ExcelExtension excelExtension = ExcelExtension.XLS, string path = "", bool hasHeaders = true) { foreach (var fileName in fileNames) { var name = string.IsNullOrEmpty(path)?fileName: Path.Combine(path, fileName); var strConn = ConnectionString(name, hasHeaders, excelExtension); using (var conn = new OleDbConnection(strConn)) { conn.Open(); DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); ImportExcelXLS(output, fileName, schemaTable, conn); } } }
public void Execute() { var workbook = ExcelExtension.LoadExcel(CertExcel); var sheet = workbook.GetSheetAt(0); var map = Program.GenerateGroupData(sheet, BeginRow, EndRow); if (Directory.Exists(OutputDir)) { Directory.Move(OutputDir, OutputDir + ".orig"); } Directory.CreateDirectory(OutputDir); var total = 0; foreach (var(xzj, group) in map) { WriteLine($"{(xzj+":").FillRight(11)} {group.Total}"); total += group.Total; Directory.CreateDirectory(Path.Join(OutputDir, xzj)); foreach (var(csq, list) in group.Data) { WriteLine($" {(csq+":").FillRight(11)} {list.Count}"); var outWorkbook = ExcelExtension.LoadExcel(Program.CertTemplate); var outSheet = outWorkbook.GetSheetAt(0); int startRow = 4, currentRow = 4; list.ForEach(rowIndex => { var index = currentRow - startRow + 1; var inRow = sheet.Row(rowIndex); var row = outSheet.GetOrCopyRow(currentRow++, startRow); row.Cell("A").SetValue(index); row.Cell("B").SetValue(inRow.Cell("C").Value()); row.Cell("C").SetValue((inRow.Cell("E").Value() == "1") ? "男" : "女"); row.Cell("D").SetValue(inRow.Cell("D").Value()); row.Cell("E").SetValue(inRow.Cell("A").Value()); row.Cell("M").SetValue(inRow.Cell("I").Value()); }); outWorkbook.Save( Path.Join(OutputDir, xzj, $"{csq}.xls")); } } WriteLine($"{"合计:".FillRight(11)} {total}"); }
public void GetHeaders(IExcelDataReader reader) { foreach (int headerIndex in this.ModelColumnsNumber) { int excelIndex = headerIndex + 1; this.Headers.Add(new Header { Column = excelIndex, Letter = ExcelExtension.ColumnIndexToColumnLetter(excelIndex), Name = Convert.ToString(reader.GetValue(headerIndex)), Index = headerIndex }); } }
public IList<Property> Parse(Stream templateStream, ExcelExtension extension) { IWorkbook wb = null; if (extension == ExcelExtension.XLS) { wb = new HSSFWorkbook(templateStream); } else if (extension == ExcelExtension.XLSX) { wb = new XSSFWorkbook(templateStream); } ISheet sheet = wb.GetSheetAt(0); return ParseSheet(sheet); }
public void Execute() { using var db = new Context(); WriteLine("开始更新落实总台账"); var workbook = ExcelExtension.LoadExcel(Xlsx); var sheet = workbook.GetSheetAt(0); for (var index = BeginRow - 1; index < EndRow; index++) { var row = sheet.Row(index); var no = row.Cell("A").Value(); var book = new Books { Dwmc = row.Cell("B").Value(), Name = row.Cell("C").Value(), Idcard = row.Cell("D").Value(), Address = row.Cell("E").Value(), Hsqk = row.Cell("F").Value(), }; WriteLine($"{no} {book.Idcard} {book.Name.FillRight(6)}"); var fcbook = from b in db.Books where b.Idcard == book.Idcard select b; if (fcbook.Any()) { foreach (var fb in fcbook) { db.Entry(fb).CurrentValues.SetValues(book); } } else { db.Add(book); } db.SaveChanges(); } WriteLine("结束更新落实总台账"); }
protected override IWorkbook CreateExportSheet(int rowCount, ExcelExtension ext) { return(this.UIDispatcher.Invoke(new Func <IWorkbook>(() => { IWorkbook workbook = ext == ExcelExtension.XLS ? (IWorkbook) new HSSFWorkbook() : new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(this.Header); var firstRow = sheet.CreateRow(0); firstRow.CreateCell(0, CellType.String).SetCellValue("名称"); firstRow.CreateCell(1, CellType.String).SetCellValue("适用于"); firstRow.CreateCell(2, CellType.String).SetCellValue("不良反应"); firstRow.CreateCell(3, CellType.String).SetCellValue("制作人"); firstRow.CreateCell(4, CellType.String).SetCellValue("饲料名称"); firstRow.CreateCell(5, CellType.String).SetCellValue("饲料产地"); firstRow.CreateCell(6, CellType.String).SetCellValue("饲料类型"); firstRow.CreateCell(7, CellType.String).SetCellValue("用量"); for (int i = 0; i < this.FormulaDetails.Count(); i++) { var ff = this.FormulaDetails[i]; var row = sheet.CreateRow(i + 1); row.CreateCell(4, CellType.String).SetCellValue(ff.Name); row.CreateCell(5, CellType.String).SetCellValue(ff.Area); row.CreateCell(6, CellType.String).SetCellValue(ff.Type); row.CreateCell(7, CellType.Numeric).SetCellValue(ff.Amount); } Formula formula = (this.Table.SelectedItem as Formula); var srow = sheet.GetRow(1); ICellStyle style = workbook.CreateCellStyle(); style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; srow.CreateCell(0, CellType.String).SetCellValue(formula.Name); srow.CreateCell(1, CellType.String).SetCellValue(formula.ApplyTo); srow.CreateCell(2, CellType.String).SetCellValue(formula.SideEffect); srow.CreateCell(3, CellType.String).SetCellValue(formula.PrincipalName); srow.Cells.ForEach(c => c.CellStyle = style); sheet.AddMergedRegion(new CellRangeAddress(1, this.FormulaDetails.Count(), 0, 0)); sheet.AddMergedRegion(new CellRangeAddress(1, this.FormulaDetails.Count(), 1, 1)); sheet.AddMergedRegion(new CellRangeAddress(1, this.FormulaDetails.Count(), 2, 2)); sheet.AddMergedRegion(new CellRangeAddress(1, this.FormulaDetails.Count(), 3, 3)); return workbook; }), null) as IWorkbook); }
protected override IEnumerable <FpRawData> Fetch() { var workbook = ExcelExtension.LoadExcel(Xlsx); var sheet = workbook.GetSheetAt(0); for (var index = BeginRow - 1; index < EndRow; index++) { var row = sheet.Row(index); if (row != null) { var name = row.Cell("C").Value(); var idcard = row.Cell("D").Value(); idcard = idcard.Trim(); if (idcard.Length < 18) { continue; } if (idcard.Length > 18) { idcard = idcard.Substring(0, 18).ToUpper(); } var birthDay = idcard.Substring(6, 8); var xzj = row.Cell("A").Value(); var csq = row.Cell("B").Value(); yield return(new FpRawData { Name = name, Idcard = idcard, BirthDay = birthDay, Xzj = xzj, Csq = csq, Type = "特困人员", Detail = "是", Date = Date }); } } }
public int LoadExcel( string tableName, string excelFile, int startRow, int endRow, string[] fields, HashSet <string> noQuote = null) { var workbook = ExcelExtension.LoadExcel(excelFile); var sheet = workbook.GetSheetAt(0); var buf = new StringBuilder(); for (var index = startRow; index <= endRow; index++) { var values = new List <string>(); foreach (var row in fields) { var value = sheet.GetRow(index).Cell(row).Value(); if (noQuote != null && noQuote.Contains(row)) { value = $"'{value}'"; } values.Add(value); } buf.Append(string.Join(",", values)); buf.Append("\n"); } var tmpFile = Path.GetTempFileName(); try { File.WriteAllText(tmpFile, buf.ToString()); var loadSql = $"load data infile '{tmpFile.Replace(@"\", @"\\")}' into table `{tableName}` " + "CHARACTER SET utf8 FIELDS TERMINATED BY ',' " + "OPTIONALLY ENCLOSED BY '\\'' LINES TERMINATED BY '\\n'"; return(Database.ExecuteSqlRaw(loadSql)); } finally { File.Delete(tmpFile); } }
public Template(string templateLocation) { string fileExtension = Path.GetExtension(templateLocation); { if (fileExtension.Equals(".xls", StringComparison.InvariantCultureIgnoreCase)) { extension = ExcelExtension.XLS; } else if (fileExtension.Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase)) { extension = ExcelExtension.XLSX; } else { throw new ArgumentException("Given file has in correct extension", templateLocation); } } Stream stream = File.OpenRead(templateLocation); Load(stream); }
public void Execute() { var workbook = ExcelExtension.LoadExcel(CertExcel); var sheet = workbook.GetSheetAt(0); var map = Program.GenerateGroupData(sheet, BeginRow, EndRow); var total = 0; foreach (var(xzj, group) in map) { WriteLine($"{(xzj+":").FillRight(11)} {group.Total}"); total += group.Total; if (Full) { foreach (var(csq, list) in group.Data) { WriteLine($" {(csq+":").FillRight(11)} {list.Count}"); } } } WriteLine($"{"合计:".FillRight(11)} {total}"); }
int ImportExcel() { if (fuExcel.FileName.Length > 3) { if (fuExcel.FileName.Substring(fuExcel.FileName.Length - 3, 3) == "xls") { try { //Nhập dữ liệu từ file excel vào dataset DataSet ds = new DataSet(); ds = ExcelExtension.ImportExcelXLS(fuExcel.PostedFile, true); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { //Thêm thông tin vào csdl // ds.Tables[0].Rows[i][].ToString() if (ds.Tables[0].Rows[i][0].ToString().Length > 0) { LanguageKey.InsertLanguageKeyProc(ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][1].ToString()); } } return(0); } catch { return(2); } } else { return(1); } } else { return(1); } }
private void SetColumnsNumber <T>() { Type type = typeof(T); ConstructorInfo constructor = type.GetConstructor(new Type[0]); object instance = constructor.Invoke(new object[] { }); PropertyInfo[] properties = type.GetProperties(); this.ModelColumnsNumber = new List <int>(); foreach (PropertyInfo property in properties) { ExcelColumn excelColumn = property.GetCustomAttribute <ExcelColumn>(); if (excelColumn == null) { continue; } int columnNumber = excelColumn.Column; if (!String.IsNullOrWhiteSpace(excelColumn.Letter)) { columnNumber = ExcelExtension.ColumnLetterToColumnIndex(excelColumn.Letter); } this.ModelColumnsNumber.Add(columnNumber); } }
public void DefaultTitleStyleTest() { //默认设置标题列样式 ExcelExtension.ConfigurationTitleCellStyle((cellStyle) => { cellStyle.FillForegroundColor = HSSFColor.Green.Index; cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.Alignment = HorizontalAlignment.Center; }); var excelTable = new ExcelTable("TitleOne", "TitleTwo"); var wb = ExcelExtension.CreateWorkbook().AddData(excelTable); var titleRow = wb.GetSheetAt(0).GetRow(0); var titleOneCellStyle = titleRow.GetCell(0).CellStyle; Assert.Equal(HSSFColor.Green.Index, titleOneCellStyle.FillForegroundColor); Assert.Equal(FillPattern.SolidForeground, titleOneCellStyle.FillPattern); Assert.Equal(HorizontalAlignment.Center, titleOneCellStyle.Alignment); var titleTwoCellStyle = titleRow.GetCell(1).CellStyle; Assert.Equal(HSSFColor.Green.Index, titleTwoCellStyle.FillForegroundColor); Assert.Equal(FillPattern.SolidForeground, titleTwoCellStyle.FillPattern); Assert.Equal(HorizontalAlignment.Center, titleTwoCellStyle.Alignment); }
protected virtual IWorkbook CreateExportSheet(int rowCount, ExcelExtension ext) { return(this.UIDispatcher.Invoke(new Func <IWorkbook>(() => { IWorkbook workbook = ext == ExcelExtension.XLS ? (IWorkbook) new HSSFWorkbook() : new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(this.Header); var firstRow = sheet.CreateRow(0); var bindPropCount = this.Table.Columns.Count() - this.Table.Columns.Where(c => string.IsNullOrWhiteSpace(GridColumn.GetBindProp(c))).Count(); for (int i = 0; i < bindPropCount; i++) { DataGridColumn col = this.Table.Columns[i]; firstRow.CreateCell(i, CellType.String).SetCellValue(col.Header.ToString()); } Array context = this.GetExportData(rowCount); for (int i = 0; i < context.Length; i++) { IRow row = sheet.CreateRow(i + 1); object rowData = context.GetValue(i); for (int j = 0; j < bindPropCount; j++) { DataGridColumn col = this.Table.Columns[j]; string bindProp = GridColumn.GetBindProp(col); IValueConverter cvt = GridColumn.GetConverter(col); object cellValue = rowData.GetType().GetProperty(bindProp).GetValue(rowData, null); if (cvt != null) { cellValue = cvt.Convert(cellValue, typeof(string), null, null); } row.CreateCell(j, CellType.String).SetCellValue((cellValue ?? string.Empty).ToString()); } } return workbook; }), null) as IWorkbook); }
public bool TryGetDataFile(int contentLength, string fileName, Stream inputStream, out IEnumerable <DataRow> dataRows, bool isFirstRowAsColumNames = true) { dataRows = new List <DataRow>(); if (contentLength > 0) { string fileExtension = System.IO.Path.GetExtension(fileName); if (".xls".Equals(fileExtension) || ".xlsx".Equals(fileExtension) || ".csv".Equals(fileExtension)) { IExcelDataReader dataReader = null; IEnumerable <string> sheets = ExcelExtension.GetWorksheetNames(inputStream, fileExtension, out dataReader, isFirstRowAsColumNames); if (!sheets.Any()) { return(false); } dataRows = ExcelExtension.GetData(sheets.First(), dataReader, fileExtension, isFirstRowAsColumNames, this.GetHeaders); if (dataRows != null) { return(true); } } } return(false); }
public void Execute() { var startDate = StartDate != null?ConvertToDashedDate(StartDate) : ""; var endDate = EndDate != null?ConvertToDashedDate(EndDate) : ""; var timeSpan = ""; if (startDate != "") { timeSpan += startDate; if (endDate != "") { timeSpan += "_" + endDate; } } WriteLine(timeSpan); var dir = @"D:\精准扶贫\"; var xlsx = "批量信息变更模板.xls"; Result <Cbsh> result = null; Session.Use(session => { session.SendService(new CbshQuery(startDate, endDate)); result = session.GetResult <Cbsh>(); }); if (result != null) { WriteLine($"共计 {result.Count} 条"); if (result.Count > 0) { var workbook = ExcelExtension.LoadExcel(Path.Join(dir, xlsx)); var sheet = workbook.GetSheetAt(0); int index = 1, copyIndex = 1; var export = false; using var context = new FpDbContext(); foreach (var cbsh in result.Data) { var data = from fpData in context.FpHistoryData where fpData.Idcard == cbsh.idcard select fpData; if (data.Any()) { var info = data.First(); WriteLine( $"{cbsh.idcard} {cbsh.name.FillRight(6)} {cbsh.birthDay} {info.Jbrdsf} " + $"{(info.Name != cbsh.name ? info.Name : "")}"); var row = sheet.GetOrCopyRow(index++, copyIndex, false); row.Cell("B").SetValue(cbsh.idcard); row.Cell("E").SetValue(cbsh.name); row.Cell("J").SetValue(_jbClassMap[info.Jbrdsf]); export = true; } else { WriteLine($"{cbsh.idcard} {cbsh.name.FillRight(6)} {cbsh.birthDay}"); } } if (Export && export) { WriteLine($"导出 批量信息变更{timeSpan}.xls"); workbook.Save( Path.Join(dir, $"批量信息变更{timeSpan}.xls"), true); } } } }
static void Main(string[] args) { var srcDir = @"E:\机关养老保险\1015确认表"; var outDir = @"E:\机关养老保险\1015确认表(新表)"; var tmpXls = @"E:\机关养老保险\(模板)试点期间参保人员缴费确认表.xls"; foreach (var xls in Directory.EnumerateFiles(srcDir)) { WriteLine($"{xls}"); var workbook = ExcelExtension.LoadExcel(xls); var sheet = workbook.GetSheetAt(0); var outWorkbook = ExcelExtension.LoadExcel(tmpXls); var outSheet = outWorkbook.GetSheetAt(0); (int start, int end)copyRange = (1, 11); int startRow = 4, currentRow = 4; var code = sheet.Cell(2, 2).Value(); var name = sheet.Cell(2, 6).Value(); outSheet.Cell(2, 2).SetValue(code); outSheet.Cell(2, 6).SetValue(name); WriteLine($"{name} {code}"); for (var i = 4; i < sheet.LastRowNum; i++) { var r = copyRange.start; var row = sheet.GetRow(i); var id = row.Cell(r)?.Value(); if (id == null) { continue; } if (Regex.IsMatch(id, @"^\d+$")) { WriteLine($"{currentRow} {id}"); var outRow = outSheet.GetOrCopyRow(currentRow++, startRow); outRow.Cell(r).SetValue(id); for (r += 1; r < copyRange.end; r++) { if (r == 8 || r == 9) { outRow.Cell(r).SetCellValue(row.Cell(r).NumericCellValue); } else { outRow.Cell(r).SetValue(row.Cell(r).Value()); } } } else if (id == "说明:") { var total = sheet.GetRow(i - 2).Cell("B").Value(); var hj = sheet.GetRow(i - 1).Cell("I").NumericCellValue; var lx = sheet.GetRow(i - 1).Cell("J").NumericCellValue; WriteLine($"{total} 合计 {hj} {lx}"); var outRow = outSheet.GetOrCopyRow(currentRow++, startRow); //outRow.Cell("B").SetValue(total); outRow.Cell("B").SetCellFormula( $"CONCATENATE(\"共 \",SUMPRODUCT(1/COUNTIF(C5:C{currentRow-1},C5:C{currentRow-1}&\"*\")),\" 人\")"); outRow.Cell("H").SetValue("合计"); //outRow.Cell("I").SetCellValue(hj); outRow.Cell("I").SetCellFormula($"SUM(I5:I{currentRow-1})"); //outRow.Cell("J").SetCellValue(lx); outRow.Cell("J").SetCellFormula($"SUM(J5:J{currentRow-1})"); break; } } outWorkbook.Save(Path.Join(outDir, $"试点期间参保人员缴费确认表_{code}_{name}.xls")); //break; } }
public bool TryGetObject <T>(DataRow dataRow, out T dataObj, int rowNumber) { Type type = typeof(T); ConstructorInfo constructor = type.GetConstructor(new Type[0]); object instance = constructor.Invoke(new object[] { }); PropertyInfo[] properties = type.GetProperties(); dataObj = default(T); dynamic dataObjRaw = new ExpandoObject(); IDictionary <string, object> dynamicObjDictionary = dataObjRaw as IDictionary <string, object>; RowResult rowResult = new RowResult { Index = rowNumber, Number = rowNumber + 1 }; foreach (PropertyInfo property in properties) { ExcelColumn excelColumn = property.GetCustomAttribute <ExcelColumn>(); if (excelColumn == null) { continue; } int columnNumber = excelColumn.Column; if (!String.IsNullOrWhiteSpace(excelColumn.Letter)) { columnNumber = ExcelExtension.ColumnLetterToColumnIndex(excelColumn.Letter); } if (columnNumber >= dataRow.ItemArray.Length) { return(false); } object columnData = dataRow[columnNumber]; dynamicObjDictionary.Add(property.Name, Convert.ToString(columnData)); Header header = this.Headers.FirstOrDefault(x => x.Index == columnNumber); string excelName = header.Name; if (String.IsNullOrWhiteSpace(excelName)) { excelName = String.Format("\"{0}\"", header.Letter); } //Validar IEnumerable <ValidationAttribute> validationAttributes = property.GetCustomAttributes <ValidationAttribute>(); IList <ValidationResult> result = null; ColumnResult columnResult = new ColumnResult { Header = header }; if (columnData != null) { columnResult.Value = Convert.ToString(columnData); } if (!ValidatorUtil.ValidateValue(columnData, excelName, validationAttributes, out result)) { columnResult.ErrorMessages = result.Select(x => x.ErrorMessage).ToList(); rowResult.ColumnResults.Add(columnResult); continue; } //if has a adapte process if (excelColumn.Adapter != null) { string dataStr = Convert.ToString(columnData); ITypeAdapter adapter = (ITypeAdapter)Activator.CreateInstance(excelColumn.Adapter); columnData = adapter.AdaptFormat(dataStr); } string typeErrorMessage = null; if (!this.TryGetAttribute(instance, property, columnData, header, rowResult, out typeErrorMessage)) { columnResult.ErrorMessages.Add(typeErrorMessage); } rowResult.ColumnResults.Add(columnResult); } rowResult.RowsValues = dataObjRaw; Results.RowResults.Add(rowResult); dataObj = (T)instance; return(!rowResult.HasError); }
public Template(Stream templateStream, ExcelExtension extension) { this.extension = extension; Load(templateStream); }
private IWorkbook GetNewWorkbook(ExcelExtension extension) { var newStream = workbookStream; workbookStream.CopyTo(newStream); IWorkbook newWb; if (extension == ExcelExtension.XLS) { newWb = new HSSFWorkbook(newStream); } else { newWb = new XSSFWorkbook(newStream); } return newWb; }
private bool TryGetAttribute(object instance, PropertyInfo property, object data, int columnNumber, int rowNumber) { Type propertyType = property.PropertyType; ErrorMap errorMap = new ErrorMap { Line = rowNumber, ExcelLine = rowNumber + 1, Column = columnNumber, ExcelColumn = columnNumber + 1, ColumnLetter = ExcelExtension.ColumnIndexToColumnLetter(columnNumber) }; if (propertyType == typeof(DateTime)) { DateTime objectData = new DateTime(); if (DateUtil.TryParserObject(data, out objectData)) { property.SetValue(instance, objectData); return(true); } errorMap.Description = "Error en el formato de la fecha"; Results.ErrorMap.Add(errorMap); return(false); } if (propertyType == typeof(DateTime?)) { string dataStr = Convert.ToString(data); if (String.IsNullOrWhiteSpace(dataStr)) { property.SetValue(instance, null); return(true); } DateTime objectData = new DateTime(); if (DateUtil.TryParserObject(data, out objectData)) { property.SetValue(instance, objectData); return(true); } errorMap.Description = "Error en el formato de la fecha"; Results.ErrorMap.Add(errorMap); return(false); } try { if (propertyType == typeof(string)) { property.SetValue(instance, Convert.ToString(data)); } else { object value = null; if (FormatUtils.TryGetValue(data, property.PropertyType, out value)) { property.SetValue(instance, value); } } } catch (InvalidCastException e) { errorMap.Description = "Error en el tipo de dato no coincide con el especificado"; Results.ErrorMap.Add(errorMap); return(false); } catch (FormatException e) { errorMap.Description = "Error en el tipo de dato no coincide con el especificado"; Results.ErrorMap.Add(errorMap); return(false); } return(true); }
public void Execute() { var workbook = ExcelExtension.LoadExcel(SourceExcel); var sheet = workbook.GetSheetAt(0); WriteLine("生成分组映射表"); var map = new Dictionary <string, List <int> >(); for (var index = BeginRow - 1; index < EndRow; index++) { var xzqh = sheet.Cell(index, XzqhCol).Value(); Match match = null; foreach (var regex in Xzqh.regex) { match = Regex.Match(xzqh, regex); if (match.Success) { break; } } if (match == null || !match.Success) { throw new ApplicationException($"未匹配行政区划: {xzqh}"); } else { var xzj = match.Groups[2].Value; if (!map.ContainsKey(xzj)) { map[xzj] = new List <int>(); } map[xzj].Add(index); } } WriteLine("生成分组数据表"); /*if (Directory.Exists(OutDir)) * Directory.Move(OutDir, OutDir + ".orig"); * Directory.CreateDirectory(OutDir);*/ foreach (var xzj in map.Keys) { var count = map[xzj].Count; WriteLine($"{xzj}: {count}"); if (count <= 0) { continue; } var outWorkbook = ExcelExtension.LoadExcel(TemplateExcel); var outSheet = outWorkbook.GetSheetAt(0); int startRow = TemplateBeginRow - 1, currentRow = startRow; map[xzj].ForEach(rowIndex => { var index = currentRow - startRow + 1; var inRow = sheet.Row(rowIndex); //WriteLine($" {index} {currentRow} {startRow}"); var outRow = outSheet.GetOrCopyRow(currentRow++, startRow); for (var cell = inRow.FirstCellNum; cell < inRow.LastCellNum; cell++) { //WriteLine($"{cell}"); outRow.Cell(cell).SetValue(inRow.Cell(cell).Value()); } if (NOCel != null) { outRow.Cell(NOCel).SetValue(index); } }); outWorkbook.Save( Path.Join(OutDir, $"{xzj}{map[xzj].Count}.xls")); } }
public static void ExportFpData( string monthOrAll, string tmplXlsx, string saveXlsx) { using var db = new FpDbContext(); WriteLine($"开始导出扶贫底册: {monthOrAll}扶贫数据=>{saveXlsx}"); var workbook = ExcelExtension.LoadExcel(tmplXlsx); var sheet = workbook.GetSheetAt(0); int startRow = 2, currentRow = 2; IQueryable <Database.Jzfp2021.FpData> data = null; if (monthOrAll.ToUpper() == "ALL") { data = db.FpHistoryData.FromSqlRaw( "SELECT * FROM fphistorydata ORDER BY CONVERT( xzj USING gbk ), " + "CONVERT( csq USING gbk ), CONVERT( name USING gbk )"); } else { data = db.FpMonthData.FromSqlRaw( "SELECT * FROM fpmonthdata WHERE month={0} ORDER BY CONVERT( xzj USING gbk ), " + "CONVERT( csq USING gbk ), CONVERT( name USING gbk )", monthOrAll); } foreach (var d in data) { var index = currentRow - startRow + 1; WriteLine($"{index} {d.Idcard} {d.Name}"); var row = sheet.GetOrCopyRow(currentRow++, startRow); row.Cell("A").SetValue(index); row.Cell("B").SetValue(d.NO); row.Cell("C").SetValue(d.Xzj); row.Cell("D").SetValue(d.Csq); row.Cell("E").SetValue(d.Address); row.Cell("F").SetValue(d.Name); row.Cell("G").SetValue(d.Idcard); row.Cell("H").SetValue(d.BirthDay); row.Cell("I").SetValue(d.Pkrk); row.Cell("J").SetValue(d.PkrkDate); row.Cell("K").SetValue(d.Tkry); row.Cell("L").SetValue(d.TkryDate); row.Cell("M").SetValue(d.Qedb); row.Cell("N").SetValue(d.QedbDate); row.Cell("O").SetValue(d.Cedb); row.Cell("P").SetValue(d.CedbDate); row.Cell("Q").SetValue(d.Yejc); row.Cell("R").SetValue(d.YejcDate); row.Cell("S").SetValue(d.Ssjc); row.Cell("T").SetValue(d.SsjcDate); row.Cell("U").SetValue(d.Sypkry); row.Cell("V").SetValue(d.Jbrdsf); row.Cell("W").SetValue(d.JbrdsfFirstDate); row.Cell("X").SetValue(d.JbrdsfLastDate); row.Cell("Y").SetValue(d.Jbcbqk); row.Cell("Z").SetValue(d.JbcbqkDate); } workbook.Save(saveXlsx); WriteLine($"结束导出扶贫底册: {monthOrAll}扶贫数据=>{saveXlsx}"); }