package itineraryparser;
import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Parser {
public static void main(String[] args) { try { ConvertToJson(args[0], args[1], Integer.parseInt(args[2])); } catch (IOException e) { e.printStackTrace(); } }
private static void ConvertToJson(String file, String table, int id) throws IOException { //ExcelWorkbook book = new ExcelWorkbook(); InputStream inp = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(inp);
XSSFWorkbook test = new XSSFWorkbook(); int totalSheets = wb.getNumberOfSheets(); String Json = "{\"" + table + "\":["; Json = Json + String.valueOf(id); for(int i=1; i<=totalSheets-1; i++) { XSSFSheet sheet = wb.getSheetAt(i); Json = Json+ParseSheet(sheet); } Json = Json + "]}"; Print(Json);
}
private static String ParseSheet(XSSFSheet sheet) { String value = GetCellValue(sheet, 2, 1); String[] lengthsOfItins = value.split(","); int itinsInSheet = lengthsOfItins.length;
for(int i=0; i<itinsInSheet; i++) { int numberOfDays = Integer.parseInt(lengthsOfItins[0]); }
return value; }
private static String GetCellValue(XSSFSheet sheet, int rowIndex, int colIndex) { XSSFRow row = sheet.getRow(rowIndex); XSSFCell cell = row.getCell(colIndex);
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{ return cell.getStringCellValue(); } else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); }
return cell.getStringCellValue(); }
private static void Print(String line) { System.out.print(line);
} }
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion> <groupId>ItineraryParser</groupId> <artifactId>ItineraryParser</artifactId> <version>0.0.1-SNAPSHOT</version> <name>ItineraryParser</name> <description>To convert excel into json</description> <dependencies> <dependency>
<groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.8-beta4</version> </dependency>
</dependencies> <build> <syntaxhighlightDirectory>src</sourceDirectory> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.7.0</version> <configuration>
1.8
<target>1.8</target> </configuration> </plugin> </plugins> </build>
</project>
Dim Json As String
Dim INSERTION_DATE As String
Dim UPDATED_DATE As String
Dim ID As Integer
Sub main()
Dim WS_Count As Integer Dim I As Integer INSERTION_DATE = "20171201T120000Z" UPDATED_DATE = "20171201T120000Z" ID = 0 ' Set WS_Count equal to the number of worksheets in the active ' workbook. WS_Count = ActiveWorkbook.Worksheets.Count ' Begin the loop. Ignore the first sheet For I = 2 To WS_Count ' Insert your code here. ' The following line shows how to reference a sheet within ' the loop by displaying the worksheet name in a dialog box. ParseItinerary ActiveWorkbook.Worksheets(I).Name, CStr(I - 1) Next I
End Sub
Sub ParseItinerary(WorksheetName As String, ID As String)
If WorksheetName = "Main" Then Exit Sub End If Dim ws As Worksheet Set ws = Sheets(WorksheetName) typeOfItins = Split(Trim(ws.Cells(3, 2)), ",") Json = "" AddToJson "{""Itinerary-dev"":[" For I = 0 To UBound(typeOfItins) Dim numberOfDays As Integer If IsNumeric(typeOfItins(I)) Then numberOfDays = CInt(typeOfItins(I)) Else MsgBox "You don't have a correctly formatted Days cell in the " & WorksheetName & " worksheet" End End If AddToJson "{""PutRequest"":{""Item"":" BuildJson ws, numberOfDays If I = UBound(typeOfItins) Then AddToJson "}}" Else AddToJson "}}," End If 'MsgBox JSON Next I
AddToJson "]}" PrintToFile Json, ID & ".txt" 'MsgBox JSON
End Sub
Sub BuildJson(ws As Worksheet, numberOfDays As Integer)
ID = ID + 1 Dim startRow As Integer Dim startCol As Integer startRow = 7 startCol = 1
AddToJson "{" AddToJson """Country"": {""S"": """ & Trim(ws.Cells(1, 2)) & """}," AddToJson """DateInserted"": {""S"": """ & INSERTION_DATE & """}," AddToJson """DateUpdated"": {""S"": """ & UPDATED_DATE & """}," AddToJson """Destination"": {""S"": """ & Trim(ws.Cells(2, 2)) & """}," AddToJson """ID"": {""S"": """ & ID & """}," AddToJson """Rating"": {""N"": """ & 0 & """}," AddToJson """SubmittedBy"": {""S"": """ & Trim(ws.Cells(4, 2)) & """}," AddToJson """TotalDays"": {""N"": """ & numberOfDays & """}," AddToJson """UsersWhoRated1"": {""N"": """ & 0 & """}," AddToJson """UsersWhoRated2"": {""N"": """ & 0 & """}," AddToJson """UsersWhoRated3"": {""N"": """ & 0 & """}," AddToJson """UsersWhoRated4"": {""N"": """ & 0 & """}," AddToJson """UsersWhoRated5"": {""N"": """ & 0 & """}," AddToJson """Days"": {""M"": {" For N = 1 To numberOfDays 'MsgBox I AddToJson """" & N & """: {""M"": {" AddToJson """Activities"": {" AddToJson """L"": [" Dim row As Integer Dim col As Integer row = startRow col = startCol Do While 1 = 1 If IsEmpty(ws.Cells(row, col)) Then 'If there are more days to loop through, put a comma and keep going, otherwise no comma 'If N = numberOfDays Then ' AddToJson "]}" 'Else ' AddToJson "]}," 'End If Exit Do End If ' If this is the second destination in the day, add a comma at the end of the previous one If row <> startRow Then AddToJson "," End If AddToJson "{" AddToJson """M"": {" AddToJson """Location"": {""S"": """ & Trim(ws.Cells(row, col)) & """}," AddToJson """Duration"": {""N"": """ & Trim(ws.Cells(row, col + 1)) & """}" If Not IsEmpty(ws.Cells(row, col + 2)) Then AddToJson "," AddToJson """Type"": {""S"": """ & Trim(ws.Cells(row, col + 2)) & """}" End If AddToJson "}" AddToJson "}" row = row + 1 Loop AddToJson "]" AddToJson "}" AddToJson "}}" If N <> numberOfDays Then AddToJson "," End If startCol = startCol + 4 Next N AddToJson "}}" AddToJson "}"
End Sub
Sub AddToJson(Line As String)
Json = Json & Line
End Sub
Sub PrintToFile(Line As String, fileName As String)
Dim fullPath As String fullPath = Application.ActiveWorkbook.Path & "\" & fileName Open fullPath For Output As #1 Print #1, Line Close #1
End Sub