internal void FixDwgIds(TDrawing Drawing, TSheet sSheet, bool UseObjId, TCopiedGen CopiedGen)
{
for (int i = 0; i < Count; i++)
{
this[i].FixDwgIds(Drawing, i, sSheet, UseObjId, CopiedGen);
}
}
internal void AddNewComment(int Row, int Col, TRichString Txt, string Author, TDrawing Drawing, TImageProperties Properties,
ExcelFile xls, TSheet sSheet, bool ReadFromXlsx)
{
TNoteRecord R = new TNoteRecord(Row, Col, Txt, Author, Drawing, Properties, xls, sSheet, ReadFromXlsx); //.CreateFromData
AddRecord(R, Row);
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
//Excel 2010 will complain if this record exists.
TLabelSSTRecord lsst = new TLabelSSTRecord(Col, XF, RecordLoader.SST, RecordLoader.FontList, (TRichString)GetValue(ws.Cells.CellList));
ws.Cells.AddCell(lsst, rRow, RecordLoader.VirtualReader);
}
internal void FixDwgOfs(TSheet dSheet)
{
for (int i = 0; i < Count; i++)
{
this[i].FixDwgOfs(i, dSheet);
}
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
TMergedCells a = new TMergedCells();
ws.MergedCells.Add(a);
a.LoadFromStream(RecordLoader, this);
}
/// <summary>
/// CreateFromData
/// </summary>
internal TNoteRecord(int aRow, int aCol, TRichString aTxt, string aAuthor, TDrawing Drawing, TImageProperties Properties,
ExcelFile xls, TSheet sSheet, bool ReadFromXlsx)
: base((int)xlr.NOTE, aCol)
{
if ((aCol < 0) || (aCol > FlxConsts.Max_Columns))
{
XlsMessages.ThrowException(XlsErr.ErrXlsIndexOutBounds, aCol, "Column", 0, FlxConsts.Max_Columns);
}
Dwg = Drawing.AddNewComment(xls, Properties, sSheet, ReadFromXlsx);
TEscherImageAnchorRecord Anchor = GetImageRecord();
if (Anchor != null)
{
NoteTextBox = Anchor.SaveCommentCoords(sSheet, aRow, aCol);
}
Col = aCol;
OptionFlags = 0; //option flags
unchecked
{
ObjId = (UInt16)Dwg.ObjId; //object id
}
Author = aAuthor;
SetText(aTxt);
}
internal void FixDwgOfs(int Row, TSheet dSheet)
{
TEscherImageAnchorRecord Anchor = GetImageRecord();
if (Anchor != null)
{
AdaptSize(Anchor, Row, dSheet);
}
}
internal void InsertSheets(int CopyFrom, int InsertBefore, int SheetCount, TWorkbook SourceWorkbook)
{
if (SourceWorkbook == null)
{
SourceWorkbook = this;
}
if (CopyFrom >= SourceWorkbook.Sheets.Count)
{
XlsMessages.ThrowException(XlsErr.ErrInvalidSheetNo, CopyFrom, -1, SourceWorkbook.Sheets.Count - 1);
}
if (InsertBefore > Sheets.Count)
{
XlsMessages.ThrowException(XlsErr.ErrInvalidSheetNo, InsertBefore, 0, Sheets.Count);
}
TSheet aSheet = null;
int OptionFlags = 0;
if (CopyFrom >= 0)
{
aSheet = SourceWorkbook.Sheets[CopyFrom];
OptionFlags = SourceWorkbook.Globals.SheetOptionFlags(CopyFrom);
}
int NewCopyFrom = CopyFrom;
if (SourceWorkbook == this && CopyFrom >= InsertBefore)
{
NewCopyFrom += SheetCount;
}
Globals.InsertSheets(CopyFrom, InsertBefore, OptionFlags, XlsMessages.GetString(XlsErr.BaseSheetName), SheetCount, SourceWorkbook.Sheets);
TSheetInfo SheetInfo = new TSheetInfo(-1, -1, -1, SourceWorkbook.Globals, Globals, aSheet, null, false);
for (int i = 0; i < SheetCount; i++)
{
SheetInfo.InsSheet = InsertBefore + i;
SheetInfo.SourceFormulaSheet = NewCopyFrom;
SheetInfo.DestFormulaSheet = InsertBefore;
SheetInfo.DestSheet = null; //keep it null, since the reference does not exist yet.
if (aSheet == null)
{
Sheets.Insert(InsertBefore, TWorkSheet.CreateFromData(Globals, Globals.Workbook.XlsBiffVersion, Globals.Workbook.ExcelFileFormat));
}
else
{
SheetInfo.DestFormulaSheet = InsertBefore + i;
Globals.Names.InsertSheets(NewCopyFrom, InsertBefore + i, 1, SheetInfo, SourceWorkbook == this); //names must be inserted before the sheet is cloned, so formulas can refer to them.
Sheets.Insert(InsertBefore + i, TSheet.Clone(aSheet, SheetInfo));
SheetInfo.DestSheet = Sheets[InsertBefore + i];
Sheets[InsertBefore + i].ArrangeCopySheet(SheetInfo);
}
}
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
if (Loader.CustomView != null)
{
Loader.CustomView.Selection.AddBiff8Record(this);
}
else
{
ws.Window.Selection.AddBiff8Record(this);
}
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
if (Loader.CustomView != null)
{
Loader.CustomView.HPageBreaks.AddRecord(this);
}
else
{
ws.SheetGlobals.HPageBreaks.AddRecord(this);
}
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
TFlxChart chart = ws as TFlxChart;
if (chart != null)
{
chart.ClrtClient = this;
return;
}
base.LoadIntoSheet(ws, rRow, RecordLoader, ref Loader);
}
internal int IndexOf(TSheet SearchRecord)
{
for (int i = Count - 1; i >= 0; i--)
{
if (FList[i] == SearchRecord)
{
return(i);
}
}
return(-1);
}
internal TClientAnchor GetAnchor(int Row, TSheet dSheet)
{
TEscherImageAnchorRecord Anchor = GetImageRecord();
if (Anchor == null)
{
return(new TClientAnchor());
}
AdaptSize(Anchor, Row, dSheet);
return(Anchor.GetAnchor());
}
internal void DeleteSheets(int SheetIndex, int SheetCount)
{
for (int i = 0; i < SheetCount; i++)
{
if (SheetIndex >= Count)
{
return;
}
TSheet Sh = this[SheetIndex];
FList.RemoveAt(SheetIndex);
Sh.Destroy(); //This will call destroy on child objects, to clear SST references and BSE drawing refs.
}
}
public void WriteXlsxTest()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t2.xlsx";
IExcel excel = ExcelFactory.CreateDefault();
DataTable dt = excel.Read(file, 0, 2);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
TTemplate template = new TTemplate();
TRow R1 = new TRow();
TCell A = new TCell("A", "A", 0, 0); A.ColSpan = 2;
TCell B = new TCell("", "B", 0, 2);
TCell C = new TCell("C", "C", 0, 3); C.RowSpan = 2;
TCell D = new TCell("D", "D", 0, 4); D.RowSpan = 2;
TCell E = new TCell("", "E", 0, 5); E.ColSpan = 2;
R1.Cells.Add(A);
R1.Cells.Add(B);
R1.Cells.Add(C);
R1.Cells.Add(D);
R1.Cells.Add(E);
TRow R2 = new TRow();
TCell A1 = new TCell("A1", "A1", 1, 0);
TCell A2 = new TCell("A2", "A2", 1, 1);
TCell B1 = new TCell("B1", "B1", 1, 2);
TCell E1 = new TCell("E1", "E1", 1, 5);
TCell E2 = new TCell("E2", "E2", 1, 6);
R2.Cells.Add(A1);
R2.Cells.Add(A2);
R2.Cells.Add(B1);
R2.Cells.Add(E1);
R2.Cells.Add(E2);
TSheet tsheet = new TSheet();
tsheet.Name = "Sheet1";
tsheet.Title = "表格O";
tsheet.Head.Rows.Add(R1);
tsheet.Head.Rows.Add(R2);
template.Sheets.Add(tsheet);
string outputFilePath = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/out2.xlsx";
bool result = excel.WriteFile(outputFilePath, ds, template);
Assert.IsTrue(result);
}
/*Now we keep range to insert on dwgofs. Inserting here could lead to inserting rows on the cells (and moving the red triangle down), but not on the note. Both must move together.
* internal override void ArrangeInsertRange(int Row, TXlsCellRange CellRange, int aRowCount, int aColCount, TSheetInfo SheetInfo)
* {
* if ((SheetInfo.InsSheet<0) || (SheetInfo.SourceFormulaSheet != SheetInfo.InsSheet)) return; //this is also on the base.arrangeinsertrange, but we want to keep both inserts together. If we insert on the red triangle, we insert on the shape. If we don't we don't no matter that the shape is on range to insert.
*
* base.ArrangeInsertRange (Row, CellRange, aRowCount, aColCount, SheetInfo);
* if ((Dwg!=null) && (Dwg.FindRoot()!=null))
* {
* Dwg.FindRoot().ArrangeInsertRange(CellRange, aRowCount, aColCount, SheetInfo, true);
* }
* }
*/
internal void FixDwgIds(TDrawing Drawing, int Row, TSheet sSheet, bool UseObjId, TCopiedGen CopiedGen)
{
if (UseObjId)
{
Dwg = Drawing.FindObjId(ObjId);
}
else
{
if (Dwg != null)
{
Dwg = Dwg.CopiedTo(CopiedGen) as TEscherClientDataRecord;
}
if (Dwg != null)
{
ObjId = (UInt16)Dwg.ObjId;
}
}
TEscherImageAnchorRecord Anchor = GetImageRecord();
if (Anchor != null)
{
NoteTextBox = Anchor.SaveCommentCoords(sSheet, Row, Col);
}
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.DataValidation.LoadDVal(this);
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.PivotView.PivotItems.Add(this);
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.WebPub.Add(this);
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.sEOF = this;
Loader.Eof = true;
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
XlsMessages.ThrowException(XlsErr.ErrExcelInvalid);
}
private void AdaptSize(TEscherImageAnchorRecord Anchor, int Row, TSheet dSheet)
{
Anchor.RestoreCommentCoords(NoteTextBox, dSheet, Row, Col);
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.HeaderImages.LoadFromStream(RecordLoader, ws.FWorkbookGlobals, this, ws.SheetType == FlexCel.Core.TSheetType.Chart);
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.SheetProtection.Password = this;
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.Notes.AddRecord(this, rRow);
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.DataValidation[ws.DataValidation.Add(new TDataValidation())].LoadFromStream(RecordLoader, this);
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.SheetExt = this;
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.SheetGlobals.VPageBreaks.AddBiff8Record(this);
}
public ActionResult Export(int payId)
{
PaymentManager pm = new PaymentManager();
Payment payment = pm.LoadPayment(payId);
#region 动态模板
TTemplate template = new TTemplate();
TRow R1 = new TRow();
TRow R2 = new TRow();
int index1 = 3;
int index2 = 3;
TCell A = new TCell("PersonId", "ID", 0, 0); A.RowSpan = 2; A.Width = 1;
TCell B = new TCell("PersonName", "姓名", 0, 1); B.RowSpan = 2;
TCell C = new TCell("PersonCode", "身份证", 0, 2); C.RowSpan = 2;
R1.Cells.Add(A);
R1.Cells.Add(B);
R1.Cells.Add(C);
foreach (PayItemDO item in payment.Items)
{
TCell cell = new TCell();
cell.Name = item.ItemName;
cell.Caption = item.ItemCaption;
if (item.IsLeaf == true)
{
//cell.Width = 50;
}
if (item.ParentId == 0)
{
cell.RowIndex = 0;
cell.ColumnIndex = index1;
int colspan = 1;
foreach (PayItemDO pi in payment.Items)
{
if (pi.ParentId == item.ItemId)
{
colspan++;
}
}
if (colspan == 1)
{
cell.RowSpan = 2;
index1++;
index2++;
}
else
{
cell.ColSpan = colspan - 1;
index1 = index1 + cell.ColSpan;
}
R1.Cells.Add(cell);
}
else
{
cell.RowIndex = 1;
cell.ColumnIndex = index2;
index2 = index2 + 1;
R2.Cells.Add(cell);
}
}
TSheet tsheet = new TSheet();
tsheet.Name = "Sheet1";
tsheet.Title = "Sheet1";
tsheet.Head.Rows.Add(R1);
tsheet.Head.Rows.Add(R2);
template.Sheets.Add(tsheet);
#endregion
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = new DataSet();
payment.DataSource.TableName = "Sheet1";
ds.Tables.Add(payment.DataSource);
POIStream stream = new POIStream();
stream.AllowClose = false;
excel.Write(stream, template, ds, ExcelExtendType.XLSX);
stream.AllowClose = true;
byte[] buffer = new byte[stream.Length];
stream.Position = 0;
stream.Read(buffer, 0, buffer.Length);
stream.Close();
HttpResponse context = System.Web.HttpContext.Current.Response;
try
{
context.ContentType = "application/ms-excel";
context.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(payment.PayTitle, System.Text.Encoding.UTF8)));
context.BinaryWrite(buffer);
context.Flush();
context.End();
}
catch (Exception ex)
{
context.ContentType = "text/plain";
context.Write(ex.Message);
}
return(null);
}
internal override void LoadIntoSheet(TSheet ws, int rRow, TBaseRecordLoader RecordLoader, ref TLoaderInfo Loader)
{
ws.Cells.AddMultipleCells(this, rRow, RecordLoader.VirtualReader);
}