public static object FormatValue(ReportSheetTemplate tpl, object value, string format) { if (string.IsNullOrEmpty(format) || value == null) { return(value); } switch (format) { case FORMAT_DATE: return(ValueToDate(value)); case FORMAT_MONTH: return(ValueToMonth(value)); case FORMAT_TIME: return(ValueToTime(value)); case FORMAT_DATE_TIME: return(ValueToDateTime(value)); case FORMAT_TIME_SPAN: return(ValueToTimeSpan(value, tpl)); case FORMAT_FEN: return(ValueToFen(value)); default: return(value); } }
public static void UpdateCellValue(ReportSheetTemplate tpl, CellRange cell, object value, string format) { UpdateCellValueCallTimes++; // return ; value = FormatValue(tpl, value, format); try { switch (format) { case FORMAT_DATE: case FORMAT_MONTH: case FORMAT_DATE_TIME: case FORMAT_TIME: case FORMAT_TIME_SPAN: // cell.DateTimeValue = (DateTime) value ; // return ; break; case FORMAT_FEN: case FORMAT_NUMBER: try { cell.NumberValue = Convert.ToDouble(value); } catch (Exception e) { // ignore exception. cell.NumberValue = 0; } return; default: break; } } catch (Exception e) { // ignore exception. } cell.Text = Convert.ToString(value); }
/* * private void ClearExcelReport(bool autoFit, string fileName, List<ReportSheetTemplate> tplList) * { * bool reusedFlag = false; * if (xlapp.Workbooks.Count > 0) * { * reusedFlag = true; * } * xlapp.Workbooks.Open(fileName, * Missing.Value, Missing.Value, Missing.Value, Missing.Value, * Missing.Value, Missing.Value, Missing.Value, Missing.Value, * Missing.Value, Missing.Value, Missing.Value, Missing.Value, * Missing.Value, Missing.Value); * * xlapp.DisplayAlerts = false; * * for (int i = 0; i < xlapp.ActiveWorkbook.Worksheets.Count && * i < tplList.Count; i++) * { * Worksheet worksheet = (Worksheet)xlapp.ActiveWorkbook.Worksheets[i + 1]; * * ReportSheetTemplate tpl = tplList[i]; * JoinTable(worksheet, tpl); * // Clear Data * Clear(worksheet, tpl.startRowIndex); * * if (autoFit || tpl.autoFit) * { * Range range = worksheet.get_Range("A1", "DZ1").EntireColumn; * range.AutoFit(); * } * // .GetType ().GetMethod ("AutoFit").Invoke (range, new object[0]) ; * * } * * // remove warnning sheet. * * IEnumerator e = xlapp.ActiveWorkbook.Worksheets.GetEnumerator(); * while (e.MoveNext()) * { * Worksheet sheet = (Worksheet)e.Current; * * if (sheet.Name.IndexOf("Warning") >= 0) * sheet.Delete(); * } * ((_Worksheet)xlapp.ActiveWorkbook.Worksheets[1]).Activate(); * ((Worksheet)xlapp.ActiveWorkbook.Worksheets[1]).get_Range("A1", "A1").Activate(); * // only save activeWorkBook * xlapp.ActiveWorkbook.Save(); * // only close activeWorkbook ; * xlapp.ActiveWorkbook.Close(true, Missing.Value, Missing.Value); * // xlapp.Workbooks.Close (); * } */ public static void JoinTable(Spire.Xls.Worksheet sheet, ReportSheetTemplate tpl) { if (tpl.blockList.Count < 2) { return; } TplBlock firstBlock = tpl.blockList [1]; int blockRow = firstBlock.startRowIndex; int blocklastColum = firstBlock.startColIndex + firstBlock.colCount /* - * (firstBlock.dColumn == null ? 0 : firstBlock.dColumn.gCols)*/; int joinedRows = 0; for (int i = 2; i < tpl.blockList.Count; i++) { TplBlock block = tpl.blockList [i]; if (block.joinat >= 0 && block.rowCount > 0) { // CopyRangeToFirstTable CellRange range = RangeHelper.GetRange(sheet, block.startColIndex + block.joinat + 1, block.startRowIndex - joinedRows, block.colCount, block.rowCount); range.Copy( RangeHelper.GetRange(sheet, blocklastColum + 1, firstBlock.startRowIndex, block.colCount, block.rowCount)); /* range.EntireRow.Delete (Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp) ; */ // delete rows. for (int k = 0; k < block.rowCount; k++) { sheet.DeleteRow(block.startRowIndex - joinedRows); } joinedRows += block.rowCount; if (block.dColumn != null && block.dColumn.startCellIndex == block.joinat) { // Merge Joined Table Columns. for (int j = 0; j < block.lineList.Count; j++) { TplLine line = block.lineList [j]; if (!line.containsHGroup) { continue; } Boolean hasMerged = false; for (int k = 0; k < line.insertedRowList.Count; k++) { int rowIndex = line.insertedRowList [k]; rowIndex = rowIndex - block.startRowIndex + blockRow; CellRange leftRange = RangeHelper.GetRange(sheet, blocklastColum, rowIndex, 1, 1); if (leftRange.MergeArea != null) { leftRange = RangeHelper.GetRange(sheet, leftRange.MergeArea.Column, leftRange.MergeArea.Row, 1, 1); } CellRange rightRange = RangeHelper.GetRange(sheet, blocklastColum + 1, rowIndex, 1, 1); if (rightRange.MergeArea != null) { rightRange = RangeHelper.GetRange(sheet, rightRange.MergeArea.Column, rightRange.MergeArea.Row, 1, 1); } if (leftRange.Text.Equals(rightRange.Text)) { // Merge RangeHelper.GetRange(sheet, leftRange.Column, leftRange.Row, rightRange.Column + rightRange.Columns.Length - leftRange.Column, Math.Min(rightRange.Rows.Length, leftRange.Rows.Length) ).Merge(); hasMerged = true; } } if (!hasMerged) { break; } } // end for } // end if blocklastColum += block.colCount - block.joinat; } } }
private void ClearReport(bool autoFit, Workbook book, List <ReportSheetTemplate> tplList) { for (int i = 0; i < book.Worksheets.Count && i < tplList.Count; i++) { Spire.Xls.Worksheet worksheet = book.Worksheets [i]; ReportSheetTemplate tpl = tplList [i]; JoinTable(worksheet, tpl); // Clear Data Clear(worksheet, tpl.startRowIndex); if (autoFit || tpl.autoFit) { CellRange range = RangeHelper.GetRange(worksheet, 1, 15, 50, 100); range.AutoFitColumns(); /*for (int j = 1; j < 100; j++) * { * try * { * worksheet.AutoFitColumn (j); * } * catch (Exception e) * { * Console.Write (e) ; * } * } */ } // .GetType ().GetMethod ("AutoFit").Invoke (range, new object[0]) ; // copy image /*for (int j = 0; tpl.pics != null && * j < tpl.pics.Count && * j < worksheet.Pictures.Count; j++) * { * Rectangle pic = tpl.pics [i] ; * * * int tmp = worksheet.Pictures [i].TopRow ; * tmp = worksheet.Pictures[i].TopRowOffset; * tmp = worksheet.Pictures[i].LeftColumn; * tmp = worksheet.Pictures[i].LeftColumnOffset; * tmp = worksheet.Pictures [i].BottomRow ; * tmp = worksheet.Pictures [i].BottomRowOffset ; * tmp = worksheet.Pictures [i].RightColumn ; * tmp = worksheet.Pictures [i].RightColumnOffset ; * * worksheet.Pictures [i].Left = pic.X ; * worksheet.Pictures [i].Top = pic.Y ; * worksheet.Pictures [i].Height = pic.Height ; * worksheet.Pictures [i].Width = pic.Width ; * * }*/ } // remove warnning sheet. /* * IEnumerator e = xlapp.ActiveWorkbook.Worksheets.GetEnumerator(); * while (e.MoveNext()) * { * Worksheet sheet = (Worksheet)e.Current; * * if (sheet.Name.IndexOf("Warning") >= 0) * sheet.Delete(); * } * ((_Worksheet)xlapp.ActiveWorkbook.Worksheets[1]).Activate (); * ((Worksheet)xlapp.ActiveWorkbook.Worksheets [1]).get_Range("A1", "A1").Activate(); * // only save activeWorkBook * xlapp.ActiveWorkbook.Save (); * // only close activeWorkbook ; * xlapp.ActiveWorkbook.Close (true, Missing.Value, Missing.Value); * // xlapp.Workbooks.Close (); */ }