老师布置了一个任务，就是从txt文件中提取到一些信息
然后有一个Excel的模板
需要把数据导入进去，并且完成校验，最后保存Excel文件等一系列事件

我首先想到了国人开发的NPOI—因为它可以处理Excel和Word文件等等
在我处理的过程中，出现了一个问题，可能因为自己学艺不精，所以在此贴出自己写的代码为以后学会了纠错

方法一：NPOI——现在没有找到为什么ShiftRow会删除下两行的内容的问题

这些代码分别是打开workbook、插入N行数据

  2.         public HSSFWorkbook NPOIOpenExcel(string FileName)
  3.         {
  4.             HSSFWorkbook MyHSSFWorkBook;
  5.             FileStream fs = new FileStream(FileName, FileMode.Open);
  6.             MyHSSFWorkBook = new HSSFWorkbook(fs);
  7.             return MyHSSFWorkBook;
  8.         }
  10.         public XSSFWorkbook NPOIOpenExcel2(string FileName)
  11.         {
  12.             XSSFWorkbook MyXSSFWorkBook;
  13.             FileStream fs = new FileStream(FileName, FileMode.Open);
  14.             MyXSSFWorkBook = new XSSFWorkbook(fs);
  15.             return MyXSSFWorkBook;
  16.         }
  20.         //参数说明
  21.         //第一个:指定操作的Sheet。
  22.         //第二个:指定在第几行指入（插入行的位置）
  23.         //第三个:指定要插入多少行
  24.         //第四个:源单元格格式的行，
  25.         private void MyInsertRow(ref HSSFSheet sheet, int startrow, int rowcount, HSSFRow FormatRow)
  26.         {
  27.             //批量移动行
  28.             sheet.ShiftRows(startrow,sheet.LastRowNum,rowcount,true,false,true);
  29.             //对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)
  30.             for (int i = startrow; i < startrow + rowcount - 1; i++)
  31.             {
  32.                 HSSFRow targetRow = null;
  33.                 HSSFCell sourceCell = null;
  34.                 HSSFCell targetCell = null;
  35.                 targetRow = (HSSFRow)sheet.CreateRow(i + 1);
  36.                 for (int m = FormatRow.FirstCellNum; m < FormatRow.LastCellNum; m++)
  37.                 {
  38.                     sourceCell = (HSSFCell)FormatRow.GetCell(m);
  39.                     if (sourceCell == null)
  40.                         continue;
  41.                     targetCell = (HSSFCell)targetRow.CreateCell(m);
  42.                     //targetCell.Encoding = sourceCell.Encoding;
  43.                     targetCell.CellStyle = sourceCell.CellStyle;
  44.                     targetCell.SetCellType(sourceCell.CellType);
  45.                 }
  46.                 //CopyRow(sourceRow, targetRow);
  47.                 //Util.CopyRow(sheet, sourceRow, targetRow);
  48.             }
  49.             HSSFRow firstTargetRow = (HSSFRow)sheet.GetRow(startrow);
  50.             HSSFCell firstSourceCell = null;
  51.             HSSFCell firstTargetCell = null;
  52.             for (int m = FormatRow.FirstCellNum; m < FormatRow.LastCellNum; m++)
  53.             {
  54.                 firstSourceCell = (HSSFCell)FormatRow.GetCell(m);
  55.                 if (firstSourceCell == null)
  56.                     continue;
  57.                 firstTargetCell = (HSSFCell)firstTargetRow.CreateCell(m);
  58.                 //firstTargetCell.Encoding = firstSourceCell.Encoding;
  59.                 firstTargetCell.CellStyle = firstSourceCell.CellStyle;
  60.                 firstTargetCell.SetCellType(firstSourceCell.CellType);
  61.             }
  62.         }
  65.         private void MyInsertRow(ref XSSFSheet sheet, int startrow, int rowcount, XSSFRow FormatRow)
  66.         {
  67.             //批量移动行
  68.             sheet.ShiftRows(startrow, sheet.LastRowNum, rowcount, true, false);
  69.             //对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)
  70.             for (int i = startrow; i < startrow + rowcount - 1; i++)
  71.             {
  72.                 XSSFRow targetRow = null;
  73.                 XSSFCell sourceCell = null;
  74.                 XSSFCell targetCell = null;
  75.                 targetRow = (XSSFRow)sheet.CreateRow(i + 1);
  76.                 for (int m = FormatRow.FirstCellNum; m < FormatRow.LastCellNum; m++)
  77.                 {
  78.                     sourceCell = (XSSFCell)FormatRow.GetCell(m);
  79.                     if (sourceCell == null)
  80.                         continue;
  81.                     targetCell = (XSSFCell)targetRow.CreateCell(m);
  82.                     //targetCell.Encoding = sourceCell.Encoding;
  83.                     targetCell.CellStyle = sourceCell.CellStyle;
  84.                     targetCell.SetCellType(sourceCell.CellType);
  85.                 }
  86.                 //CopyRow(sourceRow, targetRow);
  87.                 //Util.CopyRow(sheet, sourceRow, targetRow);
  88.             }
  89.             XSSFRow firstTargetRow = (XSSFRow)sheet.GetRow(startrow);
  90.             XSSFCell firstSourceCell = null;
  91.             XSSFCell firstTargetCell = null;
  92.             for (int m = FormatRow.FirstCellNum; m < FormatRow.LastCellNum; m++)
  93.             {
  94.                 firstSourceCell = (XSSFCell)FormatRow.GetCell(m);
  95.                 if (firstSourceCell == null)
  96.                     continue;
  97.                 firstTargetCell = (XSSFCell)firstTargetRow.CreateCell(m);
  98.                 //firstTargetCell.Encoding = firstSourceCell.Encoding;
  99.                 firstTargetCell.CellStyle = firstSourceCell.CellStyle;
  100.                 firstTargetCell.SetCellType(firstSourceCell.CellType);
  101.             }
  102.         }
  104.         private void SaveExcel(string dir,XSSFWorkbook workbook)
  105.         {
  106.             using (FileStream files = new FileStream(dir, FileMode.Create))
  107.             {
  108.                 workbook.Write(files);
  109.             }
  110.         }

