protected virtual void AfterExecute(GrapeCity.Documents.Excel.Workbook workbook, string[] userAgents)
{
if (AgentIsMac(userAgents))
{
workbook.Calculate(); // ensure that all cached values can be saved in excel file, so number can display the file correctly even if the formulas are not supported in number.
}
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyIsErrorFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = CalcError.Num;
worksheet.Range["A2"].Value = 100;
worksheet.Range["B1"].Formula = "=MyIsError(A1)";
worksheet.Range["B2"].Formula = "=MyIsError(A2)";
//Range["B1"]'s value is true.
var resultB1 = worksheet.Range["B1"].Value;
//Range["B2"]'s value is false.
var resultB2 = worksheet.Range["B2"].Value;
/* Implementation of MyIsErrorFunctionX
*
* public class MyIsErrorFunctionX : CustomFunction
* {
* public MyIsErrorFunctionX()
* : base("MyIsError", FunctionValueType.Boolean, new Parameter[] { new Parameter(FunctionValueType.Variant) })
* {
*
* }
*
* public override object Evaluate(object[] arguments, ICalcContext context)
* {
* if (arguments[0] is CalcError)
* {
* if ((CalcError)arguments[0] != CalcError.None && (CalcError)arguments[0] != CalcError.GettingData)
* {
* return true;
* }
* else
* {
* return false;
* }
* }
*
* return false;
* }
* }
*
*/
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyAddFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = 1;
worksheet.Range["B1"].Value = 2;
worksheet.Range["C1"].Formula = "=MyAdd(A1, B1)";
//Range["C1"]'s value is 3.
var result = worksheet.Range["C1"].Value;
worksheet.Range["E1:F2"].Value = new object[, ] {
{ 1, 3 },
{ 2, 4 }
};
//Custom function use in array formula.
worksheet.Range["G1:G2"].FormulaArray = "=MyAdd(E1:E2, F1:F2)";
//Range["G1"]'s value is 4, Range["G2"]'s value is 6.
var resultG1 = worksheet.Range["G1"].Value;
var resultG2 = worksheet.Range["G2"].Value;
/* Implementation of MyAddFunctionX
*
* public class MyAddFunctionX : CustomFunction
* {
* public MyAddFunctionX()
* : base("MyAdd", FunctionValueType.Number, new Parameter[] { new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) })
* {
*
* }
*
* public override object Evaluate(object[] arguments, ICalcContext context)
* {
* return (double)arguments[0] + (double)arguments[1];
* }
* }
*
*/
}
public static async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "post", Route = null)] HttpRequest req,
[Blob("template/SimpleInvoiceJP.xlsx", FileAccess.Read, Connection = "AzureWebJobsStorage")] Stream inputBlob,
[Blob("output/ResultInvoiceJP.xlsx", FileAccess.Write, Connection = "AzureWebJobsStorage")] Stream outxlsxBlob,
[Blob("output/ResultInvoiceJP.pdf", FileAccess.Write, Connection = "AzureWebJobsStorage")] Stream outpdfBlob,
ILogger log)
{
string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
dynamic data = JsonConvert.DeserializeObject <Data>(requestBody);
if (data is null)
{
return(new BadRequestObjectResult("Please pass a data in the request body"));
}
else
{
//トライアル用
string key = System.Environment.GetEnvironmentVariable("DdExcelLicenseString", EnvironmentVariableTarget.Process);
Workbook.SetLicenseKey(key);
//新しいワークブックを生成
var workbook = new GrapeCity.Documents.Excel.Workbook();
//テンプレートを読み込む
workbook.Open(inputBlob);
//ワークシートの取得
var worksheet = workbook.ActiveSheet;
//発行元情報をセルに指定
CultureInfo culture = new CultureInfo("ja-JP");
worksheet.Range["I1"].Value = DateTime.Now.ToString("D", culture); //発行日
var ticks = DateTime.Now.Ticks.ToString();
worksheet.Range["H3"].Value = DateTime.Now.Day.ToString("00")
+ "-" + ticks.Substring(ticks.Length - 4, 4); //伝票番号
worksheet.Range["I3"].Value = data.publisher.representative; //担当者
worksheet.Range["G8"].Value = data.publisher.companyname; //発行元
worksheet.Range["G9"].Value = "〒" + data.publisher.postalcode; //郵便番号
worksheet.Range["G10"].Value = data.publisher.address1 + data.publisher.address2; //所在地
worksheet.Range["H12"].Value = data.publisher.tel; //電話番号
worksheet.Range["G13"].Value = data.publisher.bankname; //銀行名
worksheet.Range["H13"].Value = data.publisher.bankblanch; //支店名
worksheet.Range["H14"].Value = data.publisher.account; //口座番号
//顧客情報をセルに指定
worksheet.Range["A3"].Value = "〒" + data.customer.postalcode; //郵便番号
worksheet.Range["A4"].Value = data.customer.address1; //住所1
worksheet.Range["A5"].Value = data.customer.address2; //住所2
worksheet.Range["A6"].Value = data.customer.companyname; //会社名
worksheet.Range["A8"].Value = data.customer.name; //氏名
//明細の開始位置を指定
var dt_init_row = 17;
var lines_mun = 2; //1明細で2行利用
//明細データ分の繰り返し
for (int i = 0; i < data.customer.detail.Length; i++)
{
var this_item = i * lines_mun;
worksheet.Range[dt_init_row + this_item, 0].Value = (string)data.customer.detail[i].sku; //商品番号
worksheet.Range[(dt_init_row + 1) + this_item, 0].Value = data.customer.detail[i].name; //商品名
worksheet.Range[dt_init_row + this_item, 4].Value = data.customer.detail[i].price; //単価
worksheet.Range[dt_init_row + this_item, 5].Value = data.customer.detail[i].unit; //数量
worksheet.Range[dt_init_row + this_item, 7].Value = data.customer.detail[i].remark; //備考
}
//Excelファイルに保存
workbook.Save(outxlsxBlob);
//PDFファイルに保存
workbook.Save(outpdfBlob, SaveFileFormat.Pdf);
return(new OkObjectResult("Finished."));
}
}
public virtual void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
}
protected virtual void BeforeExecute(GrapeCity.Documents.Excel.Workbook workbook, string[] userAgents)
{
}
public void ExecuteExample(GrapeCity.Documents.Excel.Workbook workbook, string[] userAgents)
{
this.BeforeExecute(workbook, userAgents);
this.Execute(workbook);
this.AfterExecute(workbook, userAgents);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyConcatenateFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Formula = "=MyConcatenate(\"I\", \" \", \"live\", \" \", \"in\", \" \", \"Xi'an\", \".\")";
worksheet.Range["A2"].Formula = "=MyConcatenate(A1, \"haha.\")";
worksheet.Range["B1"].Value = 12;
worksheet.Range["B2"].Value = 34;
worksheet.Range["B3"].Formula = "=MyConcatenate(B1, B2)";
worksheet.Range["M5:N5"].FormulaArray = "=CONCATENATE({\"aa\",\"bb\"}, 12, 34)";
//"I live in Xi'an."
var resultA1 = worksheet.Range["A1"].Value;
//"I live in Xi'an.haha."
var resultA2 = worksheet.Range["A2"].Value;
//"1234"
var resultB3 = worksheet.Range["B3"].Value;
//"aa1234"
var resultM5 = worksheet.Range["M5"].Value;
//"bb1234"
var resultN5 = worksheet.Range["N5"].Value;
/* Implementation of MyAddFunctionX
*
* public class MyConcatenateFunctionX : CustomFunction
* {
* public MyConcatenateFunctionX()
* : base("MyConcatenate", FunctionValueType.Text, CreateParameters())
* {
*
* }
*
* private static Parameter[] CreateParameters()
* {
* Parameter[] parameters = new Parameter[254];
* for (int i = 0; i < 254; i++)
* {
* parameters[i] = new Parameter(FunctionValueType.Variant);
* }
*
* return parameters;
* }
*
* public override object Evaluate(object[] arguments, ICalcContext context)
* {
* StringBuilder sb = new StringBuilder();
*
* string result = string.Empty;
* foreach (var argument in arguments)
* {
* if (argument is CalcError)
* {
* return argument;
* }
* if (argument is string || argument is double)
* {
* sb.Append(argument);
* }
* }
*
* return sb.ToString();
* }
*
* }
*
*/
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MySumFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = 1;
worksheet.Range["B1"].Value = 2;
worksheet.Range["C1"].Formula = "=MySum(A1:B1, 2, {3,4})";
//Range["C1"]'s value is 12.
var result = worksheet.Range["C1"].Value;
/* Implementation of MySumFunctionX
*
* public class MySumFunctionX : CustomFunction
* {
* public MySumFunctionX() : base("MYSUM", FunctionValueType.Number, CreateParameters())
* {
*
* }
*
* private static Parameter[] CreateParameters()
* {
* Parameter[] parameters = new Parameter[254];
* for (int i = 0; i < 254; i++)
* {
* parameters[i] = new Parameter(FunctionValueType.Object);
* }
*
* return parameters;
* }
*
* public override object Evaluate(object[] arguments, ICalcContext context)
* {
* double sum = 0d;
* foreach (var argument in arguments)
* {
* foreach (var item in Enumerate(argument))
* {
* if (item is CalcError)
* {
* return item;
* }
* if (item is double)
* {
* sum += (double)item;
* }
* }
* }
*
* return sum;
* }
*
* private static IEnumerable<object> Enumerate(object obj)
* {
* if (obj is IEnumerable<object>)
* {
* foreach (var item in obj as IEnumerable<object>)
* {
* foreach (var item2 in Enumerate(item))
* {
* yield return item2;
* }
* }
* }
* else if (obj is object[,])
* {
* var array = obj as object[,];
* int rowCount = array.GetLength(0);
* int colCount = array.GetLength(1);
* for (int i = 0; i < rowCount; i++)
* {
* for (int j = 0; j < colCount; j++)
* {
* yield return array[i, j];
* }
* }
* }
*
* if (obj != null)
* {
* yield return obj;
* }
* }
* }
*
*/
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyConditionalSumFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:A10"].Value = new object[, ] {
{ 1 },
{ 2 },
{ 3 },
{ 4 },
{ 5 },
{ 6 },
{ 7 },
{ 8 },
{ 9 },
{ 10 }
};
IFormatCondition cellValueRule = worksheet.Range["A1:A10"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 5) as IFormatCondition;
cellValueRule.Interior.Color = System.Drawing.Color.Red;
//Sum cells value which display format interior color are red.
worksheet.Range["C1"].Formula = "=MyConditionalSum(A1:A10)";
//Range["C1"]'s value is 40.
var result = worksheet.Range["C1"].Value;
/* Implementation of MyConditionalSumFunctionX
*
* public class MyConditionalSumFunctionX : CustomFunction
* {
* public MyConditionalSumFunctionX() : base("MyConditionalSum", FunctionValueType.Number, CreateParameters())
* {
*
* }
*
* private static Parameter[] CreateParameters()
* {
* Parameter[] parameters = new Parameter[254];
* for (int i = 0; i < 254; i++)
* {
* parameters[i] = new Parameter(FunctionValueType.Object, true);
* }
*
* return parameters;
* }
*
* public override object Evaluate(object[] arguments, ICalcContext context)
* {
* double sum = 0d;
* foreach (var argument in arguments)
* {
* foreach (var item in Enumerate(argument))
* {
* if (item is CalcError)
* {
* return item;
* }
* if (item is double)
* {
* sum += (double)item;
* }
* }
* }
*
* return sum;
* }
*
* private static IEnumerable<object> Enumerate(object obj)
* {
* if (obj is IEnumerable<object>)
* {
* foreach (var item in obj as IEnumerable<object>)
* {
* foreach (var item2 in Enumerate(item))
* {
* yield return item2;
* }
* }
* }
* else if (obj is object[,])
* {
* var array = obj as object[,];
* int rowCount = array.GetLength(0);
* int colCount = array.GetLength(1);
* for (int i = 0; i < rowCount; i++)
* {
* for (int j = 0; j < colCount; j++)
* {
* yield return array[i, j];
* }
* }
* }
* else if (obj is CalcReference)
* {
* foreach (var item in Enumerate(obj as CalcReference))
* {
* yield return item;
* }
* }
*
* yield return obj;
* }
*
* private static IEnumerable<object> Enumerate(CalcReference reference)
* {
* foreach (var range in reference.GetRanges())
* {
* int rowCount = range.Rows.Count;
* int colCount = range.Columns.Count;
* for (int i = 0; i < rowCount; i++)
* {
* for (int j = 0; j < colCount; j++)
* {
* if (range.Cells[i, j].DisplayFormat.Interior.Color == System.Drawing.Color.Red)
* {
* yield return range.Cells[i, j].Value;
* }
* }
* }
* }
* }
* }
*
*/
}
