public string GetExcelNameByTemplateIdAndSpecId(int input, int[] specId, DateTime begin, DateTime endTime)
{
DataSearchTableDto dataInfo = GetDataInfoByTemplateIdAndSpecId(input, specId, begin, endTime);
ExcelOper exceler = new ExcelOper();
string fileName = exceler.CreateSingleTableSearchExcel(dataInfo);
if (string.IsNullOrEmpty(fileName))
{
return("-1");
}
else
{
return(fileName);
}
}
public string CreateSingleTableSearchExcel(DataSearchTableDto excelData)
{
var tableHead = excelData.TableHead;
var eleList = tableHead.Elements;
var specList = tableHead.Specimens;
var dataList = excelData.TableData;
// HSSFWorkbook workbook = new HSSFWorkbook();
var workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "豫光";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "张承宇"; //填加xls文件作者信息
// si.ApplicationName = "LIMS"; //填加xls文件创建程序信息
// si.LastAuthor = "张承宇"; //填加xls文件最后保存者信息
// si.Comments = "张承宇"; //填加xls文件作者信息
// si.Title = "化验数据"; //填加xls文件标题信息
// si.Subject = "化验数据";//填加文件主题信息
// si.CreateDateTime = System.DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
ICellStyle titleStyle = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
titleStyle.Alignment = HorizontalAlignment.CenterSelection;
// titleStyle.VerticalAlignment = VerticalAlignment.Center;
//新建一个字体样式对象
IFont font = workbook.CreateFont();
//设置字体加粗样式
// font.Boldweight = short.MaxValue;
font.IsBold = true;
//使用SetFont方法将字体样式添加到单元格样式中
titleStyle.SetFont(font);
int rowIndex = 0;
IRow row1 = sheet.CreateRow(rowIndex++);
ICell cell00 = row1.CreateCell(0);
SetCell(cell00, null, "System.String", "签到时间");
//ICell cell01 = row1.CreateCell(1);
//SetCell(cell01, null, null, "采样时间");
int cellIndex = 1;
// 合并单元格
foreach (var specItem in specList)
{
int curIndex = cellIndex;
if (specItem.Count > 1)
{
int newCellIndex = cellIndex + specItem.Count;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, cellIndex, newCellIndex - 1));
cellIndex = newCellIndex;
}
else
{
cellIndex++;
}
var tempCell = row1.CreateCell(curIndex);
tempCell.CellStyle = titleStyle;
SetCell(tempCell, null, "System.String", specItem.Name);
}
cellIndex = 1;
//// 填充样品值
//foreach (var item in specList)
//{
// var tempCell = row1.CreateCell(cellIndex++);
// SetCell(tempCell, null, null, item.Name);
//}
IRow row2 = sheet.CreateRow(rowIndex++);
cellIndex = 1;
// 填充元素数据
foreach (var eleItem in eleList)
{
var tempCell = row2.CreateCell(cellIndex++);
SetCell(tempCell, null, "System.String", eleItem.Name);
}
// 合并签到时间和取样时间列表
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));
//sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1));
#region 日期样式
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd hh:mm");
#endregion
//填充数据
foreach (var dataRow in dataList)
{
IRow tmpRow = sheet.CreateRow(rowIndex++);
cellIndex = 0;
foreach (var cellValue in dataRow)
{
ICell tmpCell = tmpRow.CreateCell(cellIndex++);
if (cellIndex < 1)
{
SetCell(tmpCell, dateStyle, "System.DateTime", cellValue);
}
else
{
SetCell(tmpCell, null, "System.Double", cellValue);
}
}
}
#region 设置列宽度
sheet.SetColumnWidth(0, 18 * 256);
sheet.SetColumnWidth(1, 18 * 256);
for (int i = 2; i < eleList.Count; i++)
{
sheet.SetColumnWidth(i, 10 * 256);
}
#endregion
string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
string filePath = DirPath + fileName;
try
{
using (FileStream fs = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write))
{
workbook.Write(fs);
}
return(fileName);
}
catch
{
return(null);
}
}