/// <summary>
/// 插入Excel行
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
/// <param name="row"></param>
private static void MyInsertRow(HSSFSheet sheet, int rowIndex, int count, HSSFRow row)
{
#region 批量移动行
sheet.ShiftRows(
rowIndex, //--开始行
sheet
.LastRowNum, //--结束行
count, //--移动大小(行数)--往下移动
true, //是否复制行高
false, //是否重置行高
true //是否移动批注
);
#endregion
#region 对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)
for (int i = rowIndex; i < rowIndex + count - 1; i++)
{
HSSFRow targetRow;
HSSFCell sourceCell;
HSSFCell targetCell;
targetRow = sheet.CreateRow(i + 1);
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
sourceCell = row.GetCell(m);
if (sourceCell == null)
continue;
targetCell = targetRow.CreateCell(m);
targetCell.Encoding = sourceCell.Encoding;
targetCell.CellStyle = sourceCell.CellStyle;
targetCell.SetCellType(sourceCell.CellType);
}
//CopyRow(sourceRow, targetRow);
//Util.CopyRow(sheet, sourceRow, targetRow);
}
HSSFRow firstTargetRow = sheet.GetRow(rowIndex);
HSSFCell firstSourceCell;
HSSFCell firstTargetCell = null;
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
firstSourceCell = row.GetCell(m);
if (firstSourceCell == null)
continue;
firstTargetCell = firstTargetRow.CreateCell(m);
firstTargetCell.Encoding = firstSourceCell.Encoding;
firstTargetCell.CellStyle = firstSourceCell.CellStyle;
firstTargetCell.SetCellType(firstSourceCell.CellType);
}
sheet.AddMergedRegion(new Region(firstTargetRow.RowNum, 3, firstTargetRow.RowNum, 4));
#endregion
}
/// <summary>
/// HSSFRow Copy Command
///
/// Description: Inserts a existing row into a new row, will automatically push down
/// any existing rows. Copy is done cell by cell and supports, and the
/// command tries to copy all properties available (style, merged cells, values, etc...)
/// </summary>
/// <param name="workbook">Workbook containing the worksheet that will be changed</param>
/// <param name="worksheet">WorkSheet containing rows to be copied</param>
/// <param name="sourceRowNum">Source Row Number</param>
/// <param name="destinationRowNum">Destination Row Number</param>
private HSSFRow CopyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
{
// Get the source / new row
var newRow = (HSSFRow)worksheet.GetRow(destinationRowNum);
var sourceRow = (HSSFRow)worksheet.GetRow(sourceRowNum);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
}
else
{
newRow = (HSSFRow)worksheet.CreateRow(destinationRowNum);
}
// Loop through source columns to add to new row
for (var i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
var oldCell = (HSSFCell)sourceRow.GetCell(i);
var newCell = (HSSFCell)newRow.CreateCell(i);
// If the old cell is null jump to next cell
if (oldCell == null) continue;
// Copy style from old cell and apply to new cell
var newCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
newCell.CellStyle = newCellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.BLANK:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.BOOLEAN:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.ERROR:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.FORMULA:
newCell.CellFormula = oldCell.CellFormula;
break;
case CellType.NUMERIC:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.STRING:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (var i = 0; i < worksheet.NumMergedRegions; i++)
{
var cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow != sourceRow.RowNum) continue;
var newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.FirstRow -
cellRangeAddress.LastRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
return newRow;
}
/// <summary>
/// 生成子表数据Excel
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
private int CreateSonTable(HSSFSheet sheet, int rowIndex)
{
var hamwitemArry = _hawbDataSource.HAWBItems;
if (hamwitemArry == null || hamwitemArry.Count == 0) return 0;
var currentRow = rowIndex;
var lastIndexRow = 0;
var sourcerow = sheet.GetRow(rowIndex);
foreach (var hamwitem in hamwitemArry)
{
if (currentRow != rowIndex)
{
MyInsertRow(sheet, currentRow, 1, sourcerow);
}
var row = sheet.GetRow(currentRow);
row.Height = 840;
if (!string.IsNullOrEmpty(hamwitem.Name) && !string.IsNullOrEmpty(hamwitem.Remark))
{
row.GetCell(3).SetCellValue(hamwitem.Name.ToUpper() + hamwitem.Remark.ToUpper());
}
else
{
row.GetCell(3).SetCellValue(hamwitem.Name + hamwitem.Remark);
}
row.GetCell(5).SetCellValue(hamwitem.Piece);
row.GetCell(6).SetCellValue(Convert.ToString(hamwitem.UnitAmount));
row.GetCell(7).SetCellValue(Convert.ToString(hamwitem.TotalAmount));
lastIndexRow = row.RowNum;
currentRow += 1;
}
var tempRow = sheet.GetRow(rowIndex);
tempRow.GetCell(8).SetCellValue(Convert.ToString(_hawbDataSource.TotalWeight));
var totalValue = Convert.ToString(hamwitemArry.Sum(p => p.TotalAmount));
tempRow.GetCell(9).SetCellValue(totalValue);
if (hamwitemArry.Count < 6)
{
var count = 6 - hamwitemArry.Count;
for (var i = 0; i < count; i++)
{
MyInsertRow(sheet, currentRow, 1, sourcerow);
var row = sheet.GetRow(currentRow);
lastIndexRow = row.RowNum;
currentRow += 1;
}
}
sheet.AddMergedRegion(new Region(rowIndex, 8, lastIndexRow, 8));
sheet.AddMergedRegion(new Region(rowIndex, 9, lastIndexRow, 9));
//var deleteRow = sheet.GetRow(rowIndex);
//DeleteRow(sheet, deleteRow);
//lastIndexRow=lastIndexRow - 1;
return lastIndexRow;
}
/// <summary>
/// 合并Gridview单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
public static void SetCellRangeAddress(HSSFSheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}
