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