public void TestCloneComment()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch p = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment c = p.CreateComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)5, 5));
c.Column = (1);
c.Row = (2);
c.String = (new HSSFRichTextString("qwertyuio"));
HSSFSheet sh2 = wb.CloneSheet(0) as HSSFSheet;
HSSFPatriarch p2 = sh2.DrawingPatriarch as HSSFPatriarch;
HSSFComment c2 = (HSSFComment)p2.Children[0];
Assert.IsTrue(Arrays.Equals(c2.GetTextObjectRecord().Serialize(), c.GetTextObjectRecord().Serialize()));
Assert.IsTrue(Arrays.Equals(c2.GetObjRecord().Serialize(), c.GetObjRecord().Serialize()));
Assert.IsTrue(Arrays.Equals(c2.NoteRecord.Serialize(), c.NoteRecord.Serialize()));
//everything except spRecord.shapeId must be the same
Assert.IsFalse(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
EscherSpRecord sp = (EscherSpRecord)c2.GetEscherContainer().GetChild(0);
sp.ShapeId = (1025);
Assert.IsTrue(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
}
public void SetComment(HSSFSheet sheet, HSSFCell cell, string reference, string commentText, string author)
{
HSSFPatriarch patr = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment1 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));
comment1.String = new HSSFRichTextString(commentText);
comment1.Author = author;
cell.CellComment = comment1;
}
/// <summary>
/// 创建标题批注
/// </summary>
/// <param name="sheet">表单</param>
/// <param name="titleRow">行</param>
/// <param name="header"></param>
/// <param name="commnet"></param>
private void createTitleComment(ISheet sheet, IRow titleRow)
{
ICell newCellHeader = titleRow.CreateCell(0);
newCellHeader.SetCellValue(header);
newCellHeader.CellStyle = TitleStyle;
if (string.IsNullOrEmpty(this.header) || string.IsNullOrEmpty(this.commnet))
{
return;
}
HSSFPatriarch patr = sheet.CreateDrawingPatriarch() as HSSFPatriarch;;
HSSFComment comment = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));
comment.String = new HSSFRichTextString(this.commnet);
newCellHeader.CellComment = comment;
}
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();
}
}
}
}
/// <summary>
/// 获取Excel内存数据
/// </summary>
/// <param name="dataSource">数据源</param>
/// <param name="sheetName">Excel名字</param>
/// <returns></returns>
private static MemoryStream GetExcelData(DataTable dataSource, string sheetName)
{
if (dataSource == null)
{
throw new ArgumentException("dataSource is null");
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "UnKown";
}
//打开Excel对象
HSSFWorkbook _book = new HSSFWorkbook();
DocumentSummaryInformation _dom_summary_info = PropertySetFactory.CreateDocumentSummaryInformation();
_dom_summary_info.Company = "";
_book.DocumentSummaryInformation = _dom_summary_info;
SummaryInformation _summary_info = PropertySetFactory.CreateSummaryInformation();
_summary_info.Subject = "";
_book.SummaryInformation = _summary_info;
//Excel的Sheet对象
ISheet _sheet = _book.CreateSheet(sheetName);
DataColumnCollection _columns = dataSource.Columns;
DataRowCollection _rows = dataSource.Rows;
IRow _row = _sheet.CreateRow(0);
// 用于格式化单元格的数据
HSSFDataFormat format = (HSSFDataFormat)_book.CreateDataFormat();
// 设置列头字体
HSSFFont font = (HSSFFont)_book.CreateFont();
font.FontHeightInPoints = 18; //字体高度
//font.Color = (short)FontColor.RED;
font.FontName = "隶书"; //字体
font.Boldweight = (short)FontBoldWeight.Bold;
//font.IsItalic = true; //是否使用斜体
//font.IsStrikeout = true; //是否使用划线
//设置文本字体
HSSFFont fontContent = (HSSFFont)_book.CreateFont();
fontContent.FontHeightInPoints = 10;
fontContent.FontName = "宋体";
// 设置单元格类型
HSSFCellStyle cellStyle = (HSSFCellStyle)_book.CreateCellStyle();
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center; //水平布局：居中
cellStyle.WrapText = false;
// 添加单元格注释
// 创建HSSFPatriarch对象,HSSFPatriarch是所有注释的容器.
HSSFPatriarch patr = (HSSFPatriarch)_sheet.CreateDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5));
// 设置注释内容
comment.String = new HSSFRichTextString("列标题");
// 设置注释作者. 当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.Author = "天蓝海";
//set date format
ICellStyle cellStyleDate = _book.CreateCellStyle();
cellStyleDate.SetFont(fontContent);
cellStyleDate.Alignment = HorizontalAlignment.Center; //水平布局：居中
cellStyleDate.DataFormat = format.GetFormat("yyyy-m-d HH:MM:SS");
//数据格式
ICellStyle cellStyleNumber = _book.CreateCellStyle();
cellStyleNumber.SetFont(fontContent);
cellStyleNumber.Alignment = HorizontalAlignment.Center; //水平布局：居中
cellStyleNumber.DataFormat = format.GetFormat("0.00");
//生成列名，根据DataTable的列名
for (int i = 0; i < _columns.Count; i++)
{
// 创建单元格
ICell cell = _row.CreateCell(i);
HSSFRichTextString hssfString = new HSSFRichTextString(_columns[i].ColumnName);
cell.SetCellValue(hssfString); //设置单元格内容
cell.CellStyle = cellStyle; //设置单元格样式
cell.SetCellType(CellType.String); //指定单元格格式：数值、公式或字符串
cell.CellComment = comment; //添加注释
}
//填充数据
for (int j = 0; j < _rows.Count; j++)
{
_row = _sheet.CreateRow(j + 1);
for (int k = 0; k < _columns.Count; k++)
{
ICell cell = _row.CreateCell(k);
switch (_columns[k].DataType.ToString())
{
case "System.String":
HSSFRichTextString hssfString = new HSSFRichTextString(_rows[j][k].ToString());
cell.SetCellValue(hssfString);
cell.SetCellType(CellType.String);
break;
case "System.Decimal":
cell.SetCellValue((double)(decimal)_rows[j][k]);
cell.CellStyle = cellStyleNumber;
break;
case "System.DateTime":
cell.SetCellValue((DateTime)_rows[j][k]);
cell.CellStyle = cellStyleDate;
break;
}
//自动换行
if (_rows[j][k].ToString().Contains("\r\n"))
{
cell.CellStyle.WrapText = true;
}
}
}
//设置自动列宽
for (int i = 0; i < _columns.Count; i++)
{
_sheet.AutoSizeColumn(i);
}
//保存excel文档
_sheet.ForceFormulaRecalculation = true;
MemoryStream _stream = new MemoryStream();
_book.Write(_stream);
return(_stream);
}