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 ();
*/
}
