using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using System;
|
using System.IO;
|
using TProduct.Npoi;
|
|
namespace TProduct.DataFile.PumpReport
|
{
|
public class TestReportFileExcelBase : TestReportFileBase
|
{
|
|
protected HSSFWorkbook _theBookHander = null;
|
|
|
|
|
#region 设置值
|
protected short _backgroupColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
|
protected bool SetCellValue(NPOI.SS.UserModel.ISheet workSheet, int rowIndex, string columnName, int content, bool isAddCellBackgroupColor = false)
|
{
|
return SetCellValue(workSheet, rowIndex, columnName, content.ToString(), isAddCellBackgroupColor);
|
}
|
|
protected bool SetCellValue(NPOI.SS.UserModel.ISheet workSheet, int rowIndex, string columnName, double content, bool isAddCellBackgroupColor = false)
|
{
|
return SetCellValue(workSheet, rowIndex, columnName, content.ToString(), isAddCellBackgroupColor);
|
}
|
|
//isAddCellBackgroupColor 绘制单元背景颜色
|
protected bool SetCellValue(NPOI.SS.UserModel.ISheet workSheet, int rowIndex,
|
string columnName, string content, bool isAddCellBackgroupColor = false)
|
{
|
if (!string.IsNullOrEmpty(content))
|
{
|
workSheet.SetCellValue1(rowIndex, columnName, content);
|
}
|
|
|
if (!isAddCellBackgroupColor)
|
{
|
return true;
|
}
|
|
|
var row = workSheet.GetRow(rowIndex - 1);
|
if (row == null)
|
return false;
|
|
int? columnIndex = NpoiHelper.GetColIndex(columnName);
|
if (columnIndex == null)
|
{
|
return true;
|
}
|
var cell = row.GetCell((int)columnIndex);
|
if (cell == null)
|
return true;
|
var cell_ori_style = cell.CellStyle;
|
var cell_new_style = _theBookHander.CreateCellStyle();
|
if (cell_new_style == null)
|
{
|
return true;
|
}
|
|
cell_new_style.FillForegroundColor = _backgroupColor;//NPOI.HSSF.Util.HSSFColor
|
cell_new_style.FillPattern = FillPattern.SolidForeground;// FillPatternType.SOLID_FOREGROUND;
|
cell_new_style.Alignment = cell_ori_style.Alignment;
|
cell_new_style.BorderBottom = cell_ori_style.BorderBottom;
|
cell_new_style.BorderLeft = cell_ori_style.BorderLeft;
|
cell_new_style.BorderRight = cell_ori_style.BorderRight;
|
cell_new_style.BorderTop = cell_ori_style.BorderTop;
|
cell_new_style.BottomBorderColor = cell_ori_style.BottomBorderColor;
|
cell_new_style.DataFormat = cell_ori_style.DataFormat;
|
cell_new_style.FillBackgroundColor = cell_ori_style.FillBackgroundColor;
|
cell_new_style.FillPattern = cell_ori_style.FillPattern;
|
cell_new_style.Indention = cell_ori_style.Indention;
|
cell_new_style.IsHidden = cell_ori_style.IsHidden;
|
cell_new_style.IsLocked = cell_ori_style.IsLocked;
|
cell_new_style.LeftBorderColor = cell_ori_style.LeftBorderColor;
|
cell_new_style.RightBorderColor = cell_ori_style.RightBorderColor;
|
cell_new_style.Rotation = cell_ori_style.Rotation;
|
cell_new_style.ShrinkToFit = cell_ori_style.ShrinkToFit;
|
cell_new_style.TopBorderColor = cell_ori_style.TopBorderColor;
|
cell_new_style.VerticalAlignment = cell_ori_style.VerticalAlignment;
|
cell_new_style.WrapText = cell_ori_style.WrapText;
|
|
cell_new_style.SetFont(cell_ori_style.GetFont(_theBookHander));
|
|
cell.CellStyle = cell_new_style;
|
|
return true;
|
}
|
|
|
#endregion
|
|
/// <summary>
|
/// 插入图片,可以缩放，缩放为默认比例
|
/// </summary>
|
/// <param name="modelSheet">Sheet表</param>
|
/// <param name="startRowIndex">起始单元格行序号，从0开始计算</param>
|
/// <param name="startColIndex">起始单元格列序号，从0开始计算</param>
|
/// <param name="endRowIndex">终止单元格行序号，从0开始计算</param>
|
/// <param name="endColIndex">终止单元格列序号，从0开始计算</param>
|
/// <param name="XOffset">单元格的x偏移量</param>
|
/// <param name="YOffset">单元格的y偏移量</param>
|
/// <param name="image">图片</param>
|
protected void AddImage1(ISheet modelSheet,
|
int startRowIndex, string startColName,
|
int endRowIndex, string endColName,
|
System.Drawing.Image image)
|
{
|
if (image == null)
|
return;
|
int? startColIndex = NpoiHelper.GetColIndex(startColName);
|
if (startColIndex == null)
|
{
|
return;
|
}
|
int? endColIndex = NpoiHelper.GetColIndex(endColName);
|
if (endColIndex == null)
|
{
|
return;
|
}
|
int XOffset = endRowIndex - startRowIndex;
|
int YOffset = endColIndex.Value - startColIndex.Value;
|
|
byte[] byDatas = PhotoImageInsert(image);
|
|
int pictureIdx = _theBookHander.AddPicture(byDatas, PictureType.PNG);
|
|
HSSFPatriarch patriarch = (HSSFPatriarch)modelSheet.CreateDrawingPatriarch();
|
|
HSSFClientAnchor anchor = new HSSFClientAnchor(XOffset, YOffset, XOffset, YOffset, startColIndex.Value, startRowIndex, endColIndex.Value, endRowIndex);
|
|
//HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
|
patriarch.CreatePicture(anchor, pictureIdx);
|
// var aa = picture.GetPreferredSize();
|
|
}
|
|
/// <summary>
|
/// 将Image转换成流数据，并保存为byte[]
|
/// </summary>
|
/// <param name="imgPhoto"></param>
|
/// <returns></returns>
|
public byte[] PhotoImageInsert(System.Drawing.Image imgPhoto)
|
{
|
//将Image转换成流数据，并保存为byte[]
|
MemoryStream mstream = new MemoryStream();
|
imgPhoto.Save(mstream, System.Drawing.Imaging.ImageFormat.Png);
|
byte[] byData = new Byte[mstream.Length];
|
mstream.Position = 0;
|
mstream.Read(byData, 0, byData.Length);
|
mstream.Close();
|
return byData;
|
}
|
|
|
protected string Round(string str, int digit)
|
{
|
if (string.IsNullOrEmpty(str)) return str;
|
double v = 0;
|
if (double.TryParse(str, out v))
|
{
|
return Math.Round(v, digit).ToString();
|
}
|
else
|
{
|
return str;
|
}
|
}
|
|
protected string Round(double? str, int digit)
|
{
|
if (str == null) return "";
|
|
return Math.Round(str.Value, digit).ToString();
|
|
}
|
}
|
}
|
