Jump to content

User:Sharjeelbinkhalid

From Wikipedia, the free encyclopedia

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

Retrieved from "https://en.wikipedia.org/w/index.php?title=User:Sharjeelbinkhalid&oldid=1086842512"