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}");
}
