public void WriteFormatedValue(PointInfo startPoint, PointInfo endPoint, ReportItem reportItem)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
switch (reportItem.ContentAlignment)
{
case HorizontalAlignment.Center:
objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case HorizontalAlignment.Left:
objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
break;
case HorizontalAlignment.Right:
objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
break;
default:
objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
break;
}
if (reportItem.GetType().Name == "ReportConstantItem")
{
switch (((ReportConstantItem)reportItem).Style)
{
case ReportConstantItem.StyleType.PageIndex:
case ReportConstantItem.StyleType.PageIndexAndTotalPageCount:
if (reportItem.Format == String.Empty)
{
objRange.Value2 = String.Format(reportItem.Format, "'" + reportItem.Value);
}
else
{
objRange.Value2 = String.Format(reportItem.Format, reportItem.Value);
}
break;
default:
objRange.Value2 = String.Format(reportItem.Format, reportItem.Value);
break;
}
}
else
{
objRange.Value2 = String.Format(reportItem.Format, reportItem.Value);
}
}
/// <summary>
/// Set row grid line
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
public void SetRowGridLine(PointInfo startPoint, PointInfo endPoint, bool innerLine)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
if (endPoint.RowIndex != startPoint.RowIndex)
{
CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = innerLine ? Excel.XlLineStyle.xlContinuous : XlLineStyle.xlLineStyleNone;
}
//CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
//CurSheet.get_Range(startCell, endCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
}
/// <summary>
/// Set excel row height
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="font">letter font</param>
/// <param name="height">unit by letter count</param>
public void SetRowHeight(PointInfo startPoint, PointInfo endPoint, System.Drawing.Font font, int height)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
if (height != 0)
{
objRange.RowHeight = UnitConversion.GetLetterHeight(height, font);
}
}
/// <summary>
/// Set HorizontalAlignment of excel sheet cell
/// </summary>
/// <param name="point"></param>
/// <param name="hAlignment"></param>
public void SetHorizontalAlignment(PointInfo point, HorizontalAlignment hAlignment)
{
SetHorizontalAlignment(point, null, hAlignment);
}
public void AutoFit(PointInfo startPoint, PointInfo endPoint)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
//CurSheet.get_Range(startCell, endCell).Merge(mValue);
//endCell = mValue;
CurSheet.get_Range(startCell, endCell).AutoFit();
}
/// <summary>
/// Set excel column width
/// </summary>
/// <param name="point"></param>
/// <param name="font">letter font</param>
/// <param name="width">unit by letter count</param>
public void SetColumnWidth(PointInfo point, System.Drawing.Font font, int width)
{
SetColumnWidth(point, null, font, width);
}
/// <summary>
/// Set font of excel sheet cell
/// </summary>
/// <param name="point"></param>
/// <param name="font"></param>
public void SetFont(PointInfo point, System.Drawing.Font font)
{
SetFont(point, null, font);
}
public void DeleteRow(PointInfo startPoint, PointInfo endPoint)
{
object[] cell = this.ConvertPointToCell(startPoint, endPoint);
object startCell = cell[0];
object endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
objRange.Select();
objRange.Rows.Delete(Excel.XlDirection.xlUp);//������
}
/// <summary>
/// Get excel area width
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <returns></returns>
public double GetAreaWidth(PointInfo startPoint, PointInfo endPoint)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
return Convert.ToDouble(CurSheet.get_Range(startCell, endCell).Width);
}
public double CopyRange(int sheetIndex, PointInfo srcStartPoint, PointInfo srcEndPoint, PointInfo desStartPoint)
{
Worksheet sheet = this.CurBook.Sheets[sheetIndex] as Worksheet;
object[] cell = this.ConvertPointToCell(srcStartPoint, srcEndPoint);
object srcStartCell = cell[0];
object srcEndCell = cell[1];
Range objRange = sheet.get_Range(srcStartCell, srcEndCell);
int column = objRange.Columns.Count;
int row = objRange.Rows.Count;
PointInfo desEndPoint = new PointInfo(desStartPoint.RowIndex + row - 1, desStartPoint.ColumnIndex + column - 1);
cell = this.ConvertPointToCell(desStartPoint, desEndPoint);
object desStartCell = cell[0];
object desEndCell = cell[1];
Range desRange = this.CurSheet.get_Range(desStartCell, desEndCell);
objRange.Copy(desRange);
desStartPoint.RowIndex += row;
desStartPoint.ColumnIndex = 0;
return (double)objRange.Height;
}
public void CopyRange(PointInfo srcStartPoint, PointInfo srcEndPoint, PointInfo desStartPoint)
{
#region Variable Definition
object[] cell = null;
object srcStartCell = null;
object srcEndCell = null;
object desStartCell = null;
object desEndCell = null;
Excel.Range desRange = null;
#endregion
cell = this.ConvertPointToCell(srcStartPoint, srcEndPoint);
srcStartCell = cell[0];
srcEndCell = cell[1];
objRange = this.CurSheet.get_Range(srcStartCell, srcEndCell);
cell = this.ConvertPointToCell(desStartPoint, desStartPoint);
desStartCell = cell[0];
desEndCell = cell[1];
desRange = this.CurSheet.get_Range(desStartCell, desStartCell);
objRange.Copy(desRange);
desRange = null;
}
/// <summary>
/// Convert point to excel cell
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <returns></returns>
private object[] ConvertPointToCell(PointInfo startPoint, PointInfo endPoint)
{
object startCell = null;
object endCell = null;
startCell = (((int)(startPoint.ColumnIndex / 26) > 0) ? Convert.ToChar((startPoint.ColumnIndex / 26) + 64).ToString() : "") + Convert.ToChar((startPoint.ColumnIndex % 26) + 65).ToString() + Convert.ToString(startPoint.RowIndex + 1);
if (endPoint != null)
{
endCell = (((int)(endPoint.ColumnIndex / 26) > 0) ? Convert.ToChar((endPoint.ColumnIndex / 26) + 64).ToString() : "") + Convert.ToChar((endPoint.ColumnIndex % 26) + 65).ToString() + Convert.ToString(endPoint.RowIndex + 1);
}
else
{
endCell = mValue;
}
return new object[2] { startCell, endCell };
}
/// <summary>
/// Write value to worksheet
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="objValue">ReportItem or FieldItem or other value</param>
public void WriteValue(PointInfo startPoint, PointInfo endPoint, object objValue)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
objRange.Value2 = objValue;
}
/// <summary>
/// Write value to worksheet
/// </summary>
/// <param name="point"></param>
/// <param name="objValue">ReportItem or FieldItem or other value</param>
public void WriteValue(PointInfo point, object objValue)
{
WriteValue(point, null, objValue);
}
/// <summary>
/// Set column grid line
/// </summary>
/// <param name="point"></param>
public void SetColumnGridLine(PointInfo point)
{
SetColumnGridLine(point, null);
}
/// <summary>
/// Get excel cell height
/// </summary>
/// <param name="point"></param>
/// <returns></returns>
public double GetCellHeight(PointInfo point)
{
return GetAreaHeight(point, null);
}
public void SetColumnGridLine(PointInfo startPoint, PointInfo endPoint)
{
SetColumnGridLine(startPoint, endPoint);
}
/// <summary>
/// Get excel cell width
/// </summary>
/// <param name="point"></param>
/// <returns></returns>
public double GetCellWidth(PointInfo point)
{
return GetAreaWidth(point, null);
}
/// <summary>
/// Set excel column width
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="font">letter font</param>
/// <param name="width">unit by letter count</param>
public void SetColumnWidth(PointInfo startPoint, PointInfo endPoint, System.Drawing.Font font, int width)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
//CurSheet.get_Range(startCell, endCell).Merge(mValue);
//endCell = mValue;
if (width != 0)
{
((Range)CurSheet.Columns[startPoint.ColumnIndex + 1, mValue]).ColumnWidth = UnitConversion.GetLetterWidth(width, font);
//CurSheet.get_Range(startCell, endCell).ColumnWidth = UnitConversion.GetLetterWidth(width, font);
}
}
public void Group(PointInfo startPoint, PointInfo endPoint, FieldItem.SumType sumType, int groupIndex, int[] subTotalList)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
CurSheet.get_Range(startCell, endCell).Subtotal(groupIndex, GetExcelSumType(sumType), subTotalList, true, false, XlSummaryRow.xlSummaryBelow);
}
/// <summary>
/// Set font of excel sheet area
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="font"></param>
public void SetFont(PointInfo startPoint, PointInfo endPoint, System.Drawing.Font font)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
if (font != null)
{
objRange = CurSheet.get_Range(startCell, endCell);
if (font.Bold)
{
objRange.Font.Bold = font.Bold;
}
if (font.Italic)
{
objRange.Font.Italic = font.Italic;
}
objRange.Font.Name = font.Name;
objRange.Font.Size = font.Size;
if (font.Strikeout)
{
objRange.Font.Strikethrough = font.Strikeout;
}
if (font.Underline)
{
objRange.Font.Underline = font.Underline;
}
}
}
/// <summary>
/// Insert picture to excel sheet
/// </summary>
/// <param name="point"></param>
/// <param name="imagePath"></param>
public void InsertPicutre(PointInfo point, Image image)
{
InsertPicutre(point, null, image);
}
/// <summary>
/// Set HorizontalAlignment of excel sheet area
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="hAlignment"></param>
public void SetHorizontalAlignment(PointInfo startPoint, PointInfo endPoint, HorizontalAlignment hAlignment)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
switch (hAlignment)
{
case HorizontalAlignment.Center:
CurSheet.get_Range(startCell, endCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case HorizontalAlignment.Left:
CurSheet.get_Range(startCell, endCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
break;
case HorizontalAlignment.Right:
CurSheet.get_Range(startCell, endCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
break;
default:
CurSheet.get_Range(startCell, endCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
break;
}
}
/// <summary>
/// Insert picture to excel sheet
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="imagePath"></param>
public void InsertPicutre(PointInfo startPoint, PointInfo endPoint, Image image)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
string imagePath = "";
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
imagePath = @"C:\" + Guid.NewGuid().ToString() + ".jpg";
((Bitmap)image).Save(imagePath);
((Excel.Pictures)CurSheet.Pictures(mValue)).Insert(imagePath, mValue);
pictureIndex++;
((Excel.Picture)CurSheet.Pictures(pictureIndex)).Left = (double)objRange.Left;
((Excel.Picture)CurSheet.Pictures(pictureIndex)).Top = (double)objRange.Top;
double height = ((Excel.Picture)CurSheet.Pictures(pictureIndex)).Height;
if ((double)objRange.RowHeight < height)
{
objRange.RowHeight = height;
}
File.Delete(imagePath);
}
/// <summary>
/// Set row grid line
/// </summary>
/// <param name="point"></param>
public void SetRowGridLine(PointInfo point, bool innerLine)
{
SetRowGridLine(point, null, innerLine);
}
public void Merge(PointInfo startPoint, PointInfo endPoint)
{
object[] cell = this.ConvertPointToCell(startPoint, endPoint);
object startCell = cell[0];
object endCell = cell[1];
if (startCell.ToString() != endCell.ToString())
{
Range range = this.CurSheet.get_Range(startCell, endCell);
range.Merge(mValue);
range = null;
}
}
/// <summary>
/// Set excel row height
/// </summary>
/// <param name="point"></param>
/// <param name="font">letter font</param>
/// <param name="height">unit by letter count</param>
public void SetRowHeight(PointInfo point, System.Drawing.Font font, int height)
{
SetRowHeight(point, null, font, height);
}
public void PageWidthBreak(PointInfo point)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(point, null);
startCell = cell[0];
endCell = cell[1];
CurSheet.VPageBreaks.Add(CurSheet.get_Range(startCell, mValue));
}
public void SetRowHeight(PointInfo startPoint, PointInfo endPoint, double height)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
if (height != 0)
{
objRange.RowHeight = height;
}
}
public double CopyRange(int sheetIndex, PointInfo desStartPoint, int PageIndex)
{
Worksheet sheet = this.CurBook.Sheets[sheetIndex] as Worksheet;
int column = sheet.UsedRange.Columns.Count;
int row = sheet.UsedRange.Rows.Count;
PointInfo desEndPoint = new PointInfo(desStartPoint.RowIndex + row - 1, desStartPoint.ColumnIndex + column - 1);
object[] cell = this.ConvertPointToCell(desStartPoint, desEndPoint);
object desStartCell = cell[0];
object desEndCell = cell[1];
Range desRange = this.CurSheet.get_Range(desStartCell, desEndCell);
int picCount = (this.CurSheet.Pictures(mValue) as Pictures).Count;
sheet.UsedRange.Copy(desRange);
int deleteCount = (this.CurSheet.Pictures(mValue) as Pictures).Count - picCount;
if (deleteCount > 0)
{
for (int i = 0; i < deleteCount; i++)
{
(this.CurSheet.Pictures(picCount + 1) as Picture).Delete();
}
}
desStartPoint.RowIndex += sheet.UsedRange.Rows.Count;
desStartPoint.ColumnIndex = 0;
picCount = (sheet.Pictures(mValue) as Pictures).Count;
if (picCount > 0)
{
for (int i = 1; i <= row; i++)
{
(desRange.Rows[i, mValue] as Excel.Range).RowHeight = (sheet.UsedRange[i, 1] as Excel.Range).RowHeight;
}
for (int i = 1; i <= picCount; i++)
{
Picture pic = sheet.Pictures(i) as Picture;
pic.Copy();
this.CurSheet.Paste(mValue, mValue);
Picture picCopy = this.CurSheet.Pictures((PageIndex - 1) * picCount + i) as Picture;
picCopy.Top = pic.Top + (double)desRange.Top;
picCopy.Left = pic.Left + (double)desRange.Left;
}
}
desRange.Replace(ExcelReportExporter.PAGE_INDEX, PageIndex, mValue, mValue, mValue, mValue, mValue, mValue);
return (double)sheet.UsedRange.Height;
}