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