把PivotGridControl控件导出的Excel再整理(设置第一行标题，去掉合计，取消合并单元格)

/// <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);
            }

        }