public void TestNew() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(); XSSFDrawing Drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data"); byte[] wmfData = Encoding.UTF8.GetBytes("test wmf data"); byte[] pngData = Encoding.UTF8.GetBytes("test png data"); IList pictures = wb.GetAllPictures(); Assert.AreEqual(0, pictures.Count); int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG); Assert.AreEqual(1, pictures.Count); Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data)); int wmfIdx = wb.AddPicture(wmfData, PictureType.WMF); Assert.AreEqual(2, pictures.Count); Assert.AreEqual("wmf", ((XSSFPictureData)pictures[wmfIdx]).SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures[wmfIdx]).Data)); int pngIdx = wb.AddPicture(pngData, PictureType.PNG); Assert.AreEqual(3, pictures.Count); Assert.AreEqual("png", ((XSSFPictureData)pictures[pngIdx]).SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures[pngIdx]).Data)); //TODO finish usermodel API for XSSFPicture XSSFPicture p1 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), jpegIdx); Assert.IsNotNull(p1); XSSFPicture p2 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), wmfIdx); Assert.IsNotNull(p1); XSSFPicture p3 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), pngIdx); Assert.IsNotNull(p1); //check that the Added pictures are accessible After write wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb); IList pictures2 = wb.GetAllPictures(); Assert.AreEqual(3, pictures2.Count); Assert.AreEqual("jpeg", ((XSSFPictureData)pictures2[jpegIdx]).SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures2[jpegIdx]).Data)); Assert.AreEqual("wmf", ((XSSFPictureData)pictures2[wmfIdx]).SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures2[wmfIdx]).Data)); Assert.AreEqual("png", ((XSSFPictureData)pictures2[pngIdx]).SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures2[pngIdx]).Data)); }
public void multiRelationShips() { XSSFWorkbook wb = new XSSFWorkbook(); byte[] pic1Data = Encoding.UTF8.GetBytes("test jpeg data"); byte[] pic2Data = Encoding.UTF8.GetBytes("test png data"); List <XSSFPictureData> pictures = wb.GetAllPictures() as List <XSSFPictureData>; Assert.AreEqual(0, pictures.Count); int pic1 = wb.AddPicture(pic1Data, XSSFWorkbook.PICTURE_TYPE_JPEG); int pic2 = wb.AddPicture(pic2Data, XSSFWorkbook.PICTURE_TYPE_PNG); XSSFSheet sheet1 = wb.CreateSheet() as XSSFSheet; XSSFDrawing drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing; XSSFPicture shape1 = drawing1.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture; XSSFPicture shape2 = drawing1.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture; XSSFSheet sheet2 = wb.CreateSheet() as XSSFSheet; XSSFDrawing drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing; XSSFPicture shape3 = drawing2.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture; XSSFPicture shape4 = drawing2.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture; Assert.AreEqual(2, pictures.Count); wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; pictures = wb.GetAllPictures() as List <XSSFPictureData>; Assert.AreEqual(2, pictures.Count); sheet1 = wb.GetSheetAt(0) as XSSFSheet; drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing; XSSFPicture shape11 = (XSSFPicture)drawing1.GetShapes()[0]; Assert.IsTrue(Arrays.Equals(shape1.PictureData.Data, shape11.PictureData.Data)); XSSFPicture shape22 = (XSSFPicture)drawing1.GetShapes()[1]; Assert.IsTrue(Arrays.Equals(shape2.PictureData.Data, shape22.PictureData.Data)); sheet2 = wb.GetSheetAt(1) as XSSFSheet; drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing; XSSFPicture shape33 = (XSSFPicture)drawing2.GetShapes()[0]; Assert.IsTrue(Arrays.Equals(shape3.PictureData.Data, shape33.PictureData.Data)); XSSFPicture shape44 = (XSSFPicture)drawing2.GetShapes()[1]; Assert.IsTrue(Arrays.Equals(shape4.PictureData.Data, shape44.PictureData.Data)); }
public void Create() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(); XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data"); IList pictures = wb.GetAllPictures(); Assert.AreEqual(0, pictures.Count); int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG); Assert.AreEqual(1, pictures.Count); Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data)); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30); Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType); anchor.AnchorType = AnchorType.DontMoveAndResize; Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType); XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx); Assert.IsTrue(anchor.Equals(shape.GetAnchor())); Assert.IsNotNull(shape.PictureData); Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data)); CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0]; // STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs); }
public void TestRead() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx"); IList pictures = wb.GetAllPictures(); //wb.GetAllPictures() should return the same instance across multiple calls Assert.AreSame(pictures, wb.GetAllPictures()); Assert.AreEqual(5, pictures.Count); String[] ext = { "jpeg", "emf", "png", "emf", "wmf" }; String[] mimetype = { "image/jpeg", "image/x-emf", "image/png", "image/x-emf", "image/x-wmf" }; for (int i = 0; i < pictures.Count; i++) { Assert.AreEqual(ext[i], ((XSSFPictureData)pictures[i]).SuggestFileExtension()); Assert.AreEqual(mimetype[i], ((XSSFPictureData)pictures[i]).MimeType); } int num = pictures.Count; byte[] pictureData = { 0xA, 0xB, 0XC, 0xD, 0xE, 0xF }; int idx = wb.AddPicture(pictureData, PictureType.JPEG); Assert.AreEqual(num + 1, pictures.Count); //idx is 0-based index in the #pictures array Assert.AreEqual(pictures.Count - 1, idx); XSSFPictureData pict = (XSSFPictureData)pictures[idx]; Assert.AreEqual("jpeg", pict.SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(pictureData, pict.Data)); }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet1 = wb.CreateSheet("First Sheet"); //add picture data to this workbook. byte[] bytes = File.ReadAllBytes("../../data/aspose.png"); int pictureIdx = wb.AddPicture(bytes, PictureType.PNG); ICreationHelper helper = wb.GetCreationHelper(); // Create the drawing patriarch. This is the top level container for all shapes. IDrawing drawing = sheet1.CreateDrawingPatriarch(); // add a picture shape IClientAnchor anchor = helper.CreateClientAnchor(); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.Col1 = 3; anchor.Row1 = 2; IPicture pict = drawing.CreatePicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.Resize(); FileStream sw = File.Create("../../data/image.xlsx"); wb.Write(sw); sw.Close(); }
public void TestReadAnchors() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4); XSSFShape shape1 = Drawing.CreateTextbox(anchor1) as XSSFShape; Assert.IsNotNull(shape1); XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 5); XSSFShape shape2 = Drawing.CreateTextbox(anchor2) as XSSFShape; Assert.IsNotNull(shape2); int pictureIndex = wb.AddPicture(new byte[] { }, XSSFWorkbook.PICTURE_TYPE_PNG); XSSFClientAnchor anchor3 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 6); XSSFShape shape3 = Drawing.CreatePicture(anchor3, pictureIndex) as XSSFShape; Assert.IsNotNull(shape3); wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; sheet = wb.GetSheetAt(0) as XSSFSheet; Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; List <XSSFShape> shapes = Drawing.GetShapes(); Assert.AreEqual(3, shapes.Count); Assert.AreEqual(shapes[0].GetAnchor(), anchor1); Assert.AreEqual(shapes[1].GetAnchor(), anchor2); Assert.AreEqual(shapes[2].GetAnchor(), anchor3); Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb)); }
private void exportXLSX_Click(object sender, RoutedEventArgs e) { var newFile = @"newbook.core.xlsx"; using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write)) { // XSSFWorkbook : *.xlsx >= Excel2007 // HSSFWorkbook : *.xls < Excel2007 IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Sheet Name"); // 所有索引都从0开始 // 合并单元格 sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10)); var rowIndex = 0; IRow row = sheet1.CreateRow(rowIndex); //创建行 row.Height = 30 * 80; row.CreateCell(0).SetCellValue("this is content"); sheet1.AutoSizeColumn(0); //按照值的长短 自动调节列的大小 rowIndex++; // 插入图片 byte[] data = File.ReadAllBytes(@"image.jpg"); int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.JPEG); XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper; XSSFDrawing drawing = sheet1.CreateDrawingPatriarch() as XSSFDrawing; XSSFClientAnchor anchor = helper.CreateClientAnchor() as XSSFClientAnchor; anchor.Col1 = 10; anchor.Row1 = 0; XSSFPicture pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture; pict.Resize(); // 新建sheet var sheet2 = workbook.CreateSheet("Sheet2"); // 更改样式 var style1 = workbook.CreateCellStyle(); style1.FillForegroundColor = HSSFColor.Blue.Index2; style1.FillPattern = FillPattern.SolidForeground; var style2 = workbook.CreateCellStyle(); style2.FillForegroundColor = HSSFColor.Yellow.Index2; style2.FillPattern = FillPattern.SolidForeground; var cell2 = sheet2.CreateRow(0).CreateCell(0); cell2.CellStyle = style1; cell2.SetCellValue(0); cell2 = sheet2.CreateRow(1).CreateCell(0); cell2.CellStyle = style2; cell2.SetCellValue(1); //保存 workbook.Write(fs); } txtStatus.Text = "writing xlsx successful!"; }
/// <summary> /// 向sheet插入图片 /// </summary> /// <param name="workbook">工作簿</param> /// <param name="sheet">工作表</param> /// <param name="filePath">文件路径</param> /// <param name="row">第几行</param> /// <param name="col">第几列</param> /// <param name="suffix">后缀</param> private static void AddPicture(XSSFWorkbook workbook, ISheet sheet, string filePath, int row, int col, string suffix) { try { if (!string.IsNullOrEmpty(filePath)) { //转为本地路径 filePath = HttpContext.Current.Server.MapPath(filePath); if (File.Exists(filePath)) { var bytes = File.ReadAllBytes(filePath); if (bytes.Length > 0) { var imagedId = 0; var pictureType = new PictureType(); if (suffix.Equals("png", StringComparison.OrdinalIgnoreCase)) { pictureType = PictureType.PNG; } else if (suffix.Equals("jpg", StringComparison.OrdinalIgnoreCase)) { pictureType = PictureType.JPEG; } else { pictureType = PictureType.JPEG; } var patriarch = sheet.CreateDrawingPatriarch(); imagedId = workbook.AddPicture(bytes, pictureType); //处理照片位置,【图片左上角为(col, row)第row+1行col+1列,右下角为( col +1, row +1)第 col +1+1行row +1+1列,第三个参数为宽,第四个参数为高 var anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1); //dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一 //dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。 //dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。 //dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。 anchor.AnchorType = AnchorType.MoveDontResize; //anchor.AnchorType = 3; //load the picture and get the picture index in the workbook var picture = (XSSFPicture)patriarch.CreatePicture(anchor, imagedId); //Reset the image to the original size. //picture.Resize(); //Note: Resize will reset client anchor you set. 这句话一定不要,这是用图片原始大小来显示 //picture.Resize(0.5); picture.LineStyle = LineStyle.DashDotGel; } } } } catch (Exception ex) { LogUtil.WriteException(ex); //此处采用跳过的方式 //throw ex; } }
public void IncrementShapeId() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(); XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30); byte[] jpegData = Encoding.UTF8.GetBytes("picture1"); int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG); XSSFPicture shape1 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx); Assert.AreEqual((uint)1, shape1.GetCTPicture().nvPicPr.cNvPr.id); jpegData = Encoding.UTF8.GetBytes("picture2"); jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG); XSSFPicture shape2 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx); Assert.AreEqual((uint)2, shape2.GetCTPicture().nvPicPr.cNvPr.id); }
private void setPic(XSSFWorkbook workbook, XSSFDrawing patriarch, string path, ISheet sheet, int rowline, int col, int rowMergerPlus = 0, int colMergerPlus = 0) { if (string.IsNullOrEmpty(path)) { return; } byte[] bytes = System.IO.File.ReadAllBytes(path); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释 XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, rowline, col + colMergerPlus, rowline + rowMergerPlus); //把图片插到相应的位置 XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); }
/// <summary> /// .xlsx后缀的Excel文件添加图片 /// </summary> /// <param name="excelPath"></param> /// <param name="imgPath"></param> public static void InsertImageToXLSXExcel(string excelPath, string imgPath) { try { using (FileStream fs = new FileStream(excelPath, FileMode.Open))//获取指定Excel文件流 { //创建工作簿 XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs); //获取第一个工作表(下标从0起) XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0)); //获取指定图片的字节流 byte[] bytes = System.IO.File.ReadAllBytes(imgPath); //将图片添加到工作簿中,返回值为该图片在工作表中的索引(从0开始) //图片所在工作簿索引理解:如果原Excel中没有图片,那执行下面的语句后,该图片为Excel中的第1张图片,其索引为0; //同理,如果原Excel中已经有1张图片,执行下面的语句后,该图片为Excel中的第2张图片,其索引为1; int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.JPEG); //创建画布 XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch(); //设置图片坐标与大小 //函数原型:XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2); //坐标(col1,row1)表示图片左上角所在单元格的位置,均从0开始,比如(5,2)表示(第五列,第三行),即F3;注意:图片左上角坐标与(col1,row1)单元格左上角坐标重合 //坐标(col2,row2)表示图片右下角所在单元格的位置,均从0开始,比如(10,3)表示(第十一列,第四行),即K4;注意:图片右下角坐标与(col2,row2)单元格左上角坐标重合 //坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移);(dx1,dy1)的最大值为(1023, 255),为一个单元格的大小 //坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移);(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小 //注意:目前测试发现,对于.xlsx后缀的Excel文件,偏移量设置(dx1,dy1)(dx2,dy2)无效;只会对.xls生效 XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 5, 2, 10, 3); //正式在指定位置插入图片 XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //创建一个新的Excel文件流,可以和原文件名不一样, //如果不一样,则会创建一个新的Excel文件;如果一样,则会覆盖原文件 FileStream file = new FileStream(excelPath, FileMode.Create); //将已插入图片的Excel流写入新创建的Excel中 xssfworkbook.Write(file); //关闭工作簿 xssfworkbook.Close(); } } catch (Exception ex) { throw new Exception(ex.Message); } }
private static void InsertImage(XSSFWorkbook workbook, ISheet sheet, byte[] data, int row, int column) { try { int picInd = workbook.AddPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG); IDrawing patriarch = sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, column, row, column + 1, row + 1) { AnchorType = AnchorType.MoveAndResize }; XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, picInd); //Reset the image to the original size. //picture.Resize(); //Note: Resize will reset client anchor you set. picture.LineStyle = LineStyle.DashDotGel; } catch { //ignore } }
/// <summary> /// 导出Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="data">数据</param> /// <param name="headDict">头部信息</param> /// <param name="imgColumnList">图片列</param> /// <param name="sheetName">Sheet名</param> /// <param name="showSrNo">是否要加上序号</param> /// <returns></returns> public static byte[] ExportExcel <T>(List <T> data, Dictionary <string, string> headDict, List <string> imgColumnList, string sheetName = "", bool showSrNo = false) { if (imgColumnList == null) { imgColumnList = new List <string>(); } DataTable dt = ListToDataTable <T>(data); byte[] result = null; List <string> keyList = new List <string>(); if (showSrNo) { keyList.Add("RowNum"); dt.Columns.Add("RowNum"); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["RowNum"] = i + 1; } } //通过键的集合取 foreach (string key in headDict.Keys) { keyList.Add(key); } IWorkbook workbook = new XSSFWorkbook(); //设置宽度 ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.VerticalAlignment = VerticalAlignment.Center; //垂直居中 style.Alignment = HorizontalAlignment.Center; //水平对齐; if (showSrNo) { headDict.Add("RowNum", "序号"); } ISheet sheet = sheetName.IsNullOrEmpty() ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(sheetName); //表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < keyList.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(headDict[keyList[i]]); cell.CellStyle = style; } //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); for (int j = 0; j < keyList.Count; j++) { if (imgColumnList.Contains(keyList[j])) { //插入图片 byte[] bytes = HttpMethods.GetImage(dt.Rows[i][keyList[j]].ToString()); if (bytes != null) { ICell cell = row1.CreateCell(j); cell.CellStyle = style; try { int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG); IDrawing patriarch = sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 100, 100, j, i + 1, j + 1, i + 2); //##处理照片位置,【图片左上角为(col, row)第row+1行col+1列,右下角为( col +1, row +1)第 col +1+1行row +1+1列,宽为100,高为50 XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } catch { cell.SetCellValue(dt.Rows[i][keyList[j]].ToString()); } } } else { ICell cell = row1.CreateCell(j); cell.SetCellValue(dt.Rows[i][keyList[j]].ToString()); cell.CellStyle = style; } } } //自适应列宽 for (int i = 0; i < keyList.Count; i++) { sheet.AutoSizeColumn(i, true); } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); result = ms.GetBuffer(); ms.Close(); }; return(result); }
/// <summary> /// 将datatable导出为excel /// 图片默认显示在excel 第二行最后一列 /// </summary> /// <param name="table">数据源</param> /// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param> /// <param name="sheetName">工作簿名称</param> /// <param name="picBytes">导出图片字节流</param> /// <param name="mergedRegion">合并单元格信息:null不合并单元格</param> /// <returns></returns> public static MemoryStream ExportToExcel2007(DataTable table, List <Tuple <string, string, int> > excelInfo, string sheetName, byte[] picBytes, List <CellRangeAddress> mergedRegion) { MemoryStream ms = new MemoryStream(); try { using (table) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); for (int i = 0; i < excelInfo.Count; i++) { sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256); } IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < excelInfo.Count; i++) { headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1); } int rowIndex = 1; foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); for (int i = 0; i < excelInfo.Count; i++) { dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString()); } rowIndex++; } //合并单元格 if (mergedRegion != null && mergedRegion.Count > 0) { foreach (CellRangeAddress cellRangeAddress in mergedRegion) { //设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域 //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列 sheet.AddMergedRegion(cellRangeAddress); ICellStyle style = workbook.CreateCellStyle(); //设置单元格的样式:水平对齐居中 style.Alignment = HorizontalAlignment.Center; //将新的样式赋给单元格 var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn); cell.CellStyle = style; } } //插入图片 if (picBytes != null && picBytes.Length > 0) { var row1 = 2; var col1 = excelInfo.Count + 1; /* Add Picture to Workbook, Specify picture type as PNG and Get an Index */ int pictureIdx = workbook.AddPicture(picBytes, PictureType.PNG); //添加图片 /* Create the drawing container */ IDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); /* Create an anchor point */ IClientAnchor anchor = new XSSFClientAnchor(1, 1, 0, 0, col1, row1, col1 + 1, row1 + 1); /* Invoke createPicture and pass the anchor point and ID */ IPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx); /* Call resize method, which resizes the image */ picture.Resize(); picBytes = null; } workbook.Write(ms); workbook.Close(); } } catch (Exception ex) { ms = null; } return(ms); }
///// <summary> ///// 清除时间 ///// </summary> ///// <param name="sender"></param> ///// <param name="e"></param> //private void ClearDatePickerTime(object sender, MouseButtonEventArgs e) //{ // DatePicker dp = sender as DatePicker; // dp.Text = ""; //} /// <summary> /// 导出excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private async void ExportExcelFile_Click(object sender, RoutedEventArgs e) { try { string saveExcelPath = ConfigurationManager.AppSettings["Excel保存地址"]; if (string.IsNullOrEmpty(saveExcelPath)) { saveExcelPath = System.Windows.Forms.Application.StartupPath + @"\比对记录导出"; } else { saveExcelPath += @"\比对记录导出"; } await Task.Run( () => { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; XSSFCellStyle style2 = (XSSFCellStyle)workbook.CreateCellStyle(); style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style2.BorderBottom = BorderStyle.Thin; style2.BorderLeft = BorderStyle.Thin; style2.BorderRight = BorderStyle.Thin; style2.BorderTop = BorderStyle.Thin; XSSFFont font = (XSSFFont)workbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; style2.SetFont(font); XSSFCellStyle style0 = (XSSFCellStyle)workbook.CreateCellStyle(); style0.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style0.BorderBottom = BorderStyle.Thin; style0.BorderLeft = BorderStyle.Thin; style0.BorderRight = BorderStyle.Thin; style0.BorderTop = BorderStyle.Thin; IDataFormat dataformat = workbook.CreateDataFormat(); style0.DataFormat = dataformat.GetFormat("yyyy年MM月dd日 h:mm:ss 上午/下午"); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("比对记录"); int row = 0; List <MyCmpFaceLogWidthImgModel> allCmpInfo = new List <MyCmpFaceLogWidthImgModel>(); allCmpInfo = GetCmpFaceLogByPage(1); foreach (var item in allCmpInfo) { if (row == 0) { sheet.CreateRow(row).CreateCell(0).SetCellValue("序号"); sheet.GetRow(row).CreateCell(1).SetCellValue("抓拍通道"); sheet.GetRow(row).CreateCell(2).SetCellValue("抓拍时间"); sheet.GetRow(row).CreateCell(3).SetCellValue("模版姓名"); sheet.GetRow(row).CreateCell(4).SetCellValue("注册类型"); sheet.GetRow(row).CreateCell(5).SetCellValue("相似度"); sheet.GetRow(row).CreateCell(6).SetCellValue("抓拍照片"); sheet.GetRow(row).CreateCell(7).SetCellValue("模版照片"); sheet.GetRow(row).GetCell(0).CellStyle = style2; sheet.GetRow(row).GetCell(1).CellStyle = style2; sheet.GetRow(row).GetCell(2).CellStyle = style2; sheet.GetRow(row).GetCell(3).CellStyle = style2; sheet.GetRow(row).GetCell(4).CellStyle = style2; sheet.GetRow(row).GetCell(5).CellStyle = style2; sheet.GetRow(row).GetCell(6).CellStyle = style2; sheet.GetRow(row).GetCell(7).CellStyle = style2; sheet.SetColumnWidth(2, 30 * 256); sheet.SetColumnWidth(6, 30 * 256); sheet.SetColumnWidth(7, 30 * 256); row++; } sheet.CreateRow(row).CreateCell(0).SetCellValue(row); sheet.GetRow(row).Height = 100 * 40; sheet.GetRow(row).CreateCell(1).SetCellValue(item.channelName); sheet.GetRow(row).CreateCell(2).SetCellValue(item.time); sheet.GetRow(row).CreateCell(3).SetCellValue(item.name); sheet.GetRow(row).CreateCell(4).SetCellValue(item.type); sheet.GetRow(row).CreateCell(5).SetCellValue(item.score); sheet.GetRow(row).CreateCell(6).SetCellValue(""); sheet.GetRow(row).CreateCell(7).SetCellValue(""); sheet.GetRow(row).GetCell(0).CellStyle = style; sheet.GetRow(row).GetCell(1).CellStyle = style; sheet.GetRow(row).GetCell(2).CellStyle = style0; sheet.GetRow(row).GetCell(3).CellStyle = style; sheet.GetRow(row).GetCell(4).CellStyle = style; sheet.GetRow(row).GetCell(5).CellStyle = style; sheet.GetRow(row).GetCell(6).CellStyle = style; sheet.GetRow(row).GetCell(7).CellStyle = style; if (true) { XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch(); List <byte[]> capImage = new List <byte[]>(); capImage = thirft.QueryCmpLogImageH(item.ID, exportCurrDay); if (capImage.Count > 0) { byte[] bytes = capImage[0]; int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 6, row, 7, row + 1); XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } var faceObject = thirft.QueryFaceObj(item.tcUuid); if (faceObject.Count > 0 && faceObject[0].Tmplate.Count > 0) { byte[] bytes2 = faceObject[0].Tmplate[0].Img; int pictureIdx2 = workbook.AddPicture(bytes2, PictureType.JPEG); XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 7, row, 8, row + 1); XSSFPicture pict2 = (XSSFPicture)patriarch.CreatePicture(anchor2, pictureIdx2); } } row++; Thread.Sleep(1); } FileStream fs = new FileStream(saveExcelPath + ".xlsx", FileMode.Create, FileAccess.Write); workbook.Write(fs); workbook = null; }); MB_MODULES.Views.MyMessage.showYes("导出完成!"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public int AddPicture(byte[] pictureData, PictureType format) { return(XssfWorkbook.AddPicture(pictureData, format)); }
public async Task <ActionResult> UploadScoreAjaxDown(string disCode, string startTime, string endTime, string status, string pid, string disname, string name, string visitTypeName, string visitDateTime, string visitType, string batch) { try { string result = await CommonHelper.GetHttpClient().GetStringAsync(CommonHelper.Current.GetAPIBaseUrl + "Tour/GetTaskListByDisIdForExcel/" + disCode + "/" + startTime + "/" + endTime + "/" + status + "/" + pid); var apiResult = CommonHelper.DecodeString <APIResult>(result); if (apiResult.ResultCode == ResultType.Success) { ExcelResult er = CommonHelper.DecodeString <ExcelResult>(apiResult.Body); List <ResultExcelDto> list = er.ResultList; string discode = list.FirstOrDefault().DisCode; string pDisInfo = list.FirstOrDefault().PId; string aDisName = string.Empty; string rDisName = string.Empty; string eDisName = string.Empty; if (!string.IsNullOrWhiteSpace(pDisInfo) && pDisInfo.Split(',').Length == 3) { aDisName = pDisInfo.Split(',')[0]; rDisName = pDisInfo.Split(',')[1]; eDisName = pDisInfo.Split(',')[2]; } List <LosePic> lpList = er.LPicList; if (lpList.Count % 2 == 1) { lpList.Add(new LosePic()); } //未通过体系 List <ResultExcelDto> unlist2 = (from a in list where a.PassYN == "0" select a).ToList(); List <string> tiId = (from a in unlist2 select a.TITitle).Distinct().ToList(); //任务卡得分 var ts = (from a in list select new { TCId = a.TCId, TCTitle = a.TCTitle, TCWeight = a.TCWeight, ItemScore = a.ItemScore, ItemQty = a.ItemQty }).Distinct().ToList(); string totalScore = list.FirstOrDefault().STScore; var uploads = Path.Combine(_environment.WebRootPath, "Template"); //var newFile = Path.Combine(uploads, list[0].PTitle + "_" + discode + "_" + disname + "_" + DateTime.Now.ToString("yyyyMMdd hhmmss") + ".xlsx"); var reportname = list[0].PTitle + "_" + visitTypeName + "_" + discode + "_" + disname; var newFile = Path.Combine(uploads, reportname + ".xlsx"); if (!Directory.Exists(uploads)) { Directory.CreateDirectory(uploads); } using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write)) { IWorkbook workbook = new XSSFWorkbook(); //ISheet sheet1 = workbook.CreateSheet("检核结果"); ISheet sheet1 = workbook.CreateSheet(reportname); sheet1.FitToPage = true; sheet1.PrintSetup.FitWidth = 1; sheet1.PrintSetup.FitHeight = 0; sheet1.PrintSetup.PaperSize = 9; sheet1.SetMargin(MarginType.RightMargin, 0); //sheet1.PrintSetup.NoOrientation = true; IFont font_b = workbook.CreateFont(); font_b.FontName = "微软雅黑"; font_b.FontHeightInPoints = 10; IFont font_c = workbook.CreateFont(); font_c.FontName = "微软雅黑"; font_c.FontHeightInPoints = 10; font_c.Color = HSSFColor.White.Index; font_c.IsBold = true; byte[] rgb5 = new byte[3] { 217, 217, 217 }; byte[] rgb6 = new byte[3] { 221, 217, 196 }; byte[] rgb7 = new byte[3] { 32, 55, 100 }; var style1 = (XSSFCellStyle)workbook.CreateCellStyle(); //style1.FillForegroundColor = HSSFColor.Grey25Percent.Index; byte[] rgb = new byte[3] { 116, 163, 210 }; style1.SetFillForegroundColor(new XSSFColor(rgb)); style1.FillPattern = FillPattern.SolidForeground; style1.Alignment = HorizontalAlignment.Center; style1.BorderLeft = BorderStyle.Thin; style1.BorderBottom = BorderStyle.Thin; style1.BorderRight = BorderStyle.Thin; style1.BorderTop = BorderStyle.Thin; style1.SetFont(font_b); var style_Stand = workbook.CreateCellStyle(); style_Stand.Alignment = HorizontalAlignment.Left; style_Stand.WrapText = true; style_Stand.SetFont(font_b); style_Stand.BorderLeft = BorderStyle.Thin; style_Stand.BorderBottom = BorderStyle.Thin; style_Stand.BorderRight = BorderStyle.Thin; style_Stand.BorderTop = BorderStyle.Thin; var style_b2 = workbook.CreateCellStyle(); style_b2.WrapText = true; style_b2.VerticalAlignment = VerticalAlignment.Center; style_b2.SetFont(font_b); style_b2.BorderLeft = BorderStyle.Thin; style_b2.BorderBottom = BorderStyle.Thin; style_b2.BorderRight = BorderStyle.Thin; style_b2.BorderTop = BorderStyle.Thin; var style_b3 = workbook.CreateCellStyle(); style_b3.WrapText = true; style_b3.VerticalAlignment = VerticalAlignment.Center; style_b3.SetFont(font_b); var style2 = workbook.CreateCellStyle(); style2.Alignment = HorizontalAlignment.Left; style2.SetFont(font_b); style2.BorderLeft = BorderStyle.Thin; style2.BorderBottom = BorderStyle.Thin; style2.BorderRight = BorderStyle.Thin; style2.BorderTop = BorderStyle.Thin; var style3 = workbook.CreateCellStyle(); style3.Alignment = HorizontalAlignment.Right; style3.SetFont(font_b); var style4 = workbook.CreateCellStyle(); style4.Alignment = HorizontalAlignment.Center; style4.VerticalAlignment = VerticalAlignment.Center; style4.SetFont(font_b); style4.BorderLeft = BorderStyle.Thin; style4.BorderBottom = BorderStyle.Thin; style4.BorderRight = BorderStyle.Thin; style4.BorderTop = BorderStyle.Thin; var style5 = workbook.CreateCellStyle(); style5.Alignment = HorizontalAlignment.Center; style5.VerticalAlignment = VerticalAlignment.Center; style5.SetFont(font_b); var style6 = workbook.CreateCellStyle(); style6.Alignment = HorizontalAlignment.Center; style6.VerticalAlignment = VerticalAlignment.Center; style6.SetFont(font_b); var style_t = (XSSFCellStyle)workbook.CreateCellStyle(); style_t.Alignment = HorizontalAlignment.Center; style_t.SetFillForegroundColor(new XSSFColor(rgb)); //style_t.FillForegroundColor = HSSFColor.Grey25Percent.Index; style_t.FillPattern = FillPattern.SolidForeground; IFont font_t = workbook.CreateFont(); font_t.FontHeightInPoints = 18; font_t.FontName = "微软雅黑"; style_t.SetFont(font_t); //标题 IRow row_t = sheet1.CreateRow(0); ICell cell_t = row_t.CreateCell(0); cell_t.SetCellValue("经销商报告"); CellRangeAddress region_t = new CellRangeAddress(0, 0, 0, 6); cell_t.CellStyle = style_t; sheet1.AddMergedRegion(region_t); IRow row = sheet1.CreateRow(1); ICell cell = row.CreateCell(0); row.CreateCell(1).CellStyle = style2; cell.SetCellValue("经销商代码:" + discode); CellRangeAddress region = new CellRangeAddress(1, 1, 0, 1); cell.CellStyle = style2; sheet1.AddMergedRegion(region); ICell cell_11 = row.CreateCell(2); row.CreateCell(3).CellStyle = style2; row.CreateCell(4).CellStyle = style2; row.CreateCell(5).CellStyle = style2; row.CreateCell(6).CellStyle = style2; cell_11.SetCellValue("经销商名称:" + disname); CellRangeAddress region_1 = new CellRangeAddress(1, 1, 2, 6); cell_11.CellStyle = style2; sheet1.AddMergedRegion(region_1); IRow row1 = sheet1.CreateRow(2); ICell cel2 = row1.CreateCell(0); row1.CreateCell(1).CellStyle = style2; cel2.SetCellValue("所属小区:" + aDisName); cel2.CellStyle = style2; CellRangeAddress region2 = new CellRangeAddress(2, 2, 0, 1); sheet1.AddMergedRegion(region2); ICell cel2_1 = row1.CreateCell(2); row1.CreateCell(3).CellStyle = style2; row1.CreateCell(4).CellStyle = style2; cel2_1.SetCellValue(rDisName == "-" ? "所属大区:" + eDisName : "所属区域:" + rDisName); cel2_1.CellStyle = style2; CellRangeAddress region2_1 = new CellRangeAddress(2, 2, 2, 4); sheet1.AddMergedRegion(region2_1); ICell cel2_2 = row1.CreateCell(5); row1.CreateCell(6).CellStyle = style2; cel2_2.SetCellValue(rDisName == "-" ? "" : (eDisName == "-" ? "" : "所属大区:" + eDisName)); cel2_2.CellStyle = style2; CellRangeAddress region2_2 = new CellRangeAddress(2, 2, 5, 6); sheet1.AddMergedRegion(region2_2); IRow row0 = sheet1.CreateRow(3); ICell cel0 = row0.CreateCell(0); row0.CreateCell(1).CellStyle = style2; cel0.SetCellValue("检核类型:" + visitTypeName); cel0.CellStyle = style2; CellRangeAddress region3 = new CellRangeAddress(3, 3, 0, 1); sheet1.AddMergedRegion(region3); ICell cel0_1 = row0.CreateCell(2); row0.CreateCell(3).CellStyle = style2; row0.CreateCell(4).CellStyle = style2; cel0_1.SetCellValue("巡检月份:" + (list.Count == 0 ? "" : list[0].PTitle)); cel0_1.CellStyle = style2; CellRangeAddress region3_1 = new CellRangeAddress(3, 3, 2, 4); sheet1.AddMergedRegion(region3_1); ICell cel0_2 = row0.CreateCell(5); row0.CreateCell(6).CellStyle = style2; cel0_2.SetCellValue("检核时间:" + visitDateTime); cel0_2.CellStyle = style2; CellRangeAddress region3_2 = new CellRangeAddress(3, 3, 5, 6); sheet1.AddMergedRegion(region3_2); if (visitType == "D" || visitType == "E" || visitType == "F") { //DMS 和 卖车宝 string plansPosition = await CommonHelper.GetHttpClient().GetStringAsync(string.Format("{0}/Tour/GetPlansPosition?batch={1}&disId={2}", CommonHelper.Current.GetAPIBaseUrl, batch, disCode)); APIResult planAPIResult = CommonHelper.DecodeString <APIResult>(plansPosition); List <Dictionary <string, object> > dicList = CommonHelper.DecodeString <List <Dictionary <string, object> > >(planAPIResult.Body); if (dicList != null && dicList.Count > 0) { Dictionary <string, object> dic = dicList[0]; IRow row4 = sheet1.CreateRow(4); ICell[] cells = new ICell[7]; for (int i = 0; i < 7; i++) { cells[i] = row4.CreateCell(i); cells[i].CellStyle = style2; } string txtManager = "销售经理:" + dic["SalesManager"]; string txtConsultant = "销售顾问:" + dic["SalesConsultant"]; string txtInside = "销售内勤:" + dic["SalesInside"]; if (visitType == "F") { txtManager = "客户经理:" + dic["SalesManager"]; txtConsultant = "零件经理:" + dic["SalesConsultant"]; txtInside = "保修专员:" + dic["SalesInside"]; } if (visitType == "D" || visitType == "F") { cells[0].SetCellValue(txtManager); cells[2].SetCellValue(txtConsultant); cells[5].SetCellValue(txtInside); //合并单元格 sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 0, 1)); sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 2, 4)); sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 5, 6)); } else { cells[0].SetCellValue(txtManager); cells[3].SetCellValue(txtConsultant); //合并单元格 sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 0, 2)); sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 3, 6)); } } } var style_sub = (XSSFCellStyle)workbook.CreateCellStyle(); style_sub.Alignment = HorizontalAlignment.Center; style_sub.SetFillForegroundColor(new XSSFColor(rgb)); //style_sub.FillForegroundColor = HSSFColor.Grey25Percent.Index; style_sub.FillPattern = FillPattern.SolidForeground; style_sub.SetFont(font_b); var style_5 = (XSSFCellStyle)workbook.CreateCellStyle(); style_5.Alignment = HorizontalAlignment.Left; style_5.SetFillForegroundColor(new XSSFColor(rgb5)); style_5.FillPattern = FillPattern.SolidForeground; style_5.BorderLeft = BorderStyle.Thin; style_5.BorderBottom = BorderStyle.Thin; style_5.BorderRight = BorderStyle.Thin; style_5.BorderTop = BorderStyle.Thin; style_5.SetFont(font_b); var style_6 = (XSSFCellStyle)workbook.CreateCellStyle(); style_6.Alignment = HorizontalAlignment.Center; style_6.SetFillForegroundColor(new XSSFColor(rgb6)); style_6.FillPattern = FillPattern.SolidForeground; style_6.BorderLeft = BorderStyle.Thin; style_6.BorderBottom = BorderStyle.Thin; style_6.BorderRight = BorderStyle.Thin; style_6.BorderTop = BorderStyle.Thin; style_6.SetFont(font_b); var style_7 = (XSSFCellStyle)workbook.CreateCellStyle(); style_7.Alignment = HorizontalAlignment.Center; style_7.SetFillForegroundColor(new XSSFColor(rgb7)); style_7.FillPattern = FillPattern.SolidForeground; style_7.BorderLeft = BorderStyle.Thin; style_7.BorderBottom = BorderStyle.Thin; style_7.BorderRight = BorderStyle.Thin; style_7.BorderTop = BorderStyle.Thin; style_7.SetFont(font_c); sheet1.CreateRow(5); IRow row_score = sheet1.CreateRow(6); ICell cell_score = row_score.CreateCell(0); cell_score.SetCellValue("检核结果汇总"); CellRangeAddress region_score = new CellRangeAddress(6, 6, 0, 4); cell_score.CellStyle = style_sub; sheet1.AddMergedRegion(region_score); int tCount = 7; int sCount = tCount; IRow rows = sheet1.CreateRow(sCount); //任务卡名称 ICell cell_s1 = rows.CreateCell(0); cell_s1.SetCellValue("模块"); cell_s1.CellStyle = style1; ICell cell_s2 = rows.CreateCell(1); cell_s2.SetCellValue(""); cell_s2.CellStyle = style1; CellRangeAddress region_s1 = new CellRangeAddress(sCount, sCount, 0, 1); sheet1.AddMergedRegion(region_s1); //指标数量 ICell cell_s3 = rows.CreateCell(2); cell_s3.SetCellValue("指标数量"); cell_s3.CellStyle = style1; CellRangeAddress region_s2 = new CellRangeAddress(sCount, sCount, 2, 2); sheet1.AddMergedRegion(region_s2); //权重 ICell cell_s4 = rows.CreateCell(3); cell_s4.SetCellValue("权重"); cell_s4.CellStyle = style1; CellRangeAddress region_s3 = new CellRangeAddress(sCount, sCount, 3, 3); sheet1.AddMergedRegion(region_s3); //得分 ICell cell_s5 = rows.CreateCell(4); cell_s5.SetCellValue("得分"); cell_s5.CellStyle = style1; CellRangeAddress region_s4 = new CellRangeAddress(sCount, sCount, 4, 4); sheet1.AddMergedRegion(region_s4); sCount++; if (ts != null && ts.Count > 0) { for (int i = 0; i < ts.Count; i++) { IRow row_un = sheet1.CreateRow(sCount + i); ICell cell_0 = row_un.CreateCell(0); cell_0.SetCellValue(ts[i].TCTitle); cell_0.CellStyle = style_5; ICell cell_1 = row_un.CreateCell(1); cell_1.SetCellValue(ts[i].TCTitle); cell_1.CellStyle = style_5; CellRangeAddress r_s1 = new CellRangeAddress(sCount + i, sCount + i, 0, 1); sheet1.AddMergedRegion(r_s1); ICell cell_4 = row_un.CreateCell(2); cell_4.SetCellValue(ts[i].ItemQty); cell_4.CellStyle = style_6; CellRangeAddress r_s2 = new CellRangeAddress(sCount + i, sCount + i, 2, 2); sheet1.AddMergedRegion(r_s2); ICell cell_5 = row_un.CreateCell(3); decimal rWeight = 0; decimal.TryParse(ts[i].TCWeight, out rWeight); cell_5.SetCellValue(Math.Round(rWeight, 2) * 100 + "%"); cell_5.CellStyle = style_6; CellRangeAddress r_s3 = new CellRangeAddress(sCount + i, sCount + i, 3, 3); sheet1.AddMergedRegion(r_s3); ICell cell_6 = row_un.CreateCell(4); cell_6.SetCellValue(ts[i].ItemScore); cell_6.CellStyle = style_6; CellRangeAddress r_s4 = new CellRangeAddress(sCount + i, sCount + i, 4, 4); sheet1.AddMergedRegion(r_s4); } } if (ts != null && ts.Count >= 0) { sCount = ts.Count + tCount + 1; } IRow rowst = sheet1.CreateRow(sCount); //任务卡名称 ICell cell_st1 = rowst.CreateCell(0); cell_st1.SetCellValue("总分"); cell_st1.CellStyle = style_7; ICell cell_st2 = rowst.CreateCell(1); cell_st2.SetCellValue(""); cell_st2.CellStyle = style_7; ICell cell_st3 = rowst.CreateCell(2); cell_st3.SetCellValue(""); cell_st3.CellStyle = style_7; ICell cell_st4 = rowst.CreateCell(3); cell_st4.SetCellValue(""); cell_st4.CellStyle = style_7; CellRangeAddress region_st1 = new CellRangeAddress(sCount, sCount, 0, 3); sheet1.AddMergedRegion(region_st1); //指标数量 ICell cell_st5 = rowst.CreateCell(4); cell_st5.SetCellValue(totalScore); cell_st5.CellStyle = style_7; CellRangeAddress region_st2 = new CellRangeAddress(sCount, sCount, 4, 4); sheet1.AddMergedRegion(region_st2); /*** * IRow row_sub = sheet1.CreateRow(5); * ICell cell_sub = row_sub.CreateCell(0); * cell_sub.SetCellValue("检核结果汇总"); * CellRangeAddress region_sub = new CellRangeAddress(5, 5, 0, 6); * cell_sub.CellStyle = style_sub; * sheet1.AddMergedRegion(region_sub); * * IRow row_6 = sheet1.CreateRow(6); * ICell cell_6 = row_6.CreateCell(0); * cell_6.SetCellValue("未达标汇总:"); * cell_6.CellStyle = style2; * CellRangeAddress region_6 = new CellRangeAddress(6, 6, 0, 0); * sheet1.AddMergedRegion(region_6); * * if (tiId != null && tiId.Count > 0) * { * for (int i = 0; i < tiId.Count; i++) * { * IRow row_un = sheet1.CreateRow(7 + i); * ICell cell_un = row_un.CreateCell(0); * cell_un.SetCellValue((i + 1) + "、" + tiId[i]); * cell_un.CellStyle = style2; * } * } *****/ int pw = 1; int tcw = 1; int tiw = 1; int csw = 1; int rw = 1; if (list != null && list.Count > 0) { for (int k = 0; k < list.Count; k++) { if ((list[k].PTitle == null ? 0 : list[k].PTitle.Length) > pw) { pw = list[k].PTitle.Length; } if ((list[k].TCTitle == null ? 0 : list[k].TCTitle.Length) > tcw) { tcw = list[k].TCTitle.Length; } if ((list[k].TITitle == null ? 0 : list[k].TITitle.Length) > tiw) { tiw = list[k].TITitle.Length; } if ((list[k].CSTitle == null ? 0 : list[k].CSTitle.Length) > csw) { csw = list[k].CSTitle.Length; } if ((list[k].Remarks == null ? 0 : list[k].Remarks.Length) > rw) { rw = list[k].Remarks.Length; } } } //int dataCount = 10; //if (tiId != null && tiId.Count >= 0) //{ // dataCount = tiId.Count + 10; //} int dataCount = sCount + 2; IRow row3 = sheet1.CreateRow(dataCount); //任务卡名称 ICell cell6 = row3.CreateCell(0); cell6.SetCellValue("模块"); cell6.CellStyle = style1; CellRangeAddress region6 = new CellRangeAddress(dataCount, dataCount, 0, 0); sheet1.AddMergedRegion(region6); sheet1.SetColumnWidth(0, 7 * 600); //体系名称 ICell cell7 = row3.CreateCell(1); cell7.SetCellValue("审计项目"); cell7.CellStyle = style1; CellRangeAddress region7 = new CellRangeAddress(dataCount, dataCount, 1, 1); sheet1.AddMergedRegion(region7); sheet1.SetColumnWidth(1, 7 * 600); //体系权重 ICell cell8 = row3.CreateCell(2); cell8.SetCellValue("权重"); cell8.CellStyle = style1; CellRangeAddress region8 = new CellRangeAddress(dataCount, dataCount, 2, 2); sheet1.AddMergedRegion(region8); sheet1.SetColumnWidth(2, 13 * 300); //检查标准 ICell cell9 = row3.CreateCell(3); cell9.SetCellValue("检查标准"); cell9.CellStyle = style1; //ICell cell10 = row3.CreateCell(4); //cell10.SetCellValue(""); //cell10.CellStyle = style1; CellRangeAddress region9 = new CellRangeAddress(dataCount, dataCount, 3, 3); sheet1.AddMergedRegion(region9); sheet1.SetColumnWidth(3, (13 * 600)); //sheet1.SetColumnWidth(4, (15 * 300)); //sheet1.SetColumnWidth(4, (15 * 300)); //结果 ICell cell_10 = row3.CreateCell(4); cell_10.SetCellValue("是否达成"); cell_10.CellStyle = style1; CellRangeAddress region10 = new CellRangeAddress(dataCount, dataCount, 4, 4); sheet1.AddMergedRegion(region10); sheet1.SetColumnWidth(4, 13 * 300); //得分 ICell cell11 = row3.CreateCell(5); cell11.SetCellValue("得分"); cell11.CellStyle = style1; CellRangeAddress region11 = new CellRangeAddress(dataCount, dataCount, 5, 5); sheet1.AddMergedRegion(region11); sheet1.SetColumnWidth(5, 9 * 300); //备注 ICell cell12 = row3.CreateCell(6); cell12.SetCellValue("备注"); cell12.CellStyle = style1; CellRangeAddress region12 = new CellRangeAddress(dataCount, dataCount, 6, 6); sheet1.AddMergedRegion(region12); sheet1.SetColumnWidth(6, 15 * 300); int firstRow1 = dataCount + 1; int lastRow1 = 0; int sumCnt1 = 0; int firstRow2 = dataCount + 1; int lastRow2 = 0; int sumCnt2 = 0; int firstRow3 = dataCount + 1; int lastRow3 = 0; int sumCnt3 = 0; int firstRow4 = dataCount + 1; int lastRow4 = 0; int sumCnt4 = 0; int firstRow5 = dataCount + 1; int lastRow5 = 0; int sumCnt5 = 0; //数据的动态加载 for (int i = 0; i < list.Count; i++) { object r = "r_" + i; r = sheet1.CreateRow(dataCount + 1 + i); int cnum = 0; //计划任务名称 //任务卡名称 object c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].TCTitle); (c as ICell).CellStyle = style_b2; if (i == sumCnt1) { int cnt = (from l1 in list where l1.TCId == list[i].TCId select l1).Count(); sumCnt1 += cnt; lastRow1 = firstRow1 + cnt - 1; if (list[i].CSId != null) { sheet1.AddMergedRegion(new CellRangeAddress(firstRow1, lastRow1, 0, 0)); } firstRow1 = lastRow1 + 1; } cnum++; //体系名称 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].TITitle); (c as ICell).CellStyle = style_b2; //sheet1.AddMergedRegion(new CellRangeAddress(5 + i, 5 + i, 7 + cnum, 7 + cnum)); if (i == sumCnt2) { int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count(); sumCnt2 += cnt; lastRow2 = firstRow2 + cnt - 1; if (list[i].CSId != null) { sheet1.AddMergedRegion(new CellRangeAddress(firstRow2, lastRow2, 1, 1)); } firstRow2 = lastRow2 + 1; } cnum++; //体系权重 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].TIWeight); (c as ICell).CellStyle = style4; //sheet1.AddMergedRegion(new CellRangeAddress(5 + i, 5 + i, 7 + cnum, 7 + cnum)); if (i == sumCnt5) { int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count(); sumCnt5 += cnt; lastRow5 = firstRow5 + cnt - 1; if (list[i].CSId != null) { sheet1.AddMergedRegion(new CellRangeAddress(firstRow5, lastRow5, 2, 2)); } firstRow5 = lastRow5 + 1; } cnum++; //检查标准名称 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].CSTitle); (c as ICell).CellStyle = style_Stand; cnum++; ////检查标准名称 //c = "c_" + cnum; //c = (r as IRow).CreateCell(cnum); //(c as ICell).SetCellValue(""); //(c as ICell).CellStyle = style_Stand; //cnum++; //sheet1.AddMergedRegion(new CellRangeAddress(dataCount + 1 + i, dataCount + 1 + i, 2, 3)); //结果 string val = ""; if (list[i].Result != null) { val = list[i].Result.ToUpper() == "TRUE" ? "是" : (list[i].Result == null ? "" : "否"); } c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(val); (c as ICell).CellStyle = style4; sheet1.AddMergedRegion(new CellRangeAddress(dataCount + 1 + i, dataCount + 1 + i, 4, 4)); cnum++; //得分 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].Score); (c as ICell).CellStyle = style4; if (i == sumCnt4) { int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count(); sumCnt4 += cnt; lastRow4 = firstRow4 + cnt - 1; if (list[i].CSId != null) { sheet1.AddMergedRegion(new CellRangeAddress(firstRow4, lastRow4, 5, 5)); } firstRow4 = lastRow4 + 1; } cnum++; //备注 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].Remarks); (c as ICell).CellStyle = style_b2; if (i == sumCnt3) { int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count(); sumCnt3 += cnt; lastRow3 = firstRow3 + cnt - 1; if (list[i].CSId != null) { sheet1.AddMergedRegion(new CellRangeAddress(firstRow3, lastRow3, 6, 6)); } firstRow3 = lastRow3 + 1; } cnum++; } sheet1.CreateRow(dataCount + 1 + list.Count); IRow row_sub2 = sheet1.CreateRow(dataCount + 2 + list.Count); ICell cell_sub2 = row_sub2.CreateCell(0); cell_sub2.SetCellValue("巡检照片"); CellRangeAddress region_sub2 = new CellRangeAddress(dataCount + 2 + list.Count, dataCount + 2 + list.Count, 0, 6); cell_sub2.CellStyle = style_sub; sheet1.AddMergedRegion(region_sub2); for (int i = 0; i < lpList.Count; i++) { XSSFClientAnchor anchor; IRow row_pic = sheet1.CreateRow(dataCount + 2 + list.Count + i + 1); if (i % 2 == 1) { row_pic.Height = 4000; anchor = new XSSFClientAnchor(50, 50, 50, 50, 4, dataCount + 2 + list.Count + i + 1, 7, dataCount + 2 + list.Count + i + 2); } else { ICell cell_pic = row_pic.CreateCell(0); cell_pic.CellStyle = style_b3; ICell cell_pic2 = row_pic.CreateCell(4); cell_pic2.CellStyle = style_b3; CellRangeAddress region_tp_1 = new CellRangeAddress(dataCount + 2 + list.Count + i + 1, dataCount + 2 + list.Count + i + 1, 0, 2); sheet1.AddMergedRegion(region_tp_1); CellRangeAddress region_tp_2 = new CellRangeAddress(dataCount + 2 + list.Count + i + 1, dataCount + 2 + list.Count + i + 1, 4, 6); sheet1.AddMergedRegion(region_tp_2); if (lpList[i].PicName != null) { cell_pic.SetCellValue((i + 1) + "、第" + (i + 1) + "个拍照点的名称" + lpList[i].PicName); } if (lpList[i + 1].PicName != null) { cell_pic2.SetCellValue((i + 2) + "、第" + (i + 2) + "个拍照点的名称" + lpList[i + 1].PicName); } anchor = new XSSFClientAnchor(50, 50, 50, 50, 0, dataCount + 2 + list.Count + i + 2, 3, dataCount + 2 + list.Count + i + 3); } if (lpList[i].PicUrl != null) { string imagesPath = lpList[i].PicUrl; HttpClient webClient = new HttpClient(); Stream stream = await webClient.GetStreamAsync(imagesPath); Image img = Image.FromStream(stream).GetThumbnailImage(500, 500, null, IntPtr.Zero); MemoryStream ms = new MemoryStream(); img.Save(ms, System.Drawing.Imaging.ImageFormat.Png); var patriarch = sheet1.CreateDrawingPatriarch(); anchor.AnchorType = AnchorType.MoveAndResize; int index = workbook.AddPicture(ms.ToArray(), PictureType.PNG); var signaturePicture = patriarch.CreatePicture(anchor, index); } } workbook.Write(fs); return(Json(newFile)); } } else { return(Json("没有查询结果.")); } } catch (Exception ex) { throw; } }
public static bool ToSingleMbExcels(string savePath, string mbPath, List <SingleMb> dataList, string gwdcode, int rownum) { try { IWorkbook workbook; try { using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read)) { workbook = new HSSFWorkbook(file);//创建对应文件EXCEL2003 } } catch (Exception ex) { using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(file);//创建对应文件EXCEL2007 } } #region 样式 HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle(); style.WrapText = true; style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HSSFFont font = (HSSFFont)workbook.CreateFont(); //font.FontName = "宋体"; font.FontName = "Arial Narrow"; font.FontHeightInPoints = 9; style.SetFont(font); HSSFFont fontSmall = (HSSFFont)workbook.CreateFont(); fontSmall.FontName = "Arial Narrow"; fontSmall.FontHeightInPoints = 8; #endregion ISheet sheet = workbook.GetSheetAt(0); ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.WrapText = true;//换行 foreach (SingleMb item in dataList) { IRow row = sheet.GetRow(item.rowIndex - 1); if (row == null) { row = sheet.CreateRow(item.rowIndex - 1); } ICell cell = row.GetCell(item.cellIndex - 1, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 //cell.CellStyle = cellStyle; cell.SetCellValue(item.value); //循环往第二行的单元格中添加数据 } #region 插入图片 string gwdgz = gwdcode; string imageFilePath = HttpContext.Current.Server.MapPath("/Content/TASK/images/"); if (System.IO.File.Exists(imageFilePath + gwdgz + ".png")) { byte[] bytes = System.IO.File.ReadAllBytes(imageFilePath + gwdgz + ".png"); //int pictureIdx = myWorkbook.AddPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG); // Create the drawing patriarch. This is the top level container for all shapes. HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //add a picture //dx1:起始单元格的x偏移量,如例子中的255表示直线起始位置距A1单元格左侧的距离; //dy1:起始单元格的y偏移量,如例子中的125表示直线起始位置距A1单元格上侧的距离; //dx2:终止单元格的x偏移量,如例子中的1023表示直线起始位置距C3单元格左侧的距离; //dy2:终止单元格的y偏移量,如例子中的150表示直线起始位置距C3单元格上侧的距离; //col1:起始单元格列序号,从0开始计算; //row1:起始单元格行序号,从0开始计算,如例子中col1=0,row1=0就表示起始单元格为A1; //col2:终止单元格列序号,从0开始计算; //row2:终止单元格行序号,从0开始计算,如例子中col2=2,row2=2就表示起始单元格为C3; HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 13, 0, 14, 4); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //anchor = new HSSFClientAnchor(0, 0, 0, 0, 13, 11, 15, 14); //HSSFPicture pict1 = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //pict.Resize(); } #endregion #region 放入问题表格边框 for (int i = 23; i < rownum - 1; i++) { for (int j = 0; j <= 13; j++) { sheet.GetRow(i).GetCell(j).CellStyle = style; if (j == 0) { sheet.GetRow(i).GetCell(j).CellStyle.SetFont(fontSmall); } } } #endregion using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } } catch (Exception) { return(false); } return(true); }
/// <summary> /// 通过模板生成EXCEL /// </summary> /// <param name="workbook"></param> /// <param name="dataList">sheet集合</param> /// <param name="temName">模板名称</param> /// <returns></returns> public static byte[] writeExcelToFile(XSSFWorkbook workbook, List <ExcelSheetModel> dataList, String temName) { // 列宽一个像素的固定值 //decimal pixCellConst = 31.94888178913738m; // 行宽一个像素的固定值 //decimal pixRowConst = 15.15151515151515m; String location = null; XSSFRow row = null; foreach (ExcelSheetModel dataSheet in dataList) { if (!string.IsNullOrEmpty(dataSheet.sheetType)) { } else { #region 自定义模式 XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(dataSheet.sheetName); // 自动更新公式 sheet.ForceFormulaRecalculation = true; foreach (ExcelCellModel dataCell in dataSheet.dataList) { if (!string.IsNullOrEmpty(dataCell.location)) { location = dataCell.location.ToUpper(); int dataNum = 0; int dataRow = 0; string rowStr = ""; string columnStr = ""; for (int i = 0; i < location.Length; i++) { var locationArr = location.ToCharArray(); if (locationArr[i] >= 48 && locationArr[i] <= 57) { columnStr += locationArr[i]; } if (locationArr[i] >= 65 && locationArr[i] <= 90) { rowStr += locationArr[i]; } } for (int i = 0; i < rowStr.Length; i++) { var rowStrArr = rowStr.ToCharArray(); char ch = rowStrArr[rowStr.Length - i - 1]; dataNum = (int)(ch - 'A' + 1); dataNum *= int.Parse(Math.Pow(26, i).ToString()); dataRow += dataNum; } dataCell.rowNum = int.Parse(columnStr) - 1; dataCell.cellNum = dataRow - 1; } //logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:" //+ dataCell.cellNum + "->Strat"); XSSFCellStyle setBorder = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFFont font = (XSSFFont)workbook.CreateFont(); XSSFColor fontColor = null; XSSFColor backColor = null; if (row == null || row.RowNum != dataCell.rowNum) { row = (XSSFRow)sheet.GetRow(dataCell.rowNum); } XSSFCell cell = (XSSFCell)row.CreateCell(dataCell.cellNum); // check合并单元格 if (dataCell.regionCell != null) { if (dataCell.regionCell.Length == 2) { if (dataCell.rowNum > dataCell.regionCell[0]) { //logger.Debug("RegionCella结束行小于当前行"); } else if (dataCell.cellNum > dataCell.regionCell[1]) { //logger.Debug("RegionCella结束列小于当前列"); } else { sheet.AddMergedRegion( new CellRangeAddress(dataCell.rowNum, dataCell.regionCell[0], dataCell.cellNum, dataCell.regionCell[1])); } } else { //logger.Debug("RegionCell参数异常"); } } // check列宽 if (dataCell.cellWidth != 0) { /* * sheet.SetColumnWidth(dataCell.cellNum, * int.Parse(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString())).ToString())); * //*/ /* * sheet.SetColumnWidth(dataCell.cellNum, * Convert.ToInt32(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString())))); * //*/ sheet.SetColumnWidth(dataCell.cellNum, dataCell.cellWidth); } // check行高 if (dataCell.cellHeight != 0) { row.Height = dataCell.cellHeight; } // check字体颜色 if (dataCell.cellColor != null) { if (dataCell.cellColor.Length == 3) { if (((dataCell.cellColor[0] >= 0) || (dataCell.cellColor[0] <= 255)) && ((dataCell.cellColor[1] >= 0) || (dataCell.cellColor[1] <= 255)) && ((dataCell.cellColor[2] >= 0) || (dataCell.cellColor[2] <= 255))) { fontColor = new XSSFColor(Color.FromArgb(dataCell.cellColor[0], dataCell.cellColor[1], dataCell.cellColor[2])); font.SetColor(fontColor); } } } // check背景色 if (dataCell.backColor != null) { if (dataCell.backColor.Length == 3) { if (((dataCell.backColor[0] >= 0) || (dataCell.backColor[0] <= 255)) && ((dataCell.backColor[1] >= 0) || (dataCell.backColor[1] <= 255)) && ((dataCell.backColor[2] >= 0) || (dataCell.backColor[2] <= 255))) { backColor = new XSSFColor(Color.FromArgb(dataCell.backColor[0], dataCell.backColor[1], dataCell.backColor[2])); setBorder.FillPattern = FillPattern.SolidForeground; setBorder.SetFillForegroundColor(backColor); } } } // check边框 if (dataCell.borderLine != null) { if (dataCell.borderLine.Length == 4) { setBorder.BorderTop = dataCell.borderLine[0]; setBorder.BorderBottom = dataCell.borderLine[1]; setBorder.BorderLeft = dataCell.borderLine[2]; setBorder.BorderRight = dataCell.borderLine[3]; } } // check字体 if (!string.IsNullOrEmpty(dataCell.fontName)) { font.FontName = dataCell.fontName; } else { font.FontName = "宋体"; } // check字体大小 if (dataCell.fontSize != 0) { font.FontHeightInPoints = dataCell.fontSize; } else { font.FontHeightInPoints = (short)10; } // 字体是否加粗 if (dataCell.Boldweight) { font.IsBold = true; } // 是否自动换行 if (dataCell.wrapText) { setBorder.WrapText = true; } // 单元格的值 if (!string.IsNullOrEmpty(dataCell.cellValue)) { cell.SetCellValue(dataCell.cellValue); } else { cell.SetCellValue(""); } // 水平类型 setBorder.Alignment = dataCell.horizontalAlignment; // 垂直类型 setBorder.VerticalAlignment = dataCell.verticalAlignment; // 图片 if (dataCell.excelPictureModel != null) { int col2 = 0; int row2 = 0; byte[] arr = null; if (dataCell.excelPictureModel.CodeType == PrintStructFlag.QRCODE) {//二维码 MemoryStream pictureIS = QRCodeUtil.GetQRCode(dataCell.excelPictureModel.qrCode); arr = new byte[pictureIS.Length]; pictureIS.Position = 0; pictureIS.Read(arr, 0, (int)pictureIS.Length); pictureIS.Close(); col2 = dataCell.excelPictureModel.endColNum + 1; row2 = dataCell.excelPictureModel.endRowNum + 1; } else if (dataCell.excelPictureModel.CodeType == PrintStructFlag.BARCODE) { //一维码 BarCodeClass bc = new BarCodeClass(); Image image = bc.ZXCreateBarCode(dataCell.excelPictureModel.qrCode); ImageConverter imgconv = new ImageConverter(); arr = (byte[])imgconv.ConvertTo(image, typeof(byte[])); col2 = dataCell.excelPictureModel.endColNum + 4; row2 = dataCell.excelPictureModel.endRowNum + 1; } int pIndex = workbook.AddPicture(arr, NPOI.SS.UserModel.PictureType.JPEG); XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, dataCell.cellNum, dataCell.rowNum, col2, row2); patriarch.CreatePicture(anchor, pIndex); } setBorder.SetFont(font); cell.CellStyle = setBorder; //logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:" //+ dataCell.cellNum + "->End"); } #endregion } } string filePath = System.Threading.Thread.GetDomain().BaseDirectory + "\\"; var new_tempFileName = "to_" + temName + ".xlsx"; FileStream files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.Create); workbook.Write(files); files.Close(); files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.OpenOrCreate); byte[] bytes = new byte[files.Length]; files.Read(bytes, 0, bytes.Length); files.Close(); workbook.Close(); return(bytes); }
/// <summary> /// 集合导出Excel /// </summary> /// <param name="list">集合</param> /// <param name="columnNames">列名转换</param> /// <param name="dicOnly">部分转换</param> /// <returns></returns> public static byte[] NpoiListToExcel(List <T> list, string sheetName = "Sheet1") { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); var headRow = sheet.CreateRow(0); PropertyInfo[] props = typeof(T).GetProperties(); for (var i = 0; i < props.Length; ++i) { Object obj = props[i].GetCustomAttribute(typeof(ExcelAttribute)); if (obj != null) { ExcelAttribute head = (ExcelAttribute)obj; headRow.CreateCell(i).SetCellValue(head.Header); } else { headRow.CreateCell(i).SetCellValue(props[i].Name); } } for (var i = 0; i < list.Count; ++i) { var row = sheet.CreateRow(i + 1); for (var j = 0; j < props.Length; ++j) { Object obj = props[j].GetCustomAttribute(typeof(ExcelAttribute)); var key = props[j]; if (obj != null) { ExcelAttribute head = (ExcelAttribute)obj; if (head.Picture) { string url = props[j].GetValue(list[i]).ToString().Split(",")[0]; if (!string.IsNullOrWhiteSpace(url)) { row.Height = 80 * 20; WebClient temp = new WebClient(); byte[] bytes = temp.DownloadData(url); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 1, j + 1, i + 2); XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx); bytes = null; } continue; } else { var drValue = props[j].GetValue(list[i]) == null ? "" : props[j].GetValue(list[i]).ToString(); var type = props[j].PropertyType; if (head.Obj != null) { type = head.Obj.GetType(); } if (type == typeof(Int32) || type == typeof(Int32?)) { int intV = 0; int.TryParse(drValue, out intV); row.CreateCell(j).SetCellValue(intV); } else if (type == typeof(System.Decimal) || type == typeof(System.Decimal?) || type == typeof(System.Double) || type == typeof(System.Double?)) { double doubV = 0; double.TryParse(drValue, out doubV); if (string.IsNullOrWhiteSpace(drValue)) { row.CreateCell(j).SetCellValue(""); } else { row.CreateCell(j).SetCellValue(doubV); } } else if (type == typeof(System.Byte) || type == typeof(System.Byte?)) { Byte doubV = 0; Byte.TryParse(drValue, out doubV); row.CreateCell(j).SetCellValue(doubV); } else if (type == typeof(System.Boolean) || type == typeof(System.Boolean?)) { Boolean doubV = false; Boolean.TryParse(drValue, out doubV); row.CreateCell(j).SetCellValue(doubV); } else { row.CreateCell(j).SetCellValue(drValue); } } } } } //获取当前列的宽度,然后对比本列的长度,取最大值 for (int columnNum = 0; columnNum <= props.Length; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) { IRow currentRow; //当前行未被使用过 if (sheet.GetRow(rowNum) == null) { currentRow = sheet.CreateRow(rowNum); } else { currentRow = sheet.GetRow(rowNum); } if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; if (columnWidth < length) { columnWidth = length + 1; } } if (columnWidth > 40) { columnWidth = 40; } } sheet.SetColumnWidth(columnNum, columnWidth * 256); } //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); workbook.Close(); stream.Close(); stream.Dispose(); return(buf); }
public static void writeExcel(List <Product> products) { IWorkbook wb = new XSSFWorkbook(); ISheet ws = wb.CreateSheet("Products"); ws.SetColumnWidth(0, 6000); IRow header = ws.CreateRow(0); header.CreateCell(0).SetCellValue("Name"); header.CreateCell(1).SetCellValue("Price"); header.CreateCell(2).SetCellValue("Old Price"); header.CreateCell(3).SetCellValue("Difference"); header.CreateCell(4).SetCellValue("Category"); header.CreateCell(5).SetCellValue("Picture"); header.CreateCell(6).SetCellValue("ID"); header.CreateCell(7).SetCellValue("URL"); int rowcount = 1; foreach (Product product in products) { IRow ProductRow = ws.CreateRow(rowcount); ProductRow.CreateCell(0).SetCellValue(product.name); ProductRow.CreateCell(1).SetCellValue(product.price); ProductRow.CreateCell(2).SetCellValue(product.xprice); ProductRow.CreateCell(3).SetCellValue(product.dif); ProductRow.CreateCell(4).SetCellValue(product.category); ProductRow.CreateCell(5); ProductRow.CreateCell(6).SetCellValue(product.ID); ProductRow.CreateCell(7).SetCellValue(product.URL); ProductRow.Height = 1500; if (File.Exists(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png")) { byte[] data = File.ReadAllBytes(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png"); int pictureIndex = wb.AddPicture(data, PictureType.PNG); IDrawing patriarch = ws.CreateDrawingPatriarch(); IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor(); anchor.Col1 = 5; anchor.Row1 = rowcount; anchor.AnchorType = AnchorType.MoveAndResize; IPicture picture = patriarch.CreatePicture(anchor, pictureIndex); picture.Resize(1); //byte[] data = File.ReadAllBytes(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png"); //int pictureIndex = wb.AddPicture(data, PictureType.PNG); //ICreationHelper helper = wb.GetCreationHelper(); //IDrawing drawing = ws.CreateDrawingPatriarch(); //IClientAnchor anchor = helper.CreateClientAnchor(); //anchor.Col1 = 5;//0 index based column //anchor.Row1 = ProductRow.RowNum;//0 index based row //IPicture picture = drawing.CreatePicture(anchor, pictureIndex); //picture.Resize(); } rowcount++; } IRow row = ws.CreateRow(rowcount); row.CreateCell(0).SetCellValue("Stop"); rowcount++; row = ws.CreateRow(rowcount); rowcount++; row = ws.CreateRow(rowcount); row.CreateCell(0).SetCellValue("Skipped products"); rowcount++; foreach (String url in Program.skippedURLs) { row = ws.CreateRow(rowcount); row.CreateCell(0).SetCellValue(url); rowcount++; } Stream stream = new FileStream(ProductsExcelPath, FileMode.Create); //Stream stream = new FileStream(@"C:\Users\email\Desktop\Hardware Hub\products.xlsx", FileMode.Open); wb.Write(stream); wb.Close(); stream.Close(); }
/// <summary> /// 生成EXCEL /// </summary> /// <param name="info">数据</param> /// <param name="sSheetName">EXCEL生成后的路径,绝对路径如:C:\a.xls</param> public ImportExcelHelper(List <DataListViewModel> info, string sSheetName) { FileStream fs = null; IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("测试列表"); //创建一个名称为XX的表 sheet.CreateFreezePane(0, 1); //冻结列头行 IRow row_Title = sheet.CreateRow(0); //创建列头行 row_Title.HeightInPoints = 19.5F; //设置列头行高 // 总列 var row = 2; #region 设置列宽 // 设置列宽,excel列宽每个像素是1/256 for (int i = 0; i <= row; i++) { switch (i) { case 0: sheet.SetColumnWidth(i, 10 * 256); break; case 1: sheet.SetColumnWidth(i, 20 * 256); break; } } #endregion #region 设置列头单元格样式 ICellStyle cs_Title = wb.CreateCellStyle(); //创建列头样式 cs_Title.Alignment = HorizontalAlignment.Center; //水平居中 cs_Title.VerticalAlignment = VerticalAlignment.Center; //垂直居中 IFont cs_Title_Font = wb.CreateFont(); //创建字体 cs_Title_Font.IsBold = true; //字体加粗 cs_Title_Font.FontHeightInPoints = 12; //字体大小 cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式 #endregion #region 生成列头 for (int i = 0; i <= row; i++) { ICell cell_Title = row_Title.CreateCell(i); //创建单元格 cell_Title.CellStyle = cs_Title; //将样式绑定到单元格 switch (i) { case 0: cell_Title.SetCellValue("序号"); break; case 1: cell_Title.SetCellValue("图片"); break; } } #endregion if (info != null) { for (int i = 0; i < info.Count; i++) { #region 设置内容单元格样式 ICellStyle cs_Content = wb.CreateCellStyle(); //创建列头样式 cs_Content.Alignment = HorizontalAlignment.Center; //水平居中 cs_Content.VerticalAlignment = VerticalAlignment.Center; //垂直居中 cs_Content.WrapText = true; // 自动换行 IFont cs_Content_Font = wb.CreateFont(); //创建字体 cs_Content_Font.FontHeightInPoints = 12; //字体大小 cs_Content.SetFont(cs_Content_Font); //将字体绑定到样式 #endregion #region 生成内容单元格 //创建行 IRow row_Content = sheet.CreateRow(i + 1); //设置行高 ,excel行高度每个像素点是1/20 row_Content.Height = 100 * 20; for (int j = 0; j <= row; j++) { ICell cell_Conent = row_Content.CreateCell(j); //创建单元格 cell_Conent.CellStyle = cs_Content; switch (j) { case 0: SetCellValue(cell_Conent, info[i].ID); break; case 1: if (!string.IsNullOrEmpty(info[i].ImgPath)) { // 第一步:读取图片到byte数组 var filename = GetImageRoute(info[i].ImgPath); if (!string.IsNullOrEmpty(filename)) { byte[] bytes = File.ReadAllBytes(filename); // 第二步:将图片添加到workbook中 指定图片格式 返回图片所在workbook->Picture数组中的索引地址(从1开始) int pictureIdx = wb.AddPicture(bytes, PictureType.JPEG); // 第三步:在sheet中创建画部 IDrawing drawing = sheet.CreateDrawingPatriarch(); // 第四步:设置锚点 (在起始单元格的X坐标0-1023,Y的坐标0-255,在终止单元格的X坐标0-1023,Y的坐标0-255,起始单元格行数,列数,终止单元格行数,列数) IClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 1, j + 1, i + 2); // 第五步:创建图片 IPicture picture = drawing.CreatePicture(anchor, pictureIdx); // 删除本地图片 DeleteImage(filename); } } break; } } #endregion } } using (fs = File.OpenWrite(sSheetName)) { wb.Write(fs);//向打开的这个xls文件中写入数据 } }
/// <summary> /// 导出Excel DataTable /// </summary> /// <param name="records">records必须都为DataTable</param> /// <param name="formatter">Dictionary key:DataTable中的列明此处必须小写 value:EnumColumnTrans</param> /// <returns></returns> public byte[] Write(DataTable records, Dictionary <string, ExcelFormatter> formatter = null, string imgBasepath = "") { Stopwatch sw = new Stopwatch(); sw.Start(); var workBook = new XSSFWorkbook(); MemoryStream ms = new MemoryStream(); var sheet = workBook.CreateSheet(); var headerRow = sheet.CreateRow(0); var cellIndex = 0; Color lightGrey = Color.FromArgb(221, 221, 221); ICellStyle cstyle = workBook.CreateCellStyle(); cstyle.Alignment = HorizontalAlignment.Center; cstyle.IsLocked = true; // cstyle.FillForegroundColor = new XSSFColor(lightGrey).Indexed; cstyle.FillForegroundColor = IndexedColors.Grey25Percent.Index; foreach (var map in Maps) { var hcell = headerRow.CreateCell(cellIndex, CellType.String); hcell.CellStyle = cstyle; hcell.SetCellValue(map.Name); cellIndex++; } IDataValidationHelper dvHelper = sheet.GetDataValidationHelper(); var rowIndex = 1; IDrawing patriarch = sheet.CreateDrawingPatriarch(); bool isimg = false; foreach (DataRow record in records.Rows) { var dr = sheet.CreateRow(rowIndex); for (int i = 0; i < Maps.Count; i++) { string drValue = record[Maps[i].Info.ToString()].ToString(); ICell cell = dr.CreateCell(i); if (formatter.Any() && formatter.ContainsKey(Maps[i].Info.ToLower()) && formatter[Maps[i].Info.ToLower()] != null) { ExcelFormatter excelFormatter = formatter[Maps[i].Info.ToLower()]; if (!string.IsNullOrEmpty(drValue)) { if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertDownList) { cell.SetCellValue(drValue); XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(excelFormatter.DropSource.Split(',')); CellRangeAddressList regions = new CellRangeAddressList(1, 65535, i, i); XSSFDataValidation dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions); sheet.AddValidationData(dataValidate); } else if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertImage) { if (File.Exists(@"" + imgBasepath + drValue)) { if (!isimg) { sheet.SetColumnWidth(i, 256 * 20); isimg = true; } dr.HeightInPoints = 90; byte[] bytes = System.IO.File.ReadAllBytes(@"" + imgBasepath + drValue); int pictureIdx = workBook.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG); IClientAnchor anchor = new XSSFClientAnchor(100, 50, 0, 0, i, rowIndex, i + 1, rowIndex + 1); IPicture pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(0.3); } else { cell.SetCellValue(""); } } else { cell.SetCellValue(FormatterCoulumn(drValue, excelFormatter.ColumnTrans)); } } else { cell.SetCellValue(drValue); } } else { switch (records.Columns[Maps[i].Info].DataType.ToString()) { case "System.String": //字符串类型 cell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); cell.SetCellValue(dateV); //cell.CellStyle = break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); cell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); cell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); cell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 cell.SetCellValue(""); break; default: cell.SetCellValue(""); break; } } } rowIndex++; } workBook.Write(ms); byte[] buffer = ms.ToArray(); ms.Close(); sw.Stop(); return(buffer); }
/// <summary> /// /// </summary> /// <param name="sheet"></param> private void DiseñoCabeceras(int sheet) { if (sheet < 0) { throw new ArgumentOutOfRangeException(nameof(sheet)); } var pestana = _excel.GetSheetAt(sheet); var image = Image.FromFile(@"C:/Users/mario.chan/Documents/GitHub/Benchmark/Library_benchmark/Content/images/Cemex.png"); for (var i = 0; i < 2; i++) { var row = pestana.GetRow(i) ?? pestana.CreateRow(i); row.HeightInPoints = 51f; for (var j = 7; j < 13; j++) { if (row.GetCell(j) == null) { row.CreateCell(j); } } } var cra = new CellRangeAddress(0, 1, 8, 12); pestana.AddMergedRegion(cra); if (_cabeceraStyle == null) { _cabeceraStyle = GetCabeceraCellStyle(); } var celda = pestana.GetRow(0).GetCell(8); celda.SetCellValue("NPOI"); celda.CellStyle = _cabeceraStyle; ////row0.HeightInPoints = (float)image.Height; var converter = new ImageConverter(); var data = (byte[])converter.ConvertTo(image, typeof(byte[])); //var pictureIndex = excel.AddPicture(data, PictureType.PNG); //var helper = excel.GetCreationHelper(); //var drawing = pestana.CreateDrawingPatriarch(); //var anchor = new XSSFClientAnchor(900, 0, 0, 0, 1, 1, 7, 2); //var picture = drawing.CreatePicture(anchor, pictureIndex); //picture.Resize(1); var myPictureId = _excel.AddPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG); var drawing = pestana.CreateDrawingPatriarch(); var myAnchor = new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 7, 2); var myPicture = drawing.CreatePicture(myAnchor, myPictureId); myPicture.Resize(); }
public async Task <ActionResult> UploadScoreAjaxDown(string disCode, string startTime, string endTime, string status, string Pid, string disname, string name) { try { string result = await CommonHelper.GetHttpClient().GetStringAsync(CommonHelper.Current.GetAPIBaseUrl + "Tour/GetTaskListByDisIdForExcel/" + disCode + "/" + startTime + "/" + endTime + "/" + status + "/" + Pid); var apiResult = CommonHelper.DecodeString <APIResult>(result); if (apiResult.ResultCode == ResultType.Success) { ExcelResult er = CommonHelper.DecodeString <ExcelResult>(apiResult.Body); List <ResultExcelDto> listAll = er.ResultList; string discode = listAll.FirstOrDefault <ResultExcelDto>().DisCode; List <ResultExcelDto> list = (from a in listAll where a.Score == "0" select a).ToList <ResultExcelDto>(); var uploads = Path.Combine(_environment.WebRootPath, "Template"); var newFile = Path.Combine(uploads, "ScoreViewList_" + discode + "_" + disname + DateTime.Now.ToString("yyyyMMdd hhmmss") + ".xlsx"); if (!System.IO.Directory.Exists(uploads)) { System.IO.Directory.CreateDirectory(uploads); } using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write)) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("得分登记"); sheet1.PrintSetup.Landscape = true; sheet1.PrintSetup.PaperSize = 9; IFont font_b = workbook.CreateFont(); font_b.FontName = "Microsoft Yahei"; font_b.FontHeightInPoints = 10; var style1 = (XSSFCellStyle)workbook.CreateCellStyle(); //style1.FillForegroundColor = HSSFColor.Grey25Percent.Index; byte[] rgb = new byte[3] { 116, 163, 210 }; style1.SetFillForegroundColor(new XSSFColor(rgb)); style1.FillPattern = FillPattern.SolidForeground; style1.Alignment = HorizontalAlignment.Center; style1.BorderLeft = BorderStyle.Thin; style1.BorderBottom = BorderStyle.Thin; style1.BorderRight = BorderStyle.Thin; style1.BorderTop = BorderStyle.Thin; style1.SetFont(font_b); var style_Stand = workbook.CreateCellStyle(); style_Stand.Alignment = HorizontalAlignment.Left; style_Stand.WrapText = true; style_Stand.SetFont(font_b); var style_b2 = workbook.CreateCellStyle(); style_b2.WrapText = true; style_b2.VerticalAlignment = VerticalAlignment.Center; style_b2.SetFont(font_b); var style2 = workbook.CreateCellStyle(); style2.Alignment = HorizontalAlignment.Left; style2.VerticalAlignment = VerticalAlignment.Bottom; style2.WrapText = true; style2.SetFont(font_b); var style3 = workbook.CreateCellStyle(); style3.Alignment = HorizontalAlignment.Right; style3.SetFont(font_b); var style4 = workbook.CreateCellStyle(); style4.Alignment = HorizontalAlignment.Center; style4.VerticalAlignment = VerticalAlignment.Center; style4.SetFont(font_b); var style_t = (XSSFCellStyle)workbook.CreateCellStyle(); style_t.Alignment = HorizontalAlignment.Center; style_t.SetFillForegroundColor(new XSSFColor(rgb)); //style_t.FillForegroundColor = HSSFColor.Grey25Percent.Index; style_t.FillPattern = FillPattern.SolidForeground; IFont font_t = workbook.CreateFont(); font_t.FontHeightInPoints = 18; font_t.FontName = "Microsoft Yahei"; style_t.SetFont(font_t); //第一行,插入图片 IRow row_0 = sheet1.CreateRow(0); row_0.Height = 600; CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 8); sheet1.AddMergedRegion(region0); byte[] bytes = System.IO.File.ReadAllBytes(_environment.WebRootPath + "/images/project_logo.png"); int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG); var patriarch = sheet1.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 200, 40, 0, 0, 1, 1); anchor.AnchorType = AnchorType.MoveAndResize; //把图片插到相应的位置 var pict = patriarch.CreatePicture(anchor, pictureIdx); //标题 IRow row_t = sheet1.CreateRow(1); ICell cell_t = row_t.CreateCell(0); cell_t.SetCellValue("一汽丰田PCM经销店经营能力评估-待改善项报告"); CellRangeAddress region_t = new CellRangeAddress(1, 1, 0, 4); cell_t.CellStyle = style_t; sheet1.AddMergedRegion(region_t); //插入标题右边的Logo byte[] bytesRight = System.IO.File.ReadAllBytes(_environment.WebRootPath + "/images/pcm_logo.png"); int pictureIdxRight = workbook.AddPicture(bytesRight, PictureType.PNG); var patriarchRight = sheet1.CreateDrawingPatriarch(); XSSFClientAnchor anchorRight = new XSSFClientAnchor(0, 0, 0, 0, 6, 1, 7, 3); //把图片插到相应的位置 var pictRight = patriarch.CreatePicture(anchorRight, pictureIdxRight); pictRight.Resize(2.01, 1.5); IRow row = sheet1.CreateRow(2); row.Height = 600; ICell cell = row.CreateCell(0); cell.SetCellValue("经销店代码:" + discode + " 经销店名称:" + disname + " 经销店负责人签字:________________"); CellRangeAddress region = new CellRangeAddress(2, 2, 0, 4); cell.CellStyle = style2; sheet1.AddMergedRegion(region); IRow row1 = sheet1.CreateRow(3); row1.Height = 600; ICell cel2 = row1.CreateCell(0); //cel2.CellStyle = style; cel2.SetCellValue("日期:" + startTime + " 评估员:" + name); cel2.CellStyle = style2; CellRangeAddress region2 = new CellRangeAddress(3, 3, 0, 4); sheet1.AddMergedRegion(region2); var style_sub = (XSSFCellStyle)workbook.CreateCellStyle(); style_sub.Alignment = HorizontalAlignment.Center; style_sub.SetFillForegroundColor(new XSSFColor(rgb)); style_sub.FillPattern = FillPattern.SolidForeground; style_sub.SetFont(font_b); IRow row_nouse = sheet1.CreateRow(4); row_nouse.Height = 400; IRow row_sub = sheet1.CreateRow(5); ICell cell_sub = row_sub.CreateCell(0); cell_sub.SetCellValue("待改善项汇总"); CellRangeAddress region_sub = new CellRangeAddress(5, 5, 0, 8); cell_sub.CellStyle = style_sub; sheet1.AddMergedRegion(region_sub); int dataCount = 6; IRow row3 = sheet1.CreateRow(dataCount); //任务卡名称 ICell cell6 = row3.CreateCell(0); cell6.SetCellValue("指标名称"); cell6.CellStyle = style1; CellRangeAddress region6 = new CellRangeAddress(dataCount, dataCount, 0, 0); sheet1.AddMergedRegion(region6); sheet1.SetColumnWidth(0, 9 * 600); //体系名称 ICell cell7 = row3.CreateCell(1); cell7.SetCellValue("标准与要求"); cell7.CellStyle = style1; CellRangeAddress region7 = new CellRangeAddress(dataCount, dataCount, 1, 1); sheet1.AddMergedRegion(region7); sheet1.SetColumnWidth(1, 8 * 600); //检查标准 ICell cell8 = row3.CreateCell(2); cell8.SetCellValue("过程与关键动作建议"); cell8.CellStyle = style1; ICell cell9 = row3.CreateCell(3); cell9.SetCellValue(""); cell9.CellStyle = style1; ICell cell_9 = row3.CreateCell(4); cell_9.SetCellValue(""); cell_9.CellStyle = style1; CellRangeAddress region8 = new CellRangeAddress(dataCount, dataCount, 2, 4); sheet1.AddMergedRegion(region8); sheet1.SetColumnWidth(2, (15 * 600)); sheet1.SetColumnWidth(3, (3 * 600)); sheet1.SetColumnWidth(4, (10 * 600)); //结果 //ICell cell9 = row3.CreateCell(3); // cell9.SetCellValue("结果"); // cell9.CellStyle = style1; //CellRangeAddress region9 = new CellRangeAddress(dataCount, dataCount, 4, 4); //sheet1.AddMergedRegion(region9); //得分 ICell cell10 = row3.CreateCell(5); cell10.SetCellValue("是否合格"); cell10.CellStyle = style1; ICell cell101 = row3.CreateCell(6); cell101.SetCellValue(""); cell101.CellStyle = style1; CellRangeAddress region10 = new CellRangeAddress(dataCount, dataCount, 5, 6); sheet1.AddMergedRegion(region10); sheet1.SetColumnWidth(5, 4 * 300); sheet1.SetColumnWidth(6, 4 * 300); //备注 ICell cell11 = row3.CreateCell(7); cell11.SetCellValue("备注"); cell11.CellStyle = style1; ICell cell111 = row3.CreateCell(8); cell111.SetCellValue(""); cell111.CellStyle = style1; CellRangeAddress region11 = new CellRangeAddress(dataCount, dataCount, 7, 8); sheet1.AddMergedRegion(region11); sheet1.SetColumnWidth(7, 5 * 600); sheet1.SetColumnWidth(8, 3 * 600); int firstRow1 = dataCount + 1; int lastRow1 = 0; int sumCnt1 = 0; int firstRow2 = dataCount + 1; int lastRow2 = 0; int sumCnt2 = 0; int firstRow3 = dataCount + 1; int lastRow3 = 0; int sumCnt3 = 0; //数据的动态加载 for (int i = 0; i < list.Count; i++) { object r = "r_" + i; r = sheet1.CreateRow(dataCount + 1 + i); int cnum = 0; //计划任务名称 //任务卡名称 object c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].TCTitle); (c as ICell).CellStyle = style_b2; if (i == sumCnt1) { int cnt = (from l1 in list where l1.TCId == list[i].TCId select l1).Count(); sumCnt1 += cnt; lastRow1 = firstRow1 + cnt - 1; if (list[i].CSId != null) { sheet1.AddMergedRegion(new CellRangeAddress(firstRow1, lastRow1, 0, 0)); } firstRow1 = lastRow1 + 1; } cnum++; //体系名称 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].TITitle); (c as ICell).CellStyle = style_b2; //sheet1.AddMergedRegion(new CellRangeAddress(5 + i, 5 + i, 7 + cnum, 7 + cnum)); if (i == sumCnt2) { int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count(); sumCnt2 += cnt; lastRow2 = firstRow2 + cnt - 1; if (list[i].CSId != null) { sheet1.AddMergedRegion(new CellRangeAddress(firstRow2, lastRow2, 1, 1)); } firstRow2 = lastRow2 + 1; } cnum++; //检查标准名称 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].CSTitle); (c as ICell).CellStyle = style_Stand; //sheet1.AddMergedRegion(new CellRangeAddress(dataCount + i, dataCount + i, 7 + cnum, 7 + cnum)); //sheet1.AddMergedRegion(new CellRangeAddress(dataCount + i, dataCount + i, 2, 3)); cnum++; //检查标准名称 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(""); (c as ICell).CellStyle = style_Stand; cnum++; //检查标准名称 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(""); (c as ICell).CellStyle = style_Stand; sheet1.AddMergedRegion(new CellRangeAddress(dataCount + 1 + i, dataCount + 1 + i, 2, 4)); cnum++; //结果 /*c = "c_" + cnum; * c = (r as IRow).CreateCell(cnum); * (c as ICell).SetCellValue(list[i].Result == "False" ? "否" : (list[i].Result == null ? "" : "是")); * (c as ICell).CellStyle = style4; * sheet1.AddMergedRegion(new CellRangeAddress(dataCount + i, dataCount + i, 7 + cnum, 7 + cnum)); * cnum++;*/ //得分 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].Result == "true" ? "否" : "是"); (c as ICell).CellStyle = style4; cnum++; //得分 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(""); (c as ICell).CellStyle = style4; sheet1.AddMergedRegion(new CellRangeAddress(dataCount + 1 + i, dataCount + 1 + i, 5, 6)); cnum++; //备注 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(list[i].Remarks); (c as ICell).CellStyle = style_b2; cnum++; //备注 c = "c_" + cnum; c = (r as IRow).CreateCell(cnum); (c as ICell).SetCellValue(""); (c as ICell).CellStyle = style_b2; if (i == sumCnt3) { int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count(); sumCnt3 += cnt; lastRow3 = firstRow3 + cnt - 1; //if (list[i].CSId != null) //{ sheet1.AddMergedRegion(new CellRangeAddress(firstRow3, lastRow3, 7, 8)); //} firstRow3 = lastRow3 + 1; } cnum++; } workbook.Write(fs); return(Json(newFile)); } } else { return(Json("没有查询结果.")); } } catch (Exception) { throw; } }
public static void ToExcel2007 <T>(List <T> dataSource, string path, List <string> attries, List <string> headers) { if (dataSource == null || !dataSource.Any()) { throw new Exception("dataSource is null."); } var workbook = new XSSFWorkbook(); //创建一个工作薄 ISheet sheet = workbook.CreateSheet(); //在工作薄中创建一个工作表 IRow rw = sheet.CreateRow(0); var patriarch = sheet.CreateDrawingPatriarch(); for (int i = 0; i < headers.Count; i++) //循环一个表头来创建第一行的表头 { rw.CreateCell(i).SetCellValue(headers[i]); } Type t = typeof(T); //获取得泛型集合中的实体, 返回T的类型 PropertyInfo[] properties = t.GetProperties(); //返回当前获得实体后 实体类型中的所有公共属性 for (int i = 0; i < dataSource.Count; i++) //循环实体泛型集合 { rw = sheet.CreateRow(i + 1); //创建一个新行,把传入集合中的每条数据创建一行 foreach (PropertyInfo property in properties) //循环得到的所有属性(想要把里面指定的属性值导出到Excel文件中) { for (int j = 0; j < attries.Count; j++) //循环需要导出属性值 的 属性名 { string attry = attries[j]; //获得一个需要导入的属性名; if (string.Compare(property.Name.ToUpper(), attry.ToUpper()) == 0) //如果需要导出的属性名和当前循环实体的属性名一样, { object objValue = property.GetValue(dataSource[i], null); //获取当前循环的实体属性在当前实体对象(arr[i])的值 if (objValue != null && (objValue.GetType().Name == "Bitmap" || objValue.GetType().Name == "Image")) { //- 插入图片到 Excel,并返回一个图片的标识 var handle = (objValue as Bitmap).GetHbitmap(); using (Bitmap newBmp = Image.FromHbitmap(handle)) { MemoryStream ms = new MemoryStream(); newBmp.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); byte[] bytes = ms.GetBuffer(); ms.Close(); var pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); //- 创建图片的位置 var anchor = new XSSFClientAnchor( 0, 0, //- 上左 到 上右 的位置,是基于下面的行列位置 0, 0, //- 下左 到 下右 的位置,是基于下面的行列位置 j, i + 1, j + 1, i + 2); //- 图片输出的位置这么计算的: //- 假设我们要将图片放置于第 5(E) 列的第 2 行 //- 对应索引为是 4 : 1 (默认位置) //- 放置的位置就等于(默认位置)到(默认位置各自加上一行、一列) var pic = patriarch.CreatePicture(anchor, pictureIdx);//- 使用绘画器绘画图片 sheet.SetColumnWidth(j, 100 * 36); rw.HeightInPoints = 100 * 0.75f; bytes = null; } DeleteObject(handle); } else { rw.CreateCell(j).SetCellValue((objValue == null) ? string.Empty : objValue.ToString());//创建单元格并进行赋值 sheet.AutoSizeColumn(j); } } } } } using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { workbook.Write(fs); } }