/// <summary>
/// 获取图片信息
/// </summary>
/// <param name="cell"></param>
/// <param name="url"></param>
/// <param name="type"></param>
/// <returns></returns>
public static PictureEntity GetPictureData(this ICell cell, string url, UrlType type = UrlType.Base64)
{
byte[] data = null;
switch (type)
{
case UrlType.Base64:
string[] urls = url.Split(',');
// 获取图表
data = Convert.FromBase64String(urls[urls.Length - 1]);
break;
case UrlType.Http:
data = ExportExcelUtil.GetHttpFile(url);
break;
default:
break;
}
if (data == null || data.Length == 0)
{
return(null);
}
double scalx = 0; //x轴缩放比例
double scaly = 0; //y轴缩放比例
int Dx1 = 0; //图片左边相对excel格的位置(x偏移) 范围值为:0~1023,超过1023就到右侧相邻的单元格里了
int Dy1 = 0; //图片上方相对excel格的位置(y偏移) 范围值为:0~256,超过256就到下方的单元格里了
bool bOriginalSize = false; //是否显示图片原始大小 true表示图片显示原始大小 false表示显示图片缩放后的大小
///计算单元格的长度和宽度
double CellWidth = 0;
double CellHeight = 0;
int RowSpanCount = cell.GetSpan().RowSpan; //合并的单元格行数
int ColSpanCount = cell.GetSpan().ColSpan; //合并的单元格列数
int j = 0;
for (j = 0; j < RowSpanCount; j++)//根据合并的行数计算出高度
{
CellHeight += cell.Sheet.GetRow(cell.RowIndex + j).Height;
}
for (j = 0; j < ColSpanCount; j++)
{
CellWidth += cell.Row.Sheet.GetColumnWidth(cell.ColumnIndex + j);
}
//单元格长度和宽度与图片的长宽单位互换是根据实例得出
CellWidth = CellWidth / 35;
CellHeight = CellHeight / 15;
///计算图片的长度和宽度
MemoryStream ms = new MemoryStream(data);
Image Img = Bitmap.FromStream(ms, true);
double ImageOriginalWidth = Img.Width; //原始图片的长度
double ImageOriginalHeight = Img.Height; //原始图片的宽度
double ImageScalWidth = 0; //缩放后显示在单元格上的图片长度
double ImageScalHeight = 0; //缩放后显示在单元格上的图片宽度
if (CellWidth > ImageOriginalWidth && CellHeight > ImageOriginalHeight) //单元格的长度和宽度比图片的大，说明单元格能放下整张图片，不缩放
{
ImageScalWidth = ImageOriginalWidth;
ImageScalHeight = ImageOriginalHeight;
bOriginalSize = true;
}
else//需要缩放，根据单元格和图片的长宽计算缩放比例
{
bOriginalSize = false;
if (ImageOriginalWidth > CellWidth && ImageOriginalHeight > CellHeight) //图片的长和宽都比单元格的大的情况
{
double WidthSub = ImageOriginalWidth - CellWidth; //图片长与单元格长的差距
double HeightSub = ImageOriginalHeight - CellHeight; //图片宽与单元格宽的差距
if (WidthSub > HeightSub) //长的差距比宽的差距大时,长度x轴的缩放比为1，表示长度就用单元格的长度大小，宽度y轴的缩放比例需要根据x轴的比例来计算
{
scalx = 1;
scaly = (CellWidth / ImageOriginalWidth) * ImageOriginalHeight / CellHeight;//计算y轴的缩放比例,CellWidth / ImageWidth计算出图片整体的缩放比例,然后 * ImageHeight计算出单元格应该显示的图片高度,然后/ CellHeight就是高度的缩放比例
}
else
{
scaly = 1;
scalx = (CellHeight / ImageOriginalHeight) * ImageOriginalWidth / CellWidth;
}
}
else if (ImageOriginalWidth > CellWidth && ImageOriginalHeight < CellHeight)//图片长度大于单元格长度但图片高度小于单元格高度，此时长度不需要缩放，直接取单元格的，因此scalx=1，但图片高度需要等比缩放
{
scalx = 1;
scaly = (CellWidth / ImageOriginalWidth) * ImageOriginalHeight / CellHeight;
}
else if (ImageOriginalWidth < CellWidth && ImageOriginalHeight > CellHeight)//图片长度小于单元格长度但图片高度大于单元格高度，此时单元格高度直接取单元格的，scaly = 1,长度需要等比缩放
{
scaly = 1;
scalx = (CellHeight / ImageOriginalHeight) * ImageOriginalWidth / CellWidth;
}
ImageScalWidth = scalx * CellWidth;
ImageScalHeight = scaly * CellHeight;
}
Dx1 = Convert.ToInt32((CellWidth - ImageScalWidth) / CellWidth * 1023 / 2);
Dy1 = Convert.ToInt32((CellHeight - ImageScalHeight) / CellHeight * 256 / 2);
int pictureIdx = cell.Sheet.Workbook.AddPicture((Byte[])data, PictureType.PNG);
IClientAnchor anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor();
anchor.AnchorType = AnchorType.MoveDontResize.GetType().ToInt();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + cell.GetSpan().ColSpan;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + cell.GetSpan().RowSpan;
anchor.Dy1 = Dy1; //图片下移量
anchor.Dx1 = Dx1; //图片右移量，通过图片下移和右移，使得图片能居中显示，因为图片不同文字，图片是浮在单元格上的，文字是钳在单元格里的
PictureEntity entity = new PictureEntity()
{
ScaleX = scalx,
ScaleY = scaly,
Anchor = anchor,
PictureIndex = pictureIdx,
OriginalSize = bOriginalSize
};
return(entity);
}
private CellDataEntity ProcessCell(ISheet sheet, CellMarkEntity item, IEnumerable <CellMergeEntity> cellMerges, IRow row = null)
{
CellDataEntity cellData = new CellDataEntity();
cellData.TitleName = item.TitleName;
cellData.ColumnName = item.ColumnName;
cellData.ColIndex = item.CellIndex;
ICell cell = null;
int rowIndex = 0;
if (row == null)
{
rowIndex = item.RowIndex;
cell = sheet.GetRow(item.RowIndex).GetCell(item.CellIndex + 1);
}
else
{
rowIndex = row.RowNum;
cell = row.GetCell(item.CellIndex);
}
if (cell == null)
{
return(null);
}
cellData.RowIndex = rowIndex;
if (cell.IsMergedCell == false)
{
cellData.CellPostion = string.Concat(ExportExcelUtil.IndexToColName(item.CellIndex), rowIndex + 1);
Type originalType = item.PropertyInfo.PropertyType.GetGenericArguments()[0];
string cellValue = "";
switch (cell.CellType)
{
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
}
else
{
cellValue = cell.NumericCellValue.ToString();
}
break;
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Boolean:
cellValue = cell.BooleanCellValue.ToString();
break;
case CellType.Formula:
if (originalType == typeof(DateTime))
{
cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
}
else
{
cell.SetCellType(CellType.String);
cellValue = cell.ToString();
}
break;
}
cellData.IsEmpty = string.IsNullOrEmpty(cellValue);
cellData.CellValue = cellValue;
//if (originalType == typeof(DateTime))
//{
// cellData.CellValue = cell.DateCellValue.ToString();
//}
//else
//{
// if (cell.CellType == CellType.Formula)
// cell.SetCellType(CellType.String);
// cellData.CellValue = cell.ToString();
//}
}
else
{
CellDimension dimension = ProcessCellDimension(cell, cellMerges);
var merges = cellMerges.Where(t => t.FirstRow == dimension.FirstRowIndex && t.FirstColumn == dimension.FirstColIndex);
cellData.CellPostion = string.Concat(ExportExcelUtil.IndexToColName(dimension.FirstColIndex), dimension.FirstRowIndex + 1);
if (merges != null && merges.Any())
{
string cellValue = merges.FirstOrDefault().DataCell.ToString();
cellData.CellValue = cellValue;
}
}
cellData.Length = StringUtil.StrLength(cellData.CellValue);
return(cellData);
}
private void btnDownload_Click(object sender, EventArgs e)
{
DataTable table = (DataTable)dgvVendor.DataSource;
ExportExcelUtil.SaveAsExcel(table, "COST");
}
private string BuildBillSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtMain, DataTable[] dtSub)
{
string errorMessage = string.Empty;
// 在工作簿建立空白工作表
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetEntity.SheetName))
{
sheet = workBook.CreateSheet(sheetEntity.SheetName);
}
else
{
sheet = workBook.CreateSheet();
}
// 看是否有跳过
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
IRow rowHead = sheet.CreateRow(beginRow);
// 循环添加表头
int colIndex = beginCol;
var tempMast = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
var tempSub = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
var tempCol = tempMast.Union(tempSub);
foreach (var item in tempCol)
{
if (item.Hidden)
{
continue;
}
ICell cell = rowHead.CreateCell(colIndex);
cell.SetCellValue(item.ExcelName);
cell.CellStyle = item.CellStyle;
sheet.SetColumnWidth(colIndex, item.Width);
rowHead.Height = helpEntity.THeight;
IName iname = workBook.CreateName();
iname.NameName = item.ColumnName;
iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
colIndex++;
}
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1);
}
colIndex = beginCol;
beginRow++;
//循环赋值内容
for (int i = 0; i < dtMain.Rows.Count; i++)
{
DataRow drMain = dtMain.Rows[i];
DataTable dtData = dtSub[i];
for (int j = 0; j < dtData.Rows.Count; j++)
{
DataRow drSub = dtData.Rows[j];
IRow row = sheet.CreateRow(beginRow);
ICell cell = null;
colIndex = beginCol;
foreach (var col in tempCol)
{
if (col.Hidden)
{
continue;
}
cell = row.CreateCell(colIndex);
object value = null;
if (col.PrimaryMark == true)
{
value = drMain[col.ColumnName];
if (helpEntity.OneMain == true && j > 0)
{
colIndex++;
continue;
}
}
value = drSub[col.ColumnName];
cell.SetCellValue(value, col, col.CellStyle);
colIndex++;
}
beginRow++;
}
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
private string BuildRecSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataRow drMain, DataTable dtSub)
{
string errorMessage = string.Empty;
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetEntity.SheetName))
{
sheet = workBook.CreateSheet(sheetEntity.SheetName);
}
else
{
sheet = workBook.CreateSheet();
}
// 设置开始行和列
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
#region 对表头数据进行赋值
// 标题行
IRow rowTitle = sheet.CreateRow(beginRow);
ICell cellTitle = rowTitle.CreateCell(beginCol);
cellTitle.SetCellValue(sheetEntity.SheetTitle);
// 设置下划线
IFont fontLine = workBook.CreateFont();
fontLine.Underline = FontUnderlineType.Single;
if (helpEntity.TitleBoldMark == true)
{
fontLine.FontHeight = (double)FontBoldWeight.Bold;
}
cellTitle.CellStyle.SetFont(fontLine);
cellTitle.CellStyle.Alignment = HorizontalAlignment.Center;
cellTitle.CellStyle.VerticalAlignment = VerticalAlignment.Center;
rowTitle.Height = helpEntity.THeight;
// 标题行合并
sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, beginCol, beginCol + helpEntity.ExportColumns.Count() - 1));
beginRow++;
// 表头字段集合
var masterColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
var subColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
int maxColIndex = masterColumn.Select(t => t.ColIndex).Max();
int minColIndex = masterColumn.Select(t => t.ColIndex).Min();
var listTitleContent = masterColumn;
var temp = masterColumn.Select(t => t.RowIndex).Distinct();
int colIndex = beginCol;
// 循环赋值表头数据
foreach (var rowIndex in temp)
{
IRow rowContent = sheet.CreateRow(beginRow);
var curRow = listTitleContent.Where(t => t.RowIndex == rowIndex).OrderBy(t => t.ColIndex);
colIndex = beginCol;
var curColMinIndex = curRow.Select(t => t.ColIndex).Min();
var diff = curColMinIndex - minColIndex;
// 此处判断主要是为了有跳过列的
if (diff > 0)
{
// 标题
colIndex += diff;
// 内容
colIndex += diff;
}
foreach (var item in curRow)
{
colIndex = colIndex - item.diffNum;
// 先赋值标题，再赋值
ICell curTitle = rowContent.CreateCell(colIndex);
curTitle.SetCellValue(item.ExcelName);
curTitle.CellStyle = item.CellStyle;
IName iname = workBook.CreateName();
iname.NameName = item.ColumnName;
iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
colIndex++;
ICell cell = rowContent.CreateCell(colIndex);
string curColumn = item.ColumnName;
object curValue = drMain[curColumn];
if (item.TitleColSpan > 1)
{
sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, colIndex, colIndex + item.TitleColSpan - 1));
colIndex++;
}
cell.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
beginRow++;
}
#endregion
// 获取和循环设置表体表体行
var listTitle = helpEntity.ExportColumns;
IRow rowSubTitle = sheet.CreateRow(beginRow);
colIndex = beginCol;
foreach (var item in listTitle)
{
ICell cellSubTitle = rowSubTitle.CreateCell(colIndex);
cellSubTitle.SetCellValue(item.ExcelName);
sheet.SetColumnWidth(colIndex, item.Width);
colIndex++;
}
beginRow++;
// 冻结上述行和列
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(beginCol, beginRow - 1, beginCol, beginRow - 1);
}
// 循环赋值列表数据
foreach (DataRow dr in dtSub.Rows)
{
IRow rowSubContent = sheet.CreateRow(beginRow);
colIndex = beginCol;
foreach (var item in listTitle)
{
ICell cellSubContent = rowSubContent.CreateCell(colIndex);
object curValue = dr[item.ColumnName];
// 设置表体内容
cellSubContent.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
beginRow++;
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum + 1, 0 + helpEntity.SkipRowNum + 1, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
/// <summary>
/// 处理每个工作表数据
/// </summary>
/// <param name="dtData"></param>
/// <param name="helpEntity"></param>
/// <returns></returns>
private string BuildSinpleSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtData)
{
string errorMessage = string.Empty;
// 在工作簿建立空白工作表
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetEntity.SheetName))
{
sheet = workBook.CreateSheet(sheetEntity.SheetName);
}
else
{
sheet = workBook.CreateSheet();
}
// 看是否有跳过
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
IRow rowHead = sheet.CreateRow(beginRow);
// 循环添加表头
int colIndex = beginCol;
foreach (var item in helpEntity.ExportColumns)
{
if (item.Hidden)
{
continue;
}
ICell cell = rowHead.CreateCell(colIndex);
cell.SetCellValue(item.ExcelName);
cell.CellStyle = item.CellStyle;
sheet.SetColumnWidth(colIndex, item.Width);
rowHead.Height = helpEntity.THeight;
IName iname = workBook.CreateName();
iname.NameName = item.ColumnName;
iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
colIndex++;
}
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1);
}
colIndex = beginCol;
beginRow++;
//循环赋值内容
foreach (DataRow dr in dtData.Rows)
{
IRow rowContent = sheet.CreateRow(beginRow);
ICell cell = null;
colIndex = beginCol;
foreach (var item in helpEntity.ExportColumns)
{
if (item.Hidden)
{
continue;
}
cell = rowContent.CreateCell(colIndex);
object curValue = dr[item.ColumnName];
cell.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
rowContent.Height = (short)helpEntity.CHeight;
beginRow++;
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