image.png

但是插入N行之后，最后三行footer内容消失了
image.png
用的是这个方法

  1. private void ButtonClick(object sender,ItemClickEventArgs e)
  2. {
  3.             DirectoryInfo d = GetDirectoryInfo(3);
  4.             string modelPath = d.FullName + "CTCS_ExcelModel.xlsx";
  5.             List<TxtPoint> points = new List<TxtPoint>();
  6.             points.Add(new TxtPoint { Bear = 0.29038476533, DeltaBear = 0.089987576 });
  7.             points.Add(new TxtPoint { Bear = 0.23984676, DeltaBear = 0.1231243566 });
  8.             points.Add(new TxtPoint { Bear = 0.012398675, DeltaBear = 11.2345645756 });
  9.             points.Add(new TxtPoint { Bear = 0.29038476533, DeltaBear = 0.089987576 });
  10.             points.Add(new TxtPoint { Bear = 0.23984676, DeltaBear = 0.1231243566 });
  11.             points.Add(new TxtPoint { Bear = 0.012398675, DeltaBear = 11.2345645756 });
  12.             points.Add(new TxtPoint { Bear = 0.012398675, DeltaBear = 11.2345645756 });
  13.             points.Add(new TxtPoint { Bear = 0.29038476533, DeltaBear = 0.089987576 });
  14.             points.Add(new TxtPoint { Bear = 0.23984676, DeltaBear = 0.1231243566 });
  15.             points.Add(new TxtPoint { Bear = 0.012398675, DeltaBear = 11.2345645756 });
  16.             XSSFWorkbook workbook = NPOIOpenExcel2(modelPath);
  17.             XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
  18.             XSSFRow formatRow = (XSSFRow)sheet.GetRow(3);
  19.             MyInsertRow(ref sheet, 6, points.Count - 4, formatRow);
  21.             ////ImportData(ref sheet, 2, points);//导入数据
  22.             SaveExcel(d.FullName + "副本CTCS_ExcelModel.xlsx", workbook);  
  23. }

