User:Sharjeelbinkhalid

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); } }

 4.0.0 ItineraryParser ItineraryParser 0.0.1-SNAPSHOT ItineraryParser To convert excel into json org.apache.poi poi-ooxml 3.8-beta4 1.8

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