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; * } * } * } * } * } * } * */ }