using Aspose.Cells;
|
using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using NPOI.SS.Util;
|
using NPOI.XSSF.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Drawing;
|
using System.IO;
|
using System.Text;
|
using WIP_Models;
|
|
namespace WIP_Print
|
{
|
public class ExcelUtil
|
{
|
public static Dictionary<string, string> printTypeDict = new Dictionary<string, string>();//打印类型字典(Key:打印类型，Value：相应打印类型的模板ExceJSON数据)
|
|
/// <summary>
|
/// 通过模板文件获取单元格集合
|
/// </summary>
|
/// <param name="workbook"></param>
|
/// <param name="qrCode">二维码Code</param>
|
/// <returns></returns>
|
public static List<ExcelCellModel> GetCellListByTemplateFile(XSSFWorkbook workbook)
|
{
|
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
|
List<ExcelCellModel> dataList = null;
|
ExcelCellModel data = null;
|
IRow row = null;
|
ICell cell = null;
|
ExcelPictureModel pictureModel = null;
|
var columnWith = 0;
|
BorderStyle[] border = new BorderStyle[4];
|
try
|
{
|
//总行数
|
int rowCount = sheet.LastRowNum;
|
if (rowCount > 0)
|
{
|
dataList = new List<ExcelCellModel>();
|
IRow firstRow = sheet.GetRow(0);//第一行
|
int cellCount = firstRow.LastCellNum;//总列数
|
|
//填充行
|
for (int i = 0; i <= rowCount; ++i)
|
{
|
//清空值
|
border = new BorderStyle[4];
|
|
row = sheet.GetRow(i);
|
if (row == null) continue;
|
for (int j = row.FirstCellNum; j < cellCount; ++j)
|
{
|
cell = row.GetCell(j);
|
if (cell == null)
|
{
|
|
}
|
else
|
{
|
#region cell赋值
|
|
//四个边框按照 上下左右
|
border[0] = cell.CellStyle.BorderTop;
|
border[1] = cell.CellStyle.BorderBottom;
|
border[2] = cell.CellStyle.BorderLeft;
|
border[3] = cell.CellStyle.BorderRight;
|
columnWith = sheet.GetColumnWidth(j);
|
data = new ExcelCellModel()
|
{
|
rowNum = cell.RowIndex,
|
cellNum = cell.ColumnIndex,
|
cellWidth = columnWith,
|
cellHeight = row.Height,
|
fontSize = cell.CellStyle.GetFont(workbook).FontHeightInPoints,
|
wrapText = cell.CellStyle.WrapText,
|
borderLine = border,
|
Boldweight = cell.CellStyle.GetFont(workbook).IsBold,
|
verticalAlignment = cell.CellStyle.VerticalAlignment,
|
horizontalAlignment = cell.CellStyle.Alignment,
|
fontName = cell.CellStyle.GetFont(workbook).FontName,
|
//cellColor=cell.CellStyle.GetFont(workbook).Color
|
location = "",
|
// regionCell=""
|
};
|
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
|
switch (cell.CellType)
|
{
|
case CellType.Blank:
|
data.cellValue = "";
|
break;
|
case CellType.Numeric:
|
short format = cell.CellStyle.DataFormat;
|
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
|
if (format == 14 || format == 31 || format == 57 || format == 58)
|
data.cellValue = cell.DateCellValue.ToString();
|
else
|
data.cellValue = cell.NumericCellValue.ToString();
|
break;
|
case CellType.String:
|
data.cellValue = cell.StringCellValue;
|
break;
|
}
|
|
|
//二维码单独处理
|
if (data.cellValue == PrintStruct.QRCODE)
|
{
|
pictureModel = new ExcelPictureModel()
|
{
|
endColNum = data.cellNum,
|
endRowNum = data.rowNum,
|
qrCode = PrintStruct.QRCODE,
|
CodeType = PrintStructFlag.QRCODE
|
};
|
data.excelPictureModel = pictureModel;
|
}
|
|
//一维码单独处理
|
if (data.cellValue == PrintStruct.BARCODE)
|
{
|
pictureModel = new ExcelPictureModel()
|
{
|
endColNum = data.cellNum,
|
endRowNum = data.rowNum,
|
qrCode = PrintStruct.BARCODE,
|
CodeType = PrintStructFlag.BARCODE
|
};
|
data.excelPictureModel = pictureModel;
|
}
|
|
#endregion
|
dataList.Add(data);
|
}
|
}
|
}
|
}
|
}
|
catch (Exception)
|
{
|
throw;
|
}
|
return dataList;
|
}
|
|
/// <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;
|
}
|
|
}
|
}
|
