sdfsdfsdfdffor随手记录!
源于公司的报表需求,需要将多个相同格式的报表xlsx文件,合并到同一个xlsx中。报表头一样(前三行为报表头),sheet表一样。
因为项目紧急,还有很多东西没有深入深究,还不够完善。另外问个问题,使用openXml移除最后一行(带公式)后,这个xlxs,用excel打开时,会提示部份内容有问题,并让修复,用wps则没有问题,看看有没有大佬能给点思路。
private void mergeXlsx()
{
string sourceFileName = "D:\\源报表.xlsx";//这个源报表无法用openxml打开,因为是别的组人导出的,所以未深究,先用npoi打开读取内容实现功能先
string mergeFileName = "D:\\合并之后的报表.xlsx";//这个合并后的报表,是直接复制了报表模板,并把后面的数据行和公式去掉,只保留了文件头
//使用npoi读取需要被合并的报表,这个报表无法用openxml打开,可能是报表生成的时候,里面公式有问题
FileStream fileStream = new FileStream(sourceFileName, FileMode.Open, FileAccess.Read);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileStream);
int sheetCount = xssfWorkbook.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
ISheet sheet = xssfWorkbook.GetSheetAt(sheetIndex);
string sheetName = sheet.SheetName;
IRow row = sheet.GetRow(sheet.LastRowNum);
using (SpreadsheetDocument tempateDocument = SpreadsheetDocument.Open(mergeFileName, true))
{
WorkbookPart workbookPart = tempateDocument.WorkbookPart;
DocumentFormat.OpenXml.Spreadsheet.Workbook workbook = workbookPart.Workbook;
DocumentFormat.OpenXml.Spreadsheet.Sheet s = workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(sht => sht.Name == sheetName).FirstOrDefault();
WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(s.Id);
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetdata = wsPart.Worksheet.Elements<DocumentFormat.OpenXml.Spreadsheet.SheetData>().FirstOrDefault();
//复制模板出来的报表-合并后的报表
//-------------单元格样式---------------
WorkbookStylesPart workbookStylesPart = workbookPart.WorkbookStylesPart;
//Stylesheet stylesheet = new Stylesheet();
//定义格式-这里就不要定义新的CellFormats,目标报表本身里面就带了很多格式,如果定义新的格式,会把报表头那些全部冲掉,所以这里添加新的格式
//stylesheet.CellFormats = new CellFormats();
//stylesheet.CellFormats.Count = 2;
//styleIndex =0U
CellFormat cfDefault = new CellFormat();
cfDefault.Alignment = new Alignment();
cfDefault.NumberFormatId = 2;//2代表保留两位小数,对照表在后面
cfDefault.FontId = 0;
cfDefault.BorderId = 0;
cfDefault.FillId = 0;
cfDefault.ApplyAlignment = true;
cfDefault.ApplyBorder = true;
workbookStylesPart.Stylesheet.CellFormats.Append(cfDefault);//添加新的格式
//styleIndex =1U
CellFormat cfContent = new CellFormat();
cfContent.Alignment = new Alignment();
cfContent.NumberFormatId = 10;//10代表百分比,并保留两位小数,对照表在后面
cfContent.FontId = 0;
cfContent.BorderId = 0;
cfContent.FillId = 0;
cfContent.ApplyAlignment = true;
cfContent.ApplyBorder = true;
workbookStylesPart.Stylesheet.CellFormats.Append(cfContent);//添加新的格式
//------------------------------------------
for (int i = 3; i <= sheet.LastRowNum; i++) //对工作表每一行读取
{
row = sheet.GetRow(i);
if (row != null)
{
DocumentFormat.OpenXml.Spreadsheet.Row lastrow = new DocumentFormat.OpenXml.Spreadsheet.Row();
bool isAddrow = true;
for (int j = 0; j < row.LastCellNum; j++) //对工作表每一列读取
{
//获取i行j列数据
if (row.GetCell(j) == null)
{
//业务特殊约定-如果第一列为null或者值为空,本行不加入合并
if (j == 0)
{
isAddrow = false;
break;
}
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = "", DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String) };
DocumentFormat.OpenXml.Spreadsheet.CellValue cellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue();
cellValue.Text = "";
cell.Append(cellValue);
lastrow.AppendChild(cell);
}
else
{
string format = row.GetCell(j).CellStyle.GetDataFormatString().Trim();//通过npoi读出来的单元格格式
object val = GetValueType(xssfWorkbook, row.GetCell(j));
string disVal = val.ToString();
//如果第一列为null或者值为空,不加入合并
if (j == 0 && string.IsNullOrEmpty(disVal))
{
isAddrow = false;
break;
}
if (val.GetType() == typeof(double))
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = "", DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number) };
if (format == "0.00_")
{
//这里取index,不要用workbookStylesPart.Stylesheet.CellFormats.Count,虽然上面添加了cellformat,但是count不会变,ChildElements是count才是对的
cell.StyleIndex = (uint)workbookStylesPart.Stylesheet.CellFormats.ChildElements.Count - 2;
}
else if (format == "0.00%")
{
//这里取index,不要用workbookStylesPart.Stylesheet.CellFormats.Count,虽然上面添加了cellformat,但是count不会变,ChildElements是count才是对的
cell.StyleIndex = (uint)workbookStylesPart.Stylesheet.CellFormats.ChildElements.Count - 1;
}
DocumentFormat.OpenXml.Spreadsheet.CellValue cellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(disVal);
cell.Append(cellValue);
lastrow.AppendChild(cell);
}
else
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = "", DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String) };
DocumentFormat.OpenXml.Spreadsheet.CellValue cellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(disVal);
cell.Append(cellValue);
lastrow.AppendChild(cell);
}
}
}
if (isAddrow)
{
sheetdata.AppendChild(lastrow);
}
}
}
}
}
fileStream.Close();
xssfWorkbook.Close();
}
/// <summary>
/// 单元格值类型
/// </summary>
/// <param name="workbook"></param>
/// <param name="cell"></param>
/// <returns></returns>
private object GetValueType(XSSFWorkbook workbook, ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Blank:
return "";
case NPOI.SS.UserModel.CellType.Boolean:
return cell.BooleanCellValue;
case NPOI.SS.UserModel.CellType.Numeric:
return cell.NumericCellValue;
case NPOI.SS.UserModel.CellType.String:
return cell.StringCellValue;
case NPOI.SS.UserModel.CellType.Error:
return cell.ErrorCellValue;
case NPOI.SS.UserModel.CellType.Formula:
//针对公式列 进行动态计算;注意:公式暂时只支持 数值 字符串类型
XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook);
var formulaValue = eva.Evaluate(cell);
if (formulaValue.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
return formulaValue.NumberValue;
}
else if (formulaValue.CellType == NPOI.SS.UserModel.CellType.String)
{
return formulaValue.StringValue;
}
else
{
return "";
}
default:
return "=" + cell.CellFormula;
}
}
NumberFormatId对照表
ID Format Code
0 General
1 0
2 0.00
3 #,##0
4 #,##0.00
9 0%
10 0.00%
11 0.00E+00
12 # ?/?
13 # ??/??
14 d/m/yyyy
15 d-mmm-yy
16 d-mmm
17 mmm-yy
18 h:mm tt
19 h:mm:ss tt
20 H:mm
21 H:mm:ss
22 m/d/yyyy H:mm
37 #,##0 ;(#,##0)
38 #,##0 ;[Red](#,##0)
39 #,##0.00;(#,##0.00)
40 #,##0.00;[Red](#,##0.00)
45 mm:ss
46 [h]:mm:ss
47 mmss.0
48 ##0.0E+0
49 @
因公司报表需求,需将多个相同格式的xlsx报表文件合并到一个xlsx中,报表头和sheet表相同。项目紧急未深入研究,还不够完善。此外,使用openXml移除最后一行带公式内容后,Excel打开文件提示部分内容有问题需修复,WPS则无此问题,寻求解决思路。
968





