public void RefreshReport(SheetView Report)
{
int Column = Report.GetLastNonEmptyColumn(NonEmptyItemFlag.Data) + 1;
int Row = Report.GetLastNonEmptyRow(NonEmptyItemFlag.Data) + 1;
for (int i = 0; i < Row; i++)
{
for (int j = 0; j < Column; j++)
{
if (Report.Cells[i, j].Value is GridElement)
{
GridElement Element = Report.Cells[i, j].Value as GridElement;
Element.Row = Report.Cells[i, j].Row.Index;
Element.Column = Report.Cells[i, j].Column.Index;
Element.RowSpan = Report.Cells[i, j].RowSpan;
Element.ColumnSpan = Report.Cells[i, j].ColumnSpan;
if (Element.Value is Formula)
{
Formula formula = Element.Value as Formula;
formula.Expression = Report.Cells[i, j].Formula;
}
Report.Cells[i, j].Invalidate();
}
}
}
}
public static void calculateReportDefaultParent(SheetView paramReport, Object[,] Elements)
{
//GetLastNonEmptyRow方法获得是某一行或某一列的索引，所以行数(列数) = 行(列)索引号 + 1
int ColumnCount = paramReport.GetLastNonEmptyColumn(NonEmptyItemFlag.Data) + 1;
int RowCount = paramReport.GetLastNonEmptyRow(NonEmptyItemFlag.Data) + 1;
for (int i = 0; i < RowCount; i++)
{
for (int j = 0; j < ColumnCount; j++)
{
if (paramReport.Cells[i, j].Value is GridElement)
{
GridElement Element = paramReport.Cells[i, j].Value as GridElement;
ExpandOrientation Orientation = Element.ExpandOrientation;
if (Orientation.isDefaultLeftParent)
{
Orientation.LeftParent = calculateReportDefaultLeftParent(paramReport, Elements, Element.getLiteralCell());
}
if (Orientation.isDefaultTopParent)
{
Orientation.TopParent = calculateReportDefaultTopParent(paramReport, Elements, Element.getLiteralCell());
}
}
}
}
}
private void RemoveRowUnused(SheetView sheet)
{
int lastRowNonEmpty = sheet.GetLastNonEmptyRow(NonEmptyItemFlag.Data);
if (lastRowNonEmpty != sheet.RowCount - 1)
{
sheet.RemoveRows(sheet.RowCount - 1, 1);
}
}
/// <summary>
/// Action สำหรับเพิ่ม Row ใหม่
/// </summary>
/// <param name="sender"></param>
void actionAddNewRow_Action(object sender)
{
if (sender is SpreadView)
{
SpreadView view = (SpreadView)sender;
int sheetIndex = view.ActiveSheetIndex;
if (sheetIndex < 0)
{
return;
}
if (RowAdding != null)
{
EventRowAdding eArg = new EventRowAdding();
RowAdding(this, eArg);
if (eArg.Cancel)
{
return;
}
}
SheetView sheet = view.Sheets[sheetIndex];
if (sheet.RowCount == 0)
{ // ถ้าไม่ซักแถว
sheet.AddRows(0, 1);
sheet.SetActiveCell(sheet.RowCount - 1, 1, true);
sheet.SetActiveCell(sheet.RowCount - 1, 0, true);
}
else
{
// เช็คดูก่อนว่าแถวสุดท้าย ว่างหรือไม่ ??
int lastRowNonEmpty = sheet.GetLastNonEmptyRow(NonEmptyItemFlag.Data);
if (lastRowNonEmpty == sheet.RowCount - 1)
{ // ถ้าแถวสุดท้ายไม่ว่าง ก็เพิ่มแถวได้เลย
// Add new row.
sheet.AddRows(sheet.RowCount, 1);
}
sheet.SetActiveCell(sheet.RowCount - 1, 0, true);
view.ShowActiveCell(VerticalPosition.Center, HorizontalPosition.Center);
}
if (RowAdded != null)
{
RowAdded(this, sheet.RowCount - 1);
}
}
}
public Object[,] InitElements(SheetView Report)
{
int Column = Report.GetLastNonEmptyColumn(NonEmptyItemFlag.Data) + 1;
int Row = Report.GetLastNonEmptyRow(NonEmptyItemFlag.Data) + 1;
Elements = new Object[Row, Column];
for (int i = 0; i < Row; i++)
{
for (int j = 0; j < Column; j++)
{
if (Report.Cells[i, j].Value is GridElement)
{
GridElement Element = Report.Cells[i, j].Value as GridElement;
Element.Report = Report;
Elements[i, j] = Element;
}
}
}
return(Elements);
}
public static DynamicValueList getRowHeightList(SheetView paramReport)
{
int cCount = paramReport.GetLastNonEmptyColumn(NonEmptyItemFlag.Data) + 1;
int rCount = paramReport.GetLastNonEmptyRow(NonEmptyItemFlag.Data) + 1;
for (int m = 0; m < rCount; m++)
{
for (int n = 0; n < cCount; n++)
{
if (paramReport.Cells[m, n].Tag is GridElement)
{
cCount = Math.Max(cCount, paramReport.Cells[m, n].Column.Index + paramReport.Cells[m, n].ColumnSpan);
rCount = Math.Max(rCount, paramReport.Cells[m, n].Row.Index + paramReport.Cells[m, n].RowSpan);
}
}
}
foreach (IElement Element in paramReport.DrawingContainer.ContainedObjects)
{
if (Element is FloatElement)
{
FloatElement localFloatElement = Element as FloatElement;
Point[] arrayOfPoint = ReportHelper.calculateLastColumnAndRowOfFloatElement(paramReport, localFloatElement);
cCount = Math.Max(cCount, arrayOfPoint[0].X);
rCount = Math.Max(rCount, arrayOfPoint[0].Y);
}
}
DynamicValueList localDynamicValueList = new DynamicValueList(19, rCount);
for (int j = 0; j < rCount; j++)
{
localDynamicValueList.set(j, Convert.ToInt32(paramReport.Rows[j].Height));
}
return(localDynamicValueList);
}
/// <summary>
/// 导入EXCEL
/// </summary>
/// <param name="filepach"></param>
/// <param name="filedList"></param>
/// <param name="capList"></param>
/// <returns></returns>
public static DataTable GetExcelmainandchildren(string filepach, IList <string> filedList, IList <string> capList, string sheetname, DataTable dt1)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
IList <string> fie = new List <string>();
IList <int> yxzl = new List <int>();//有效的那几列
SheetView sheetview = fpSpread1.Sheets.Find(sheetname);
if (sheetview == null)
{
return(dt);
}
int m = 1;
for (int j = 1; j < sheetview.GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.Contains(sheetview.Cells[0, j].Text))
{
fie.Add(filedList[capList.IndexOf(sheetview.Cells[0, j].Text)]);
yxzl.Add(j);
}
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
dt.Columns.Add("SvgUID");
for (int i = m; i <= sheetview.GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
string datsourctitle = sheetview.Cells[i, 0].Text;
DataRow dr = dt.NewRow();
// for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
DataRow[] drsource = dt1.Select("Title='" + datsourctitle + "'");
if (drsource.Length != 1)
{
continue;
}
dr["SvgUID"] = drsource[0]["UID"];
int n = 0;
foreach (int j in yxzl)
{
dr[fie[n]] = sheetview.Cells[i, j].Text;
n++;
}
dt.Rows.Add(dr);
}
return(dt);
}