private static void CopyCell(XSSFCell oldCell, HSSFCell newCell, Dictionary <int, XSSFCellStyle> styleMap, HSSFWorkbook retVal)
{
if (styleMap != null)
{
int stHashCode = oldCell.CellStyle.Index;
XSSFCellStyle sourceCellStyle = null;
if (styleMap.TryGetValue(stHashCode, out sourceCellStyle))
{
}
HSSFCellStyle destnCellStyle = (HSSFCellStyle)newCell.CellStyle;
if (sourceCellStyle == null)
{
sourceCellStyle = (XSSFCellStyle)oldCell.Sheet.Workbook.CreateCellStyle();
}
// destnCellStyle.CloneStyleFrom(oldCell.CellStyle);
if (!styleMap.Any(p => p.Key == stHashCode))
{
styleMap.Add(stHashCode, sourceCellStyle);
}
destnCellStyle.VerticalAlignment = VerticalAlignment.Top;
newCell.CellStyle = (HSSFCellStyle)destnCellStyle;
}
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
default:
break;
}
}
/// <summary>
/// Update the formulas in specified row using the formula shifting policy specified by shifter
/// </summary>
/// <param name="row">the row to update the formulas on</param>
/// <param name="formulaShifter">the formula shifting policy</param>
public static void UpdateRowFormulas(IRow row, FormulaShifter formulaShifter)
{
ISheet sheet = row.Sheet;
foreach (ICell c in row)
{
HSSFCell cell = (HSSFCell)c;
String formula = cell.CellFormula;
if (formula.Length > 0)
{
String shiftedFormula = ShiftFormula(row, formula, formulaShifter);
cell.SetCellFormula(shiftedFormula);
}
}
}
public void RecalculateSheet()
{
try
{
// recalc
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(this._CurrentSheet, this.book);
for (int iSheet = 0; iSheet < this.book.NumberOfSheets; iSheet++)
{
HSSFSheet sheet = this.book.GetSheetAt(iSheet);
int rows = this._CurrentSheet.LastRowNum;
for (int r = 0; r < rows; r++)
{
HSSFRow row = sheet.GetRow(r);
if (row == null)
{
continue;
}
evaluator.SetCurrentRow(row);
int first = row.FirstCellNum;
int last = row.LastCellNum;
for (int c = first; c < last; c++)
{
try
{
HSSFCell cell = row.GetCell(c);
if (cell != null &&
cell.CellType == HSSFCell.CELL_TYPE_FORMULA)
{
String formula = cell.CellFormula;
if (formula != null)
{
evaluator.EvaluateInCell(cell);
cell.SetCellFormula(formula);
}
}
}
catch (System.Exception ex)
{ }
}
}
}
}
catch (System.Exception ex)
{
}
}
public void setCellValue(int row, int col, double sValue)
{
try
{
if (this._CurrentSheet.GetRow(row) != null && this._CurrentSheet.GetRow(row).GetCell(col) != null)
{
HSSFCell celda = this._CurrentSheet.GetRow(row).GetCell(col);
celda.SetCellFormula(null);
celda.SetCellValue(sValue);
}
}
catch (Exception ex)
{
Logger.Instance.LogException(Logger.typeUserActions.INSERT, "error seteando string en celda", ref ex);
}
}
private void CheckReport(HSSFWorkbook wb, DataSet ds, DataSet dsFrom1104Formula, List <string> list_FormulaTableName)
{
for (int i = 0; i < wb.NumberOfSheets; i++)
{
HSSFSheet sheet = wb.GetSheetAt(i) as HSSFSheet; //读取wb中当前sheet的数据
DataTable dt_FromSum = ds.Tables[ds.Tables[0].TableName.Substring(0, 10) + sheet.SheetName]; //取出对应ds中的dt，需加上前缀，如“汇总20190930”
DataGridView dgv_FromSum = new DataGridView();
//从list_Dgv中取出对应的Dgv
foreach (DataGridView _dgv in list_Dgv)
{
if (_dgv.Name == sheet.SheetName)
{
dgv_FromSum = _dgv;
break;
}
}
string dt_Formula_Name = sheet.SheetName;
DataTable dtOfCheck = new DataTable();
//如果DB中存在此表校验公式的表
if (list_FormulaTableName.Contains(dt_Formula_Name))
{
foreach (DataTable dt_Checking in dsFrom1104Formula.Tables)
{
if (dt_Checking.TableName == dt_Formula_Name)
{
dtOfCheck = dt_Checking;
break;
}
}
if (dtOfCheck.Rows.Count > 0)
{
//依次取出dtOfCheck中的每行，即每个公式，赋值到wbForImportingChecking中对应的sheet中，开始校验
foreach (DataRow checking_Row in dtOfCheck.Rows)
{
int checking_RowIndex = int.Parse(checking_Row[0].ToString());
int checking_ColumnIndex = int.Parse(checking_Row[1].ToString());
HSSFCell checking_Cell = new HSSFCell(wb, sheet, checking_RowIndex, short.Parse(checking_ColumnIndex.ToString()));
//checking_Cell = sheet.GetRow(checking_RowIndex).GetCell(checking_ColumnIndex) as HSSFCell; //这样会出现null的Cell
string str_Formula_Original = checking_Row[2].ToString();
string str_Formula = str_Formula_Original;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].ToolTipText = str_Formula;
//检验单元格的值：（此时cell的ToString返回的是公式，需将其计算后，才能得到校验的值)
//计算公式
try
{
HSSFFormulaEvaluator ev = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator.SetupEnvironment(new string[] { wb.SummaryInformation.Title }, new HSSFFormulaEvaluator[] { ev });
Dictionary <string, IFormulaEvaluator> dic_Wb = new Dictionary <string, IFormulaEvaluator>();
dic_Wb.Add(wb.SummaryInformation.Title, ev as IFormulaEvaluator);
ev.SetupReferencedWorkbooks(dic_Wb);
ev.IgnoreMissingWorkbooks = true;
checking_Cell.SetCellFormula(str_Formula);
ev.EvaluateInCell(checking_Cell);
}
catch (Exception ex)
{
//公式计算有问题的单元格设置颜色和备注
//设置颜色
HSSFCellStyle cellStyleOfWrong = wbForImportingChecking.CreateCellStyle() as HSSFCellStyle;
cellStyleOfWrong.FillBackgroundColor = HSSFColor.DarkYellow.Index;
checking_Cell.CellStyle = cellStyleOfWrong;
//创建批注
HSSFPatriarch patr = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment_NullWarning = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, checking_ColumnIndex, checking_RowIndex, checking_ColumnIndex, checking_RowIndex));
//设置批注的内容和作者
comment_NullWarning.String = new HSSFRichTextString("此单元格无法计算公式(有可能是未导入引用的汇总表)，请自行校验");
comment_NullWarning.Author = "LYF";
checking_Cell.CellComment = comment_NullWarning;
//赋值到Dgv中
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].Style.BackColor = Color.SandyBrown;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].ToolTipText = "此单元格无法计算公式，请自行校验";
continue;
//throw new Exception(ex.Message);
}
string result = checking_Cell.ToString();
//赋值给对应的dt和dgv
dt_FromSum.Rows[checking_RowIndex][checking_ColumnIndex] = result;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].Value = result;
//设置颜色
HSSFCellStyle cellStyleForChecking = wbForImportingChecking.CreateCellStyle() as HSSFCellStyle;
if (double.Parse(result) == 0)
{
cellStyleForChecking.FillBackgroundColor = HSSFColor.Aqua.Index;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].Style.BackColor = Color.Aqua;
}
else if (double.Parse(result) != 0)
{
cellStyleForChecking.FillBackgroundColor = HSSFColor.Red.Index;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].Style.BackColor = Color.Red;
}
checking_Cell.CellStyle = cellStyleForChecking;
}
dgv_FromSum.CellClick += new DataGridViewCellEventHandler(Dgv_CellClick); //添加点击单元格事件
dgv_FromSum.EndEdit();
bs_Dgv.EndEdit();
}
}
}
}
//reference link : http://www.zachhunter.com/2010/05/npoi-copy-row-helper/
/// <summary>複製指定工作表的指定資料列到指定工作表的指定資料列</summary>
/// <param name="sourceSheet">來源工作表</param>
/// <param name="targetSheet">目標工作表</param>
/// <param name="sourceIndex">來源資料列索引</param>
/// <param name="targetIndex">目標資料列索引</param>
private void CopyRow(HSSFSheet sourceSheet
, HSSFSheet targetSheet
, int sourceIndex
, int targetIndex)
{
// Get the source / new row
HSSFRow newRow;
HSSFRow sourceRow;
newRow = targetSheet.GetRow(targetIndex) as HSSFRow;
sourceRow = sourceSheet.GetRow(sourceIndex) as HSSFRow;
if (newRow == null)
{
newRow = targetSheet.CreateRow(targetIndex) as HSSFRow;
}
if (sourceRow == null)
{
sourceRow = sourceSheet.CreateRow(sourceIndex) as HSSFRow;
}
newRow.HeightInPoints = sourceRow.HeightInPoints;
// Loop through source columns to add to new row
//複製儲存格內容
for (int i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
HSSFCell oldCell = sourceRow.GetCell(i) as HSSFCell;
HSSFCell newCell = newRow.CreateCell(i) as HSSFCell;
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
newCell.CellStyle = oldCell.CellStyle;
// 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.SetCellFormula(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;
}
}
//進行合併儲存格設定搬移作業
CellRangeAddress cellRangeAddress;
CellRangeAddress newCellRangeAddress;
for (int i = 0; i < sourceSheet.NumMergedRegions; i++)
{
cellRangeAddress = sourceSheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
newCellRangeAddress = new CellRangeAddress(newRow.RowNum
, (cellRangeAddress.LastRow - cellRangeAddress.FirstRow)
+ newRow.RowNum
, cellRangeAddress.FirstColumn
, cellRangeAddress.LastColumn
);
targetSheet.AddMergedRegion(newCellRangeAddress);
}
}
this.MoveCursorPosition();
}
/// <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="sourceRowNumber">Source Row Number</param>
/// <param name="destinationRowNumber">Destination Row Number</param>
public static void CopyRow(HSSFSheet worksheet, int sourceRowNumber, int destinationRowNumber)
{
// Get the source / new row
HSSFRow newRow = (HSSFRow)worksheet.GetRow(destinationRowNumber);
HSSFRow sourceRow = (HSSFRow)worksheet.GetRow(sourceRowNumber);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
worksheet.ShiftRows(destinationRowNumber, worksheet.LastRowNum, 1);
}
else
{
newRow = (HSSFRow)worksheet.CreateRow(destinationRowNumber);
}
// Loop through source columns to add to new row
for (int i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
HSSFCell oldCell = (HSSFCell)sourceRow.GetCell(i);
HSSFCell newCell = (HSSFCell)newRow.CreateCell(i);
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
//HSSFCellStyle newCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//newCellStyle.CloneStyleFrom(oldCell.CellStyle); ;
newCell.CellStyle = oldCell.CellStyle;
// 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.SetCellFormula(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 (int i = 0; i < worksheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.LastRow -
cellRangeAddress.FirstRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
}
}
/// <summary>
/// 设置单元格公式
/// </summary>
/// <param name="rowNum">行</param>
/// <param name="columnNum">列</param>
/// <param name="formulastring">公式</param>
public void SetCellFormula(int rowNum, int columnNum, string formulastring)
{
HSSFCell cell = GetCell(rowNum, columnNum);
cell.SetCellFormula(formulastring);
}
