how to export excel from json string ?

Question

Hi  we have requirment to achieve to export excel from json string  that json array wil have different column name  For example Can anyone suggest with example code . ? [ { CASH:866.6000 CASHCount:127 SHOPBACK_P:113.4000 SHOPBACK_PCount:8 U_Credit:560.9000 U_CreditCount:54 VREDEEM:20.0000 VREDEEMCount:4 PayOut Count:3 PayOut Amount:$87.60 Receiving Count:0 Receiving Amount:$0.00 Cash in Drawer:$779.00 Rounding:$0.00 Refund:$0.00 Item Discount:$0.00 Total Discount:$0.00 Member Total Disc Amount:$0.00 Total Price Shift Disc Amount:$0.00 Total Disc Amount:$0.00 Voucher Amount:($26.00) Total Sale:2239.0000 Net Amount:$2239.00 Gross Amount:$2265.00 Hold Count:0 Hold Amount:$0.00 Cancel Count:0 Cancel Amount:$0.00 Void Count:0 Void Amount:$0.00 Exchange Amount:$0.00 No Sales Open:0 No. Of Receipts:252 Receipts Avg:$8.88 } { CASH:660.9000 CASHCount:98 REDEEM:469.1000 REDEEMCount:50 SHOPBACK_P:49.9000 SHOPBACK_PCount:7 U_Credit:319.3000 U_CreditCount:38 VREDEEM:25.0000 VREDEEMCount:5 PayOut Count:2 PayOut Amount:$118.55 Receiving Count:0 Receiving Amount:$0.00 Cash in Drawer:$542.35 Rounding:$0.00 Refund:$0.00 Item Discount:$0.00 Total Discount:$0.00 Member Total Disc Amount:$0.00 Total Price Shift Disc Amount:$0.00 Total Disc Amount:$0.00 Voucher Amount:($15.00) Total Sale:1524.2000 Net Amount:$1524.20 Gross Amount:$1539.20 Hold Count:0 Hold Amount:$0.00 Cancel Count:0 Cancel Amount:$0.00 Void Count:0 Void Amount:$0.00 Exchange Amount:$0.00 No Sales Open:0 No. Of Receipts:192 Receipts Avg:$7.94 } ] Thanks in advance Karthik.K

Amit Mohanty · Answer

By using ClosedXML library you can export data from a JSON array to an Excel file in C#: using System; using System.Collections.Generic; using System.IO; using Newtonsoft.Json; using OfficeOpenXml; public class SalesData { public string CASH { get; set; } public string CASHCount { get; set; } public string SHOPBACK_P { get; set; } public string SHOPBACK_PCount { get; set; } public string U_Credit { get; set; } public string U_CreditCount { get; set; } public string VREDEEM { get; set; } public string VREDEEMCount { get; set; } // Add more properties here for all your columns } public class Program { public static void Main() { // Your JSON data as a string string jsonData = @[ { CASH: 866.6000 CASHCount: 127 SHOPBACK_P: 113.4000 SHOPBACK_PCount: 8 U_Credit: 560.9000 U_CreditCount: 54 VREDEEM: 20.0000 VREDEEMCount: 4 // Add more properties here } { CASH: 660.9000 CASHCount: 98 REDEEM: 469.1000 REDEEMCount: 50 SHOPBACK_P: 49.9000 SHOPBACK_PCount: 7 U_Credit: 319.3000 U_CreditCount: 38 VREDEEM: 25.0000 VREDEEMCount: 5 // Add more properties here } ]; List salesDataList = JsonConvert.DeserializeObject >(jsonData); using (var workbook = new XLWorkbook()) { var worksheet = workbook.Worksheets.Add(SalesData); // Write headers based on your desired column names worksheet.Cell(1 1).Value = CASH; worksheet.Cell(1 2).Value = CASHCount; worksheet.Cell(1 3).Value = SHOPBACK_P; worksheet.Cell(1 4).Value = SHOPBACK_PCount; worksheet.Cell(1 5).Value = U_Credit; worksheet.Cell(1 6).Value = U_CreditCount; worksheet.Cell(1 7).Value = VREDEEM; worksheet.Cell(1 8).Value = VREDEEMCount; // Add more headers for other columns for (int i = 0; i

Lokesh Varman · Answer

To export data from a JSON array with different column names to an Excel file in Python you can use libraries like pandas and openpyxl . Here's an example code import pandas as pd # Your JSON data data = [ { CASH: 866.6000 CASHCount: 127 SHOPBACK_P: 113.4000 # ... (other columns) } { CASH: 660.9000 CASHCount: 98 REDEEM: 469.1000 # ... (other columns) } ] # Create a pandas DataFrame from the JSON data df = pd.DataFrame(data) # Define the desired column order in the Excel file column_order = [ CASH CASHCount SHOPBACK_P # ... (list all other columns in the desired order) ] # Reorder the DataFrame columns based on the specified order df = df[column_order] # Create an Excel writer object excel_writer = pd.ExcelWriter(output.xlsx engine=openpyxl) # Write the DataFrame to an Excel sheet df.to_excel(excel_writer sheet_name=Sheet1 index=False) # Save the Excel file excel_writer.save() Make sure you installed pandas and openpyxl using pip. This code reads your JSON data into a pandas DataFrame reorders the columns in the desired order and then exports it to an Excel file named output.xlsx. You can modify the column_order list to specify the order in which you want the columns to appear in the Excel file.