private doGenerateDocumentResult GenerateACC010_Sheet1(DocumentContext context, SLDocument doc, string sheetname)
{
#region Prepare
doGenerateDocumentResult result = new doGenerateDocumentResult()
{
ErrorFlag = false,
Total = 0,
Complete = 0,
Failed = 0,
ResultDocumentNoList = string.Empty
};
#endregion
#region Constants
const int ROW_HEADER = 1;
const int COL_HEADER_TITLE = 1;
const int ROW_TBLHDR = 3;
int columncount = 0;
int COL_ROWNUMBER = ++columncount;
int COL_CUSTFULLNAMEEN = ++columncount;
int COL_CONTRACTCODE = ++columncount;
int COL_SITENAMEEN = ++columncount;
int COL_PLANCODE = ++columncount;
int COL_OFFICENAMEEN = ++columncount;
int COL_CUSTACCEPTANCEDATE = ++columncount;
int COL_PRODUCTNAMEEN = ++columncount;
int COL_SALESMANNAME = ++columncount;
int COL_ORDERPRODUCTPRICECURRENCY = ++columncount;
int COL_ORDERPRODUCTPRICE = ++columncount;
int COL_INSTRUMENTCOSTCURRENCY = ++columncount;
int COL_INSTRUMENTCOST = ++columncount;
int COL_ORDERINSTALLFEECURRENCY = ++columncount;
int COL_ORDERINSTALLFEE = ++columncount;
int COL_PAYTOSUBCONTRACTORCURRENCY = ++columncount;
int COL_PAYTOSUBCONTRACTOR = ++columncount;
#endregion
if (doc.GetSheetNames().Contains(sheetname))
{
doc.DeleteWorksheet(sheetname);
}
doc.AddWorksheet(sheetname);
doc.SelectWorksheet(sheetname);
doc.SetCellStyle(ROW_HEADER, COL_HEADER_TITLE, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 12, Bold = true
}
});
doc.SetCellValue(ROW_HEADER, COL_HEADER_TITLE, "New operation sales report");
doc.SetCellStyle(ROW_TBLHDR, COL_HEADER_TITLE, ROW_TBLHDR, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = true
}
});
doc.SetCellValue(ROW_TBLHDR, COL_ROWNUMBER, "NO.");
doc.SetCellValue(ROW_TBLHDR, COL_CUSTFULLNAMEEN, "CUSTFULLNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_CONTRACTCODE, "CONTRACTNO.");
doc.SetCellValue(ROW_TBLHDR, COL_SITENAMEEN, "PREMISE'SNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_PLANCODE, "PLANCODE");
doc.SetCellValue(ROW_TBLHDR, COL_OFFICENAMEEN, "OFFICENAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_CUSTACCEPTANCEDATE, "CUSTACCEPTANCEDATE");
doc.SetCellValue(ROW_TBLHDR, COL_PRODUCTNAMEEN, "PRODUCTNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_SALESMANNAME, "SALESMANNAME");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERPRODUCTPRICECURRENCY, "ORDERPRODUCTPRICECURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERPRODUCTPRICE, "ORDERPRODUCTPRICE");
doc.SetCellValue(ROW_TBLHDR, COL_INSTRUMENTCOSTCURRENCY, "INSTRUMENTCOSTCURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_INSTRUMENTCOST, "INSTRUMENTCOST");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERINSTALLFEECURRENCY, "ORDERINSTALLFEECURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERINSTALLFEE, "ORDERINSTALLFEE");
doc.SetCellValue(ROW_TBLHDR, COL_PAYTOSUBCONTRACTORCURRENCY, "PAYTOSUBCONTRACTORCURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_PAYTOSUBCONTRACTOR, "PAYTOSUBCONTRACTOR");
var accountData = this.GetAccountDataOfACC010(context.TargetPeriodFrom, context.TargetPeriodTo, context.UserHQCode);
if (accountData.Count == 0)
{
result.ErrorFlag = true;
result.ErrorCode = MessageUtil.MessageList.MSG8005;
return(result);
}
result.Complete = accountData.Count;
result.Total = accountData.Count;
int rowindex = ROW_TBLHDR + 1;
doc.SetCellStyle(rowindex, COL_HEADER_TITLE, rowindex + accountData.Count - 1, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = false
}
});
foreach (var rowdata in accountData)
{
if (rowdata.RowNumber != null)
{
doc.SetCellValue(rowindex, COL_ROWNUMBER, rowdata.RowNumber.Value);
}
doc.SetCellValue(rowindex, COL_CUSTFULLNAMEEN, rowdata.CustFullNameEN);
doc.SetCellValue(rowindex, COL_CONTRACTCODE, rowdata.ContractCode);
doc.SetCellValue(rowindex, COL_SITENAMEEN, rowdata.SiteNameEN);
doc.SetCellValue(rowindex, COL_PLANCODE, rowdata.PlanCode);
doc.SetCellValue(rowindex, COL_OFFICENAMEEN, rowdata.OfficeNameEN);
if (rowdata.CustAcceptanceDate != null)
{
doc.SetCellValue(rowindex, COL_CUSTACCEPTANCEDATE, rowdata.CustAcceptanceDate.Value.ToString("dd-MMM-yyyy"));
}
doc.SetCellValue(rowindex, COL_PRODUCTNAMEEN, rowdata.ProductNameEN);
doc.SetCellValue(rowindex, COL_SALESMANNAME, rowdata.SalesmanName);
if (rowdata.OrderProductPriceCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_ORDERPRODUCTPRICECURRENCY, rowdata.OrderProductPriceCurrencyType);
}
if (rowdata.OrderProductPrice != null)
{
doc.SetCellValue(rowindex, COL_ORDERPRODUCTPRICE, rowdata.OrderProductPrice.Value);
}
if (rowdata.InstrumentCostCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_INSTRUMENTCOSTCURRENCY, rowdata.InstrumentCostCurrencyType);
}
if (rowdata.InstrumentCost != null)
{
doc.SetCellValue(rowindex, COL_INSTRUMENTCOST, rowdata.InstrumentCost.Value);
}
if (rowdata.OrderInstallFee != null)
{
doc.SetCellValue(rowindex, COL_ORDERINSTALLFEE, rowdata.OrderInstallFee.Value);
}
if (rowdata.OrderInstallFeeCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_ORDERINSTALLFEECURRENCY, rowdata.OrderInstallFeeCurrencyType);
}
if (rowdata.PayToSubcontractor != null)
{
doc.SetCellValue(rowindex, COL_PAYTOSUBCONTRACTOR, rowdata.PayToSubcontractor.Value);
}
rowindex++;
}
doc.Filter(ROW_TBLHDR, COL_ROWNUMBER, rowindex - 1, COL_PAYTOSUBCONTRACTOR);
doc.SetCellValue(SLConvert.ToCellReference(rowindex, COL_ORDERPRODUCTPRICE), string.Format("=SUBTOTAL({0})", SLConvert.ToCellRange(ROW_TBLHDR + 1, COL_ORDERPRODUCTPRICE, rowindex - 1, COL_ORDERPRODUCTPRICE)));
return(result);
}
public void ShouldWriteHello()
{
SLDocument sl = new SLDocument();
// set a boolean at "A1"
sl.SetCellValue("A1", true);
// set at row 2, columns 1 through 20, a value that's equal to the column index
for (int i = 1; i <= 20; ++i)
{
sl.SetCellValue(2, i, i);
}
// set the value of PI
sl.SetCellValue("B3", 3.14159);
// set the value of PI at row 4, column 2 (or "B4") in string form.
// use this when you already have numeric data in string form and don't
// want to parse it to a double or float variable type
// and then set it as a value.
// Note that "3,14159" is invalid. Excel (or Open XML) stores numerals in
// invariant culture mode. Frankly, even "1,234,567.89" is invalid because
// of the comma. If you can assign it in code, then it's fine, like so:
// double fTemp = 1234567.89;
sl.SetCellValueNumeric(4, 2, "3.14159");
// normal string data
sl.SetCellValue("C6", "This is at C6!");
// typical XML-invalid characters are taken care of,
// in particular the & and < and >
sl.SetCellValue("I6", "Dinner & Dance costs < $10");
// this sets a cell formula
// Note that if you want to set a string that starts with the equal sign,
// but is not a formula, prepend a single quote.
// For example, "'==" will display 2 equal signs
sl.SetCellValue(7, 3, "=SUM(A2:T2)");
// if you need cell references and cell ranges *really* badly, consider the SLConvert class.
sl.SetCellValue(SLConvert.ToCellReference(7, 4), string.Format("=SUM({0})", SLConvert.ToCellRange(2, 1, 2, 20)));
// dates need the format code to be displayed as the typical date.
// Otherwise it just looks like a floating point number.
sl.SetCellValue("C8", new DateTime(3141, 5, 9));
SLStyle style = sl.CreateStyle();
style.FormatCode = "d-mmm-yyyy";
sl.SetCellStyle("C8", style);
sl.SetCellValue(8, 6, "I predict this to be a significant date. Why, I do not know...");
sl.SetCellValue(9, 4, 456.123789);
// we don't have to create a new SLStyle because
// we only used the FormatCode property
style.FormatCode = "0.000%";
sl.SetCellStyle(9, 4, style);
sl.SetCellValue(9, 6, "Perhaps a phenomenal growth in something?");
sl.SaveAs("HelloWorld.xlsx");
}
static void Main(string[] args)
{
int i, j;
//double fValue;
Random rand = new Random();
double[] doubledata = new double[] { rand.Next(10), rand.NextDouble(), 2.3, 4.5, 6.9 };
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Random");
for (i = 1; i <= 16; ++i)
{
for (j = 1; j <= 6; ++j)
{
switch (rand.Next(5))
{
case 0:
case 1:
sl.SetCellValue(i, j, doubledata[rand.Next(doubledata.Length)]);
break;
case 2:
case 3:
sl.SetCellValue(i, j, rand.NextDouble() * 1000.0 + 350.0);
break;
case 4:
if (rand.NextDouble() < 0.5)
{
sl.SetCellValueNumeric(i, j, "3.1415926535898");
}
else
{
sl.SetCellValueNumeric(i, j, "2.7182818284590");
}
break;
}
}
}
//sl.Filter("A1", "F1");
//sl.FlattenAllSharedCellFormula();
//sl.DrawBorderGrid
//sl.SetCellValue("C6", "This is at C6!");
//sl.SetCellValue(SLConvert.ToCellReference(7, 4), string.Format("=SUM({0})", SLConvert.ToCellRange(2, 1, 2, 20)));
//sl.SetCellValue("C8", new DateTime(3141, 5, 9));
//SLStyle style = sl.CreateStyle();
//style.FormatCode = "d-mmm-yyyy";
//sl.SetCellStyle("C8", style);
//for (int i = 1; i <= 20; ++i) sl.SetCellValue(2, i, i);
sl.SaveAs("Miscellaneous1.xlsx");
SLDocument tl = new SLDocument("Miscellaneous1.xlsx", "Sheet");
//以下两个方法功能一致
tl.SetCellValue("G1", "=SUM(A1:F2)");
tl.SetCellValue(SLConvert.ToCellReference(2, 7), string.Format("=SUM({0})", SLConvert.ToCellRange(1, 1, 2, 6)));
tl.SetCellValue("G3", "=A2-A3");
//tl.SetCellValueNumeric("G6", "=AVERAGE(A1:F2)");
//tl.SetCellValue("G2", string.Format(" =SUM({0})", SLConvert.ToCellRange(1, 1, 2, 6)));
//以下两个方法功能一致
tl.SetCellValue("G4", StringValue.ToString("=AVERAGE(A1:F2)"));
tl.SetCellValue("G5", String.Format("=AVERAGE({0})", SLConvert.ToCellRange(1, 1, 2, 6)));
tl.SetCellValue("G6", "So this is the random number table");
tl.SetCellValue("G7", "The time is ");
tl.SetCellValue("H7", DateTime.Now.ToString()); //获取当前日期和时间
tl.AddWorksheet("Secret"); //添加工作表
tl.SelectWorksheet("Random"); //选择Random工作表
tl.RenameWorksheet("Secret", "again"); //重命名Secret
SLStyle style = tl.CreateStyle(); //设置单元格格式
style.SetFont("Impact", 24);
style.Font.Underline = UnderlineValues.Single;
tl.SetCellStyle(1, 7, style);
tl.SetCellStyle("G6", style);
//设置EXCEL属性
tl.DocumentProperties.Creator = "ZhouL";
tl.DocumentProperties.ContentStatus = "Secret";
tl.DocumentProperties.Title = "Random number table";
tl.DocumentProperties.Description = "Get data and manipulate it and export it";
tl.SaveAs("MiscellaneousModified.xlsx");
}
Console.WriteLine("End of program");
Console.ReadLine();
}