否则用这个方法，则会出现这个问题

  1. /// <summary>
  2.         /// NPOI使用ShiftRows向Excel插入行,并复制原有样式
  3.         /// </summary>
  4.         /// <param name="file"></param>模板文件
  5.         /// <param name="dir">导出路径</param>
  6.         public void ShiftRows(string file,string dir,List<TxtPoint> txtPoints)
  7.         {
  8.             try
  9.             {
  10.                 FileStream fs = new FileStream(file, FileMode.Open);
  11.                 XSSFWorkbook workbook = new XSSFWorkbook(fs);
  12.                 ISheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
  13.                 int startRow = 3;// 6;//开始插入行索引
  14.                 //列控数据表0-地理信息表默认可填充四行数据
  15.                 //当导入的DataTable超出4行时,使用ShiftRows插入行
  16.                 if (txtPoints.Count > 4)
  17.                 {
  18.                     //插入行
  19.                     Console.WriteLine(sheet.LastRowNum.ToString());
  20.                     sheet.ShiftRows(startRow, sheet.LastRowNum, txtPoints.Count - 4, false, false);// txtPoints.Count - 4, true, false);
  21.                     //sheet.ShiftRows(startRow, sheet.LastRowNum, txtPoints.Count - 4, true, false);
  23.                     var rowSource = sheet.GetRow(2);
  24.                     var rowStyle = rowSource.RowStyle;//获取当前行样式
  25.                     for (int i = startRow; i < startRow + txtPoints.Count-1; i++)
  26.                     {
  27.                         var rowInsert = sheet.CreateRow(i);
  28.                         if (rowStyle != null)
  29.                         {
  30.                             rowInsert.RowStyle = rowStyle;
  31.                             rowInsert.Height = rowSource.Height;
  32.                         }
  33.                         for (int col = 0; col < rowSource.LastCellNum; col++)
  34.                         {
  35.                             var cellsource = rowSource.GetCell(col);
  36.                             var cellInsert = rowInsert.CreateCell(col);
  37.                             var cellStyle = cellsource.CellStyle;
  38.                             //设置单元格样式
  39.                             if (cellStyle != null)
  40.                             {
  41.                                 cellInsert.CellStyle = cellsource.CellStyle;
  42.                             }
  43.                         }
  44.                     }
  45.                 }
  46.                 //绑定数据
  47.                 for (int j = 0; j < txtPoints.Count; j++)
  48.                 {
  49.                     TxtPoint t = txtPoints[j];
  50.                     IRow r = sheet.GetRow(j + 2);
  51.                     r.Cells[0].SetCellValue(j + 1);
  52.                     r.Cells[1].SetCellValue("江仓站");
  53.                     r.Cells[9].SetCellValue(t.Bear);
  54.                     r.Cells[10].SetCellValue(t.DeltaBear);
  55.                 }
  57.                 //sheet.GetRow
  58.                 //后续操作
  59.                 using (FileStream files = new FileStream(dir, FileMode.Create))
  60.                 {
  61.                     workbook.Write(files);
  62.                 }
  63.                 fs.Dispose();
  64.             }
  65.             catch(Exception ex)
  66.             {
  67.                 XtraMessageBox.Show(ex.Message);
  68.             }
  69.         }

不仅出现下三行内容消失，新增的行高度和格式也不同

这真是个无解的问题
在我一筹莫展的过了一天，然后想到DevExpress有一个可视的报表软件SpreadSheetControl

方法二：解决了我之前的更改模板的问题，而且可以在不影响Header和Footer的情况下，在中间插入格式的N行，并且暂时没发现问题！Nice！

我是直接用的DevExpress的Template Gallery中的SpreadSheet
步骤如下
基本操作，选择项目
image.png
找到SpreadSheet就可以很方便啦
image.png

首先贴出一些常用的打开。保存。插入N行的代码

  1. //打开文件操作
  2.         public static string OpenExcel()
  3.         {
  4.             OpenFileDialog fileDialog = new OpenFileDialog();
  5.             fileDialog.Multiselect = true;
  6.             fileDialog.Title = "请选择文件";
  7.             fileDialog.Filter = "所有文件(*.xls)|*.xls"; //设置要选择的文件的类型
  8.             if (fileDialog.ShowDialog() == DialogResult.OK)
  9.             {
  10.                 return fileDialog.FileName;//返回文件的完整路径               
  11.             }
  12.             else
  13.             {
  14.                 return null;
  15.             }
  17.         }
  19. //导入Excel
  20.         private void ImportData(string filePath)//导入按钮
  21.         {
  22.             if (!string.IsNullOrEmpty(filePath))
  23.             {
  24.                 IWorkbook workbook = spreadsheetControl.Document;
  25.                 workbook.LoadDocument(filePath);
  26.             }
  27.         }
  29. //保存Excel文件
  30.         private void SaveExcel()
  31.         {
  32.             try
  33.             {
  34.                 string s = GetDirectoryInfo(3).FullName + "副本CTCS_ExcelModel.xlsx";
  35.                 spreadsheetControl.SaveDocument(s,DocumentFormat.Xlsx);
  36.             }
  37.             catch (Exception ExError)
  38.             {
  39.                 XtraMessageBox.Show("该文件正被别的地方占用");
  40.                 ExError.ToString();
  41.             }
  42.         }
  45.         public static DirectoryInfo GetDirectoryInfo(int index)
  46.         {
  47.             string directoryindex = "";
  48.             for (int i = 0; i < index; i++)
  49.             {
  50.                 directoryindex += @"..\";
  51.             }
  52.             DirectoryInfo di = new DirectoryInfo(string.Format("{0}{1}", System.Windows.Forms.Application.StartupPath, directoryindex));
  53.             return di;
  54.         }

不过本来写了这些打开、导入方法，好像这个demo已经包含了这些功能
所以这些我就注释掉了

  1. private void spreadsheetCommandBarButtonItem190_ItemClick(object sender, ItemClickEventArgs e)
  2.         {
  3.             //DirectoryInfo di = GetDirectoryInfo(3);
  4.             //string file = di.FullName + "CTCS_ExcelModel.xlsx";
  5.             //ImportExcel(file);
  6.         }

一些插入行和获取行列信息的代码

  1. //访问行
  2.         Workbook workbook=new Workbook();
  3.         //Access a Collection of rows
  4.         RowCollection rows = workbook.Worksheets[0].Rows;
  5.         //Access the first row by its index in the collection of rows;
  6.         Row firstRow_byIndex = rows[0];
  7.         //Access ths first row by its unique name
  8.         Row firstRow_byName = rows["1"];
  11.         访问列
  12.         Workbook workbook=new Workbook();
  13.         //Access a collection of columns
  14.         ColumnCollection columns = workbook.Worksheets[0].Columns.
  15.         //ColumnCollection columns=workbook.Worksheets[0].Columns;
  16.         Column firstColumn_byIndex = columns[0];
  17.         //Access the first column by its unique name;
  18.         Column firstColumn_byName = columns["A"];//

一些更具体的使用方法来自如下：
https://blog.csdn.net/hotmee/article/details/50554381

接下来插入N行的思路是:先在指定位置添加Rows，这些Row是空白的无格式的，之后就需要CopyFrom，从现有行复制模板
这里要注意Insert（int index，int count）；这里的index指的是，
image.png

  1.         /// <summary>
  2.         /// 插入N行
  3.         /// </summary>
  4.         /// <param name="workbook">Excel工作区</param>
  5.         /// <param name="sheetIndex">sheet索引</param>
  6.         /// <param name="startRowIndex">开始插入行的索引(向下插入表的)</param>
  7.         /// <param name="rowcount">插入的行数</param>
  8.         private void InsertRows(IWorkbook workbook,int sheetIndex,int startRowIndex,int rowcount)
  9.         {
  10.             Worksheet sheet = workbook.Worksheets[sheetIndex];
  11.             RowCollection rows = sheet.Rows;
  12.             //example:
  13.             //sheet.Rows.Insert(6, 5);//在行索引为6的行下方插入5行,即增加7、8、9、10、11
  14.             sheet.Rows.Insert(startRowIndex, rowcount);
  15.             for(int j=startRowIndex; j<startRowIndex+rowcount;j++)
  16.             {
  17.                 sheet.Rows[j].CopyFrom(sheet.Rows[3]);//复制行格式
  18.             }
  19.         }
  22. //使用方法//我在第6行下方添加了5行，并更改了格式
  23. //InsertRows(spreadsheetControl.Document, 0,6,5);

图示：
image.png
插入5行
image.png
稍微更改了一下
加上了formatRow的index

  1. /// <summary>
  2.         /// 插入N行
  3.         /// </summary>
  4.         /// <param name="workbook">Excel工作区</param>
  5.         /// <param name="sheetIndex">sheet索引</param>
  6.         /// <param name="startRowIndex">开始插入行的索引(向下插入表的)</param>
  7.         /// <param name="rowcount">插入的行数</param>
  8.         /// <param name="formatRowIndex">格式行的索引值</param>
  9.         private void InsertRows(IWorkbook workbook,int sheetIndex,int startRowIndex,int rowcount,int formatRowIndex)
  10.         {
  11.             Worksheet sheet = workbook.Worksheets[sheetIndex];
  12.             RowCollection rows = sheet.Rows;
  13.             //example:
  14.             //sheet.Rows.Insert(6, 5);//在行索引为6的行下方插入5行,即增加7、8、9、10、11
  15.             sheet.Rows.Insert(startRowIndex, rowcount);
  16.             for(int j=startRowIndex; j<startRowIndex+rowcount;j++)
  17.             {
  18.                 sheet.Rows[j].CopyFrom(sheet.Rows[formatRowIndex-1]);//复制行格式
  19.             }
  20.         }

就成了复制第三行的样式
image.png

SpreadSheet合并和取消合并方法
//取消合并
sheet.UnMergeCells(sheet.Range[“D2:J2”]);、
//合并
sheet.MergeCells(sheet.Range[“D2:D3”]);
行删除
//更改cell的边框格式为OutLine
sheet.Cells[“D2”].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);