//需要记录行数
ExcelRow GetExcelRow(IRow row, int rowNum)
{
ExcelRow excelRow = new ExcelRow();
excelRow.row = rowNum;
if (row == null)
{
return(excelRow);
}
excelRow.row = row.RowNum;
excelRow.cellList = new List <ExcelCell>();
for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
{
excelRow.AddCell(GetExcelCell(row.GetCell(i), i));
}
return(excelRow);
}
/// <summary>
/// 数据导出
/// </summary>
/// <param name="searchModel">查询参数</param>
/// <param name="fileFormat">文件格式,2003/2007</param>
/// <returns>用于下载的Excel文件内容</returns>
public ActionResult Export(SysSearchModel searchModel, int?fileFormat)
{
searchModel.GroupType = "Custom";
var response = GetStatisData(searchModel);
bool xlsx = (fileFormat ?? 2007) == 2007;
ExcelExporter ee = new ExcelExporter("材料动态分析表", xlsx);
ee.SetColumnTitles("序号, 地区, 报告总数, 不合格数量, 不合格数量占比, 生产厂家总数, 不合格生产商家数量, 不合格生产厂家占比 ");
if (response.IsValid)
{
var bucks = response.Aggs.Terms("Custom").Buckets;
//countByArea 地区报告总数 unQualifyCount 地区报告不合格总数 countUnitByArea 地区厂家总数 unQualifyUnitCountByArea 地区厂家不合格总数
Dictionary <string, int> countByArea, unQualifyCount, countUnitByArea, unQualifyUnitCountByArea;
GetSearchResult(bucks, out countByArea, out unQualifyCount, out countUnitByArea, out unQualifyUnitCountByArea);
var index = 1;
foreach (var item in countByArea)
{
var unQualifyCountRow = 0;
var unitCountByArea = 0;
var unqualifyUnitCount = 0;
var isUnqualify = unQualifyCount.TryGetValue(item.Key, out unQualifyCountRow); //isUnqualify 标识是否有不合格报告
var isUnitCount = countUnitByArea.TryGetValue(item.Key, out unitCountByArea);
var isUnqualifyUnitCount = unQualifyUnitCountByArea.TryGetValue(item.Key, out unqualifyUnitCount);
ExcelRow row = ee.AddRow();
row.AddCell(index++);
row.AddCell(item.Key);
row.AddCell(item.Value);
row.AddCell(isUnqualify ? unQualifyCountRow : 0);
row.AddCell(item.Value == 0 ? "0" : Math.Round(((unQualifyCountRow * 1.00 / item.Value) * 100), 2).ToString() + "%");
row.AddCell(unitCountByArea.ToString());
row.AddCell(unqualifyUnitCount.ToString());
row.AddCell(unitCountByArea == 0 ? "0" : Math.Round(((unqualifyUnitCount * 1.00 / unitCountByArea) * 100), 2).ToString() + "%");
}
}
// 改动4：返回字节流
return(File(ee.GetAsBytes(), ee.MIME, ee.FileName));
}
/// <summary>
/// 把ExcelSheet对象保存成Excel文件
/// </summary>
/// <param name="excelSheet"></param>
/// <param name="filePath"></param>
/// <param name="version"></param>
/// <returns></returns>
public static int Write <TRow>(string filePath, IList <TRow> dataList, ExcelVersions version = ExcelVersions.Xls) where TRow : IExcelRow
{
List <PropertyAttribute <ExcelColumnAttribute> > properties = Reflections.GetPropertyAttribute <ExcelColumnAttribute>(typeof(TRow));
if (properties.Count == 0)
{
return(DotNETCode.SUCCESS);
}
ExcelSheet sheet = new ExcelSheet();
// 先写第一行，相当于是标题
ExcelRow titleRow = new ExcelRow();
IEnumerable <ExcelColumnAttribute> columns = properties.Select(v => v.Attribute);
foreach (ExcelColumnAttribute column in columns)
{
titleRow.AddCell(column.Name, ExcelCellTypes.String);
}
sheet.AddRow(titleRow);
foreach (TRow row in dataList)
{
ExcelRow excelRow = new ExcelRow();
foreach (PropertyAttribute <ExcelColumnAttribute> property in properties)
{
// 当前要写入的列
object value = property.Property.GetValue(row, null);
excelRow.AddCell(value, property.Attribute.Type);
}
sheet.AddRow(excelRow);
}
return(QuickWrite(sheet, filePath, version));
}
public static int QuickRead(string filePath, ReadOptions options, out ExcelSheet excelSheet)
{
excelSheet = null;
if (!File.Exists(filePath))
{
return(DotNETCode.FILE_NOT_FOUND);
}
using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
string extention = Path.GetExtension(filePath);
IWorkbook workbook = OpenRead(extention, fs);
ISheet sheet = workbook.GetSheetAt(0);
// 还剩leftRow行没读取
int leftRow = sheet.PhysicalNumberOfRows;
excelSheet = new ExcelSheet();
excelSheet.Name = sheet.SheetName;
int currentRow = 0;
// 要注意处理空行
while (leftRow > 0)
{
IRow row = sheet.GetRow(currentRow);
if (row == null)
{
// 该行是空行，不计算在内
currentRow++;
continue;
}
int numCell = row.PhysicalNumberOfCells;
ExcelRow excelRow = new ExcelRow();
for (int cellnum = 0; cellnum < numCell; cellnum++)
{
ICell cell = row.GetCell(cellnum);
if (cell == null || cell.CellType == CellType.Blank)
{
// 空列，根据选项进行处理
if (options.HasFlag(ReadOptions.IgnoreEmptyCell))
{
}
else if (options.HasFlag(ReadOptions.KeepEmptyCell))
{
excelRow.AddCell(null, ExcelCellTypes.Null);
}
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
{
excelRow.AddCell(cell.NumericCellValue, ExcelCellTypes.Numberic);
break;
}
case CellType.String:
{
excelRow.AddCell(cell.StringCellValue, ExcelCellTypes.String);
break;
}
case CellType.Blank:
{
// 空列，在107行进行了处理
break;
}
default:
logger.ErrorFormat("不支持的Cell数据类型, {0}", cell.CellType);
return(DotNETCode.NOT_SUPPORTED);
}
}
excelSheet.AddRow(excelRow);
leftRow--;
currentRow++;
}
fs.Close();
}
return(DotNETCode.SUCCESS);
}
public ActionResult Export(int?fileFormat)
{
var reportUploadResult = GetReportUploadStatis();
bool xlsx = (fileFormat ?? 2007) == 2007;
ExcelExporter ee = new ExcelExporter("报告上传统计", xlsx);
ee.SetColumnTitles("序号, 检测机构,最新上传时间,未上传间隔 ");
if (reportUploadResult.IsValid)
{
var bucks = reportUploadResult.Aggs.Terms(aggeKey).Buckets;
int userId = HttpContext.User.Identity.GetUserId <Int32>();
var allInsts = GetCurrentEliminateIsUerInsts();
int index = 1;
Dictionary <string, DateTime> dicExist = new Dictionary <string, DateTime>();
foreach (var item in bucks)
{
var tbuck = item.TopHits(tBuckKey);
if (tbuck != null && tbuck.Total > 0)
{
var lastItem = tbuck.Documents <es_t_bp_item>().First();
if (lastItem != null && lastItem.UPLOADTIME.HasValue)
{
dicExist[item.Key] = lastItem.UPLOADTIME.Value;
}
}
}
foreach (var custom in allInsts)
{
//if (!checkUnitService.IsCanUploadUnit(custom.Key))
//{
// continue;
//}
var currentDt = DateTime.Now;
if (dicExist.ContainsKey(custom.Key))
{
ExcelRow row = ee.AddRow();
row.AddCell(index.ToString());
row.AddCell(custom.Value);
var lastDt = dicExist[custom.Key];
var dtSpan = currentDt - lastDt;
row.AddCell(lastDt.ToString("yyyy-MM-dd HH:mm"));
row.AddCell("{0}天{1}小时{2}分".Fmt(dtSpan.Days, dtSpan.Hours, dtSpan.Minutes));
index++;
}
else
{
ExcelRow row = ee.AddRow();
row.AddCell(index.ToString());
row.AddCell(custom.Value);
var lastDt = DateTime.Now.AddYears(-1);
var dtSpan = currentDt - lastDt;
row.AddCell(lastDt.ToString("yyyy-MM-dd HH:mm"));
row.AddCell("{0}天{1}小时{2}分".Fmt(dtSpan.Days, dtSpan.Hours, dtSpan.Minutes));
index++;
}
}
}
// 改动4：返回字节流
return(File(ee.GetAsBytes(), ee.MIME, ee.FileName));
}
public ActionResult Export(SysSearchModel model, int?fileFormat)
{
ISearchResponse <es_t_bp_item> response = GetSearchResult(model);
var allItems = itemNameService.GetAllItemName();
var allInsts = checkUnitService.GetAllCheckUnit();
var reportConclusions = sysDictService.GetDictsByKey("ReportConclusionCode");
bool xlsx = (fileFormat ?? 2007) == 2007;
ExcelExporter ee = new ExcelExporter("监督抽检", xlsx);
ee.SetColumnTitles("序号, 工程名称, 合格, 机构名称, 检测项目, 报告编号, 样品编号, 委托日期, 检测日期, 数据状态");
int pos = model.posStart.HasValue ? model.posStart.Value : 0;
int index = pos;
foreach (var item in response.Documents)
{
ExcelRow row = ee.AddRow();
row.AddCell(index + 1);
row.AddCell(item.PROJECTNAME);
row.AddCell(SysDictUtility.GetKeyFromDic(reportConclusions, item.CONCLUSIONCODE, "/"));
row.AddCell(checkUnitService.GetCheckUnitByIdFromAll(allInsts, item.CUSTOMID));
if (item.ITEMCHNAME.IsNullOrEmpty())
{
row.AddCell(itemNameService.GetItemCNNameFromAll(allItems, item.REPORTJXLB, item.ITEMNAME));
}
else
{
row.AddCell(item.ITEMCHNAME);
}
row.AddCell(item.REPORTNUM);
row.AddCell(item.SAMPLENUM);
row.AddCell(item.ENTRUSTDATE.HasValue ? item.ENTRUSTDATE.Value.ToString("yyyy-MM-dd") : "/");
row.AddCell(item.CHECKDATE.HasValue ? item.CHECKDATE.Value.ToString("yyyy-MM-dd") : "/");
row.AddCell(reportService.GetReportDataStatus(item.SAMPLEDISPOSEPHASEORIGIN));
index++;
}
return(File(ee.GetAsBytes(), ee.MIME, ee.FileName));
}
static void Main(string[] args)
{
List <ExcelTable> l = new List <ExcelTable>();
for (int k = 0; k < 4; k++)
{
ExcelTable table = new ExcelTable();
table.Landscape = true;
//table.Title = "疑似黑广播信号出现情况" + (k + 1);
table.ColumnCount = 10;
int[] columnswidth = new int[table.ColumnCount];
ExcelTitle title = new ExcelTitle();
title.TableTitle = "疑似黑广播信号出现情况" + (k + 1);
table.Title = title;
ExcelHeader header = new ExcelHeader();
ExcelRow headerrow = (ExcelRow)table.CreateRow();
headerrow.Height = 20;
ExcelCell headercell = (ExcelCell)headerrow.CreateCell();
headercell.Value = "表格描述信息";
headercell.Colspan = table.ColumnCount;
headerrow.AddCell(headercell);
List <Row> rows = new List <Row>();
rows.Add(new ExcelRow());
rows.Add(headerrow);
header.Rows = rows;
table.Header = header;
ExcelFooter footer = new ExcelFooter();
//ExcelRow footerrow = (ExcelRow)table.CreateRow();
//footerrow.Height = 200;
//ExcelCell footercell = (ExcelCell)footerrow.CreateCell();
//byte[] b = System.IO.File.ReadAllBytes(@"C:\Users\HRDS-ZENGPEIFENG\Pictures\th2X7ZWG1D.jpg");
//footercell.Value = Convert.ToBase64String(b);
//footercell.Colspan = table.ColumnCount;
//footercell.CellType = NPOIHelper.NPOI.Common.CellTypes.Image;
//footerrow.AddCell(footercell);
ExcelRow footerrow2 = (ExcelRow)table.CreateRow();
ExcelCell footercell2 = (ExcelCell)footerrow2.CreateCell();
footercell2.Colspan = table.ColumnCount;
footercell2.CellType = NPOIHelper.NPOI.Common.CellTypes.String;
footercell2.Value = "表格描述信息";
footerrow2.AddCell(footercell2);
//footercell2 = (ExcelCell)footerrow2.CreateCell();
//footercell2.CellType = NPOIHelper.NPOI.Common.CellTypes.String;
//footercell2.Value = "表格描述信息2";
//footerrow2.AddCell(footercell2);
footerrow2.HaveRowBreak = true;
List <Row> footerrows = new List <Row>();
footerrows.Add(new ExcelRow());
//footerrows.Add(footerrow);
footerrows.Add(footerrow2);
footer.Rows = footerrows;
table.Footer = footer;
ExcelTableHeader tableheader = new ExcelTableHeader();
ExcelTableBody tablebody = new ExcelTableBody();
//throws.Add(new )
for (int r = 0; r < table.ColumnCount; r++)
{
ExcelRow row;
if (r == 0 || r == 1)
{
row = (ExcelRow)table.CreateRow();
row.Height = 28;
}
else
{
row = (ExcelRow)table.CreateRow();
}
for (int i = 0; i < table.ColumnCount; i++)
{
ExcelCell cell = (ExcelCell)row.CreateCell();
if (r == 4 && i == 5)
{
cell.IsBasedOnDefaultStyle = true;
cell.Style = "font-color:#03A8F4;border-color:#03A8F4;font-size:50;";
}
//if (i == 1)
//{
// cell.CellType = NPOIHelper.NPOI.Common.CellTypes.Numeric;
// cell.Value = r * i;
// cell.Style = "border:thin;font-color:red;font-weight:normal;text-align:left;";
//}
//else
//{
cell.CellType = NPOIHelper.NPOI.Common.CellTypes.String;
if (r == 0 || r == 1)
{
cell.Value = "标题" + i + "\r\n(单位)";
columnswidth[i] = 12;
}
else
{
cell.Value = "行:" + r + " 列:" + i;
}
//}
//cell.FontColor = HSSFColor.BlueGrey.Index;
row.AddCell(cell);
}
if (r == 0 || r == 1)
{
tableheader.AddRow(row);
}
else
{
tablebody.AddRow(row);
}
}
table.TableHeader = tableheader;
table.TableBody = tablebody;
table.ColumnWidths = columnswidth;
l.Add(table);
}
ExcelHelper excelhelper = new ExcelHelper(l);
MemoryStream s = excelhelper.RenderToXls();
bool issaved = excelhelper.SaveToFile(s, "d:/test.xls");
//issaved = false;
if (issaved)
{
IPrint proxy = null;
RemotingConfiguration.Configure(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile, false);
proxy = (IPrint)Activator.GetObject(typeof(IPrint), "tcp://172.39.8.173:1235/Print/PrintURL");
proxy.ExcelPrint("d:/test.xls", "疑似黑广播信号出现情况1", new PrintCallBackHandler());
//excelhelper.ExcelPrint("d:/test.xls", "疑似黑广播信号出现情况1");
}
}