/// <summary>
/// 把PivotGridControl控件导出的Excel再整理(设置第一行标题,去掉合计,取消合并单元格)
/// </summary>
public static void Tidy(string filePath, string[] column,int columnCount)
{
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
//设置第一行的标题
for (int i = 0; i < column.Length; i++)
{
sheet.GetRow(0).CreateCell(i).SetCellValue(column[i]);
}
List<int> rowsIndex = new List<int>();
int rowCount = sheet.LastRowNum;
HSSFCell cell;
//获取包含合计的行号
for (int i = 0; i <= rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
cell = (HSSFCell)sheet.GetRow(i).GetCell(j);
if (cell != null && (cell.StringCellValue.Contains("合计") || cell.StringCellValue.Contains("总计")))
{
sheet.RemoveRow(sheet.GetRow(i));//删除合计行相当于清空
rowsIndex.Add(i);
break;
}
}
}
//删除合计行Excel中通过单元格移动来实现
int[] rowOfArray = rowsIndex.ToArray();
for (int i = rowOfArray.Length - 1; i >=0; i--)
{
if (rowOfArray[i] < sheet.LastRowNum)
sheet.ShiftRows(rowOfArray[i] + 1, sheet.LastRowNum, -1);
}
//填充合并的单元格
rowCount = sheet.LastRowNum;
for (int i = 0; i <= rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
cell = (HSSFCell)sheet.GetRow(i).GetCell(j);
if (cell == null)
{
cell = (HSSFCell)sheet.GetRow(i).CreateCell(j);
cell.SetCellValue(sheet.GetRow(i - 1).GetCell(j).StringCellValue);
}
else
{
if (string.IsNullOrEmpty(cell.StringCellValue))
{
cell.SetCellValue(sheet.GetRow(i-1).GetCell(j).StringCellValue);
}
}
}
}
//写回Excel
using (FileStream filess = File.OpenWrite(filePath))
{
workbook.Write(filess);
}
}