/// <summary>
/// Конвертировать лист (worksheet)
/// </summary>
/// <param name="sheet">Лист</param>
/// <returns></returns>
public ExcelSheetModel ConvertSheet(ExcelWorksheet sheet)
{
var rows = new List <ExcelRowModel>(sheet.Cells.Rows);
var cells = sheet.Cells;
cells.Reset();
int previosRowIndex = 1;
var currentCells = new List <ExcelCellModel>();
var currentRow = new ExcelRowModel
{
RowIndex = 1,
Cells = currentCells
};
rows.Add(currentRow);
while (cells.MoveNext())
{
if (cells.Current.Start.Row > previosRowIndex)
{
currentCells = new List <ExcelCellModel>();
currentRow = new ExcelRowModel
{
RowIndex = cells.Current.Start.Row,
Cells = currentCells
};
rows.Add(currentRow);
}
var currentCell = new ExcelCellModel
{
ColumnIndex = cells.Current.Start.Column,
Value = cells.Current.Value,
Address = cells.Current.Address,
FullAddress = cells.Current.FullAddress
};
currentCells.Add(currentCell);
previosRowIndex = cells.Current.Start.Row;
}
var sheetModel = new ExcelSheetModel
{
SheetName = sheet.Name,
Rows = rows
};
return(sheetModel);
}
public void TMSEmployeeExportExcel(string searchprm, int PageSize)
{
EMPTMSPara ObjPara = JsonConvert.DeserializeObject <EMPTMSPara>(searchprm,
new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore,
DateFormatString = "dd/MM/yyyy"
});
ObjPara.OrderBy = "EmployeeNo";
ObjPara.OrderDirection = "Desc";
ObjPara.Page = 1;
ObjPara.PageSize = PageSize;
DataTable dt = new DataTable();
dt = ReportManager.EmployeeTMSSummaryReport(ObjPara);
string fileName = "TMS_Employee_Summary_" + SystemConfig.CurrentDate.ToString("MM-dd-yyyy");
string[] RemoveColumn = { "CompanyID", "TargetDisplayID", "ReturnDisplay", "TotalRecord", "CreatedUser", "CreatedDate" };
for (int i = 0; i < RemoveColumn.Length; i++)
{
if (dt.Columns.Contains(RemoveColumn[i]))
{
dt.Columns.Remove(RemoveColumn[i]);
}
}
List <ExcelCellModel> listd = new List <ExcelCellModel>();
ExcelCellModel ex = new ExcelCellModel();
DateTime mytime = ObjPara.FromDate.Value;
List <int> columnIndex = new List <int>();
int b = 9;
for (DateTime day = ObjPara.FromDate.Value; day < ObjPara.ToDate.Value; day = day.AddDays(1.0))
{
if (BDatetime.isWeekDay(day))
{
columnIndex.Add(b);
}
b = b + 1;
}
ex.BackgroundColorInfo.ColunmIndex = columnIndex;
ex.BackgroundColorInfo.BackgorundColor = XLColor.FromHtml("#FFFFCC");
ex.BackgroundColorInfo.FontColor = XLColor.Black;
//ex. = XLColor.Black;// CellValue(0,PageSize)
listd.Add(ex);
FileInputHelper.ExportExcel(dt, fileName, "TMS Employee Summary", true, listd);
// return fileName;
}
public byte[] Write(DatasetDefinition data)
{
if (data == null)
{
throw new ArgumentNullException(nameof(data), "Null dataset definition was provided");
}
if (data.TableDefinitions == null || !data.TableDefinitions.Any())
{
return(null);
}
using (ExcelPackage package = new ExcelPackage())
{
foreach (TableDefinition tableDefinition in data.TableDefinitions)
{
int fieldCount = tableDefinition.FieldDefinitions.Count();
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(tableDefinition.Name);
IEnumerable <ExcelCellModel> headers = CreateHeaders(tableDefinition.FieldDefinitions);
for (int i = 1; i <= headers.Count(); i++)
{
ExcelCellModel excelCellModel = headers.ElementAt(i - 1);
ExcelRange cell = workSheet.Cells[1, i];
cell.Value = excelCellModel.Value;
cell.AddComment(excelCellModel.Comment, "Calculate Funding");
cell.Style.Font.Bold = true;
}
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
}
return(package.GetAsByteArray());
}
}
public byte[] Write(DatasetDefinition datasetDefinition, IEnumerable <TableLoadResult> data = null)
{
if (datasetDefinition == null)
{
throw new ArgumentNullException(nameof(datasetDefinition), "Null dataset definition was provided");
}
if (datasetDefinition.TableDefinitions == null || !datasetDefinition.TableDefinitions.Any())
{
return(null);
}
bool haveData = data != null;
using (ExcelPackage package = new ExcelPackage())
{
foreach (TableDefinition tableDefinition in datasetDefinition.TableDefinitions)
{
int fieldCount = tableDefinition.FieldDefinitions.Count();
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(tableDefinition.Name);
IEnumerable <ExcelCellModel> headers = CreateHeaders(tableDefinition.FieldDefinitions);
for (int i = 1; i <= headers.Count(); i++)
{
ExcelCellModel excelCellModel = headers.ElementAt(i - 1);
ExcelRange cell = workSheet.Cells[1, i];
cell.Value = excelCellModel.Value;
cell.AddComment(excelCellModel.Comment, "Calculate Funding");
cell.Style.Font.Bold = true;
}
if (haveData)
{
TableLoadResult tableData = data.FirstOrDefault(x => x.TableDefinition.Name == tableDefinition.Name);
if (tableData != null)
{
int rowNumber = 2;
foreach (RowLoadResult row in tableData.Rows)
{
for (int i = 1; i <= headers.Count(); i++)
{
ExcelCellModel excelCellModel = headers.ElementAt(i - 1);
object fieldValue = row.Fields.FirstOrDefault(x => x.Key == excelCellModel.Value.ToString()).Value;
if (fieldValue != null)
{
workSheet.Cells[rowNumber, i].Value = fieldValue;
}
}
rowNumber++;
}
}
}
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
}
return(package.GetAsByteArray());
}
}
public static Dictionary <string, string> printTypeDict = new Dictionary <string, string>();//打印类型字典(Key:打印类型，Value：相应打印类型的模板ExceJSON数据)
/// <summary>
/// 通过模板文件获取单元格集合
/// </summary>
/// <param name="workbook"></param>
/// <param name="qrCode">二维码Code</param>
/// <returns></returns>
public static List <ExcelCellModel> GetCellListByTemplateFile(XSSFWorkbook workbook)
{
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
List <ExcelCellModel> dataList = null;
ExcelCellModel data = null;
IRow row = null;
ICell cell = null;
ExcelPictureModel pictureModel = null;
var columnWith = 0;
BorderStyle[] border = new BorderStyle[4];
try
{
//总行数
int rowCount = sheet.LastRowNum;
if (rowCount > 0)
{
dataList = new List <ExcelCellModel>();
IRow firstRow = sheet.GetRow(0); //第一行
int cellCount = firstRow.LastCellNum; //总列数
//填充行
for (int i = 0; i <= rowCount; ++i)
{
//清空值
border = new BorderStyle[4];
row = sheet.GetRow(i);
if (row == null)
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
}
else
{
#region cell赋值
//四个边框按照 上下左右
border[0] = cell.CellStyle.BorderTop;
border[1] = cell.CellStyle.BorderBottom;
border[2] = cell.CellStyle.BorderLeft;
border[3] = cell.CellStyle.BorderRight;
columnWith = sheet.GetColumnWidth(j);
data = new ExcelCellModel()
{
rowNum = cell.RowIndex,
cellNum = cell.ColumnIndex,
cellWidth = columnWith,
cellHeight = row.Height,
fontSize = cell.CellStyle.GetFont(workbook).FontHeightInPoints,
wrapText = cell.CellStyle.WrapText,
borderLine = border,
Boldweight = cell.CellStyle.GetFont(workbook).IsBold,
verticalAlignment = cell.CellStyle.VerticalAlignment,
horizontalAlignment = cell.CellStyle.Alignment,
fontName = cell.CellStyle.GetFont(workbook).FontName,
//cellColor=cell.CellStyle.GetFont(workbook).Color
location = "",
// regionCell=""
};
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
data.cellValue = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
{
data.cellValue = cell.DateCellValue.ToString();
}
else
{
data.cellValue = cell.NumericCellValue.ToString();
}
break;
case CellType.String:
data.cellValue = cell.StringCellValue;
break;
}
//二维码单独处理
if (data.cellValue == PrintStruct.QRCODE)
{
pictureModel = new ExcelPictureModel()
{
endColNum = data.cellNum,
endRowNum = data.rowNum,
qrCode = PrintStruct.QRCODE,
CodeType = PrintStructFlag.QRCODE
};
data.excelPictureModel = pictureModel;
}
//一维码单独处理
if (data.cellValue == PrintStruct.BARCODE)
{
pictureModel = new ExcelPictureModel()
{
endColNum = data.cellNum,
endRowNum = data.rowNum,
qrCode = PrintStruct.BARCODE,
CodeType = PrintStructFlag.BARCODE
};
data.excelPictureModel = pictureModel;
}
#endregion
dataList.Add(data);
}
}
}
}
}
catch (Exception)
{
throw;
}
return(dataList);
}