JSON to XLS in java

Question

Is it possible to turn a JSON into XLS with java? I’ve done an algorithm that writes JSON to CSV, but not XLS. If you can post libraries or examples.

Answer 1

One option is to use the library Apache POI to record the XLS and the library JSON-java to read the JSON. Below is an example of the implementation of the class with the two libraries:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 *
 * @author Lucas Souza [[email protected]]
 *
 * Utilizado na resposta para a pergunta "JSON para XLS em java":
 * /a/229853/59479
 */
public class JSON2XLS {

  public static void transformar(String array, String caminho) {
    JSON2XLS.transformar(new JSONArray(array), caminho);
  }

  public static void transformar(JSONArray array, String caminho) {
    Set<String> campos = new HashSet<>();

    for (Object objeto : array) {
      JSONObject linha = (JSONObject) objeto;

      campos.addAll(linha.keySet());
    }

    JSON2XLS.transformar(new LinkedList<>(campos), array, caminho);
  }

  public static void transformar(List<String> campos, String array, String caminho) {
    JSON2XLS.transformar(campos, new JSONArray(array), caminho);
  }

  public static void transformar(List<String> campos, JSONArray array, String caminho) {
    SXSSFWorkbook xls = new SXSSFWorkbook(50);
    Sheet aba = xls.createSheet();
    FileOutputStream saida;
    File arquivo;

    JSON2XLS.criarCabecalho(campos, aba);

    for (int indice = 1; indice <= array.length(); indice++) {
      List<Object> celulas = new ArrayList();
      JSONObject objeto = array.getJSONObject(indice - 1);

      campos.forEach((campo) -> {
        if (objeto.has(campo)) {
          celulas.add(objeto.get(campo));
        } else {
          celulas.add("");
        }
      });

      JSON2XLS.criarLinha(celulas, aba, indice);
    }

    try {
      arquivo = new File(caminho);

      if (!arquivo.exists()) {
        arquivo.getParentFile().mkdirs();
        arquivo.createNewFile();
      }

      saida = new FileOutputStream(caminho);
      xls.write(saida);
      saida.close();
    } catch (IOException excecao) {
      throw new RuntimeException(excecao);
    }

    xls.dispose();
  }

  private static void criarCabecalho(List<String> rotulos, Sheet aba) {
    CellStyle estilo;
    Font fonte;
    Row row;
    int indice;

    row = aba.createRow(0);

    for (indice = 0; indice < rotulos.size(); indice++) {
      row.createCell(indice).setCellValue(rotulos.get(indice));
    }

    estilo = aba.getWorkbook().createCellStyle();
    fonte = aba.getWorkbook().createFont();
    fonte.setBold(true);
    estilo.setFont(fonte);

    for (indice = 0; indice < row.getLastCellNum(); indice++) {
      row.getCell(indice).setCellStyle(estilo);
    }
  }

  private static void criarLinha(List<Object> celulas, Sheet aba, int indiceLinha) {
    Row linha = aba.createRow(indiceLinha);

    for (int indice = 0; indice < celulas.size(); indice++) {
      Object celula = celulas.get(indice);

      linha.createCell(indice).setCellValue(String.valueOf(celula));
    }
  }
}

The use of the above class would be as follows:

public static void main(String[] args) {
  String json = "[{\"codigo\": 1, \"nome\": \"José\"}, {\"codigo\": 2, \"nome\": \"João\"}]";
  List<String> campos = new LinkedList<>();

  campos.add("codigo");
  campos.add("nome");

  JSON2XLS.transformar(campos, new JSONArray(json), "C:/D/teste/teste.xls");
}

For this case I am considering the following JSON:

[{
  "codigo": 1,
  "nome": "José"
},
{
  "codigo": 2,
  "nome": "João"
}]

If you do not want to inform the fields, use the following implementation:

public static void main(String[] args) {
  String json = "[{\"codigo\": 1, \"nome\": \"José\"}, {\"codigo\": 2, \"nome\": \"João\"}]";

  JSON2XLS.transformar(new JSONArray(json), "C:/D/teste/teste.xls");
}

Answer 2

It is possible yes! Follow an example below:

Use Stringbuilder to generate the file:

StringBuilder xls = new StringBuilder();
xls.append("usuario");
xls.append(",");//Caracter "," significa próxima célula
xls.append("idade");
xls.append("\n"); //Caracter "\n" significa próxima linha
xls.append("Karan");
xls.append(",");
xls.append("24");

Manages the archive:

    File file = new File("teste.xls");
    BufferedWriter writer = null;
    try {
        writer = new BufferedWriter(new FileWriter(file));
        writer.write(xls.toString());
    } finally {
        if (writer != null) {
            writer.close();
        }
    }

Ready! Tested and running.