java – How to create and write to Excel file (.xlsx)?-ThrowExceptions

Exception or error:

There are libraries for Java developers that have tons of methods which do not work on Android.

I started by working with libraries like OpenCSV, but unfortunately Excel has known issues with opening CSV files.

Then I tried to use:

  • Apache POI – It definitely has too many methods.
  • JExcelAPI – It works, but only on old binary .xls files.
  • docx4j – again too many jars, because it’s based on JAXB which is not included in Android.

My question is, how can I create a simple Excel file in .xlsx format on Android, without exceeding 65k methods?

How to solve:

First answer: do it server-side.

If that’s not possible, just use JExecelAPI – pretty much everything that reads xlsx files reads xls files too.

Every other Excel library is going to be way too big.

Another thought – write csv files, either manually, or with one of the many csv libraries available. Again, most applications that read Excel files read csv files too.

###

Since this question seems to be rather: “What is the most lightweight way creating Office Open XML files for Excel (*.xlsx)?”, I will providing an example which does not need any libraries except the default java.lang, java.io and java.util.zip.

A *.xlsx file is nothing else than a ZIP archive containing XML files and other files in a directory structure. So all we need is a possibility for creating, reading and writing ZIP archives and creating, reading and writing XML files. For the ZIP part I am using java.util.zip and for the XML part I am using string manipulation. This, creating and manipulating XML via string manipulation, is not the most recommended way for manipulating XML but it is the most lightweight way since it does not needs any additional XML libraries.

Complete example:

import java.io.OutputStream;
import java.io.ByteArrayOutputStream;

import java.util.zip.*;

public class CreateXLSXFromScratch {

 //some static parts of the XLSX file:

 static String content_types_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\"><Default ContentType=\"application/vnd.openxmlformats-package.relationships+xml\" Extension=\"rels\"/><Default ContentType=\"application/xml\" Extension=\"xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.extended-properties+xml\" PartName=\"/docProps/app.xml\"/><Override ContentType=\"application/vnd.openxmlformats-package.core-properties+xml\" PartName=\"/docProps/core.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml\" PartName=\"/xl/sharedStrings.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml\" PartName=\"/xl/styles.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\" PartName=\"/xl/workbook.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml\" PartName=\"/xl/worksheets/sheet1.xml\"/></Types>";

 static String docProps_app_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\"><Application>" + "Created Low level From Scratch" + "</Application></Properties>";

 static String docProps_core_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"><dcterms:created xsi:type=\"dcterms:W3CDTF\">" + java.time.Instant.now().truncatedTo(java.time.temporal.ChronoUnit.SECONDS).toString() + "</dcterms:created><dc:creator>" + "Axel Richter from scratch" + "</dc:creator></cp:coreProperties>";

 static String _rels_rels_xml  = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\"><Relationship Id=\"rId1\" Target=\"xl/workbook.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument\"/><Relationship Id=\"rId2\" Target=\"docProps/app.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties\"/><Relationship Id=\"rId3\" Target=\"docProps/core.xml\" Type=\"http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties\"/></Relationships>";

 static String xl_rels_workbook_xml_rels_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\"><Relationship Id=\"rId1\" Target=\"sharedStrings.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings\"/><Relationship Id=\"rId2\" Target=\"styles.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles\"/><Relationship Id=\"rId3\" Target=\"worksheets/sheet1.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet\"/></Relationships>";

 static String xl_sharedstrings_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><sst count=\"0\" uniqueCount=\"0\" xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"/>"; 

 static String xl_styles_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><numFmts count=\"0\"/><fonts count=\"1\"><font><sz val=\"11.0\"/><color indexed=\"8\"/><name val=\"Calibri\"/><family val=\"2\"/><scheme val=\"minor\"/></font></fonts><fills count=\"2\"><fill><patternFill patternType=\"none\"/></fill><fill><patternFill patternType=\"darkGray\"/></fill></fills><borders count=\"1\"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\"/></cellStyleXfs><cellXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/></cellXfs></styleSheet>";

 static String xl_workbook_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\"><workbookPr date1904=\"false\"/><bookViews><workbookView activeTab=\"0\"/></bookViews><sheets><sheet name=\"" + "Sheet1" + "\" r:id=\"rId3\" sheetId=\"1\"/></sheets></workbook>";

 static String xl_worksheets_sheet1_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><dimension ref=\"A1\"/><sheetViews><sheetView workbookViewId=\"0\" tabSelected=\"true\"/></sheetViews><sheetFormatPr defaultRowHeight=\"15.0\"/><sheetData/><pageMargins bottom=\"0.75\" footer=\"0.3\" header=\"0.3\" left=\"0.7\" right=\"0.7\" top=\"0.75\"/></worksheet>";

 public static void main(String[] args) throws Exception {

  // result goes into a ByteArrayOutputStream
  ByteArrayOutputStream resultout = new ByteArrayOutputStream();

  // needed objects
  ZipEntry zipentry = null;
  byte[] data = null;

  // create ZipOutputStream
  ZipOutputStream zipout = new ZipOutputStream(resultout);

  // create the static parts of the XLSX ZIP file:

  zipentry = new ZipEntry("[Content_Types].xml");
  zipout.putNextEntry(zipentry);
  data = content_types_xml.getBytes();
  zipout.write(data, 0, data.length);
  zipout.closeEntry();

  zipentry = new ZipEntry("docProps/app.xml");
  zipout.putNextEntry(zipentry);
  data = docProps_app_xml.getBytes();
  zipout.write(data, 0, data.length);
  zipout.closeEntry();

  zipentry = new ZipEntry("docProps/core.xml");
  zipout.putNextEntry(zipentry);
  data = docProps_core_xml.getBytes();
  zipout.write(data, 0, data.length);
  zipout.closeEntry();

  zipentry = new ZipEntry("_rels/.rels");
  zipout.putNextEntry(zipentry);
  data = _rels_rels_xml.getBytes();
  zipout.write(data, 0, data.length);
  zipout.closeEntry();

  zipentry = new ZipEntry("xl/_rels/workbook.xml.rels");
  zipout.putNextEntry(zipentry);
  data = xl_rels_workbook_xml_rels_xml.getBytes();
  zipout.write(data, 0, data.length);
  zipout.closeEntry();

  zipentry = new ZipEntry("xl/sharedStrings.xml");
  zipout.putNextEntry(zipentry);
  data = xl_sharedstrings_xml.getBytes();
  zipout.write(data, 0, data.length);
  zipout.closeEntry();

  zipentry = new ZipEntry("xl/styles.xml");
  zipout.putNextEntry(zipentry);
  data = xl_styles_xml.getBytes();
  zipout.write(data, 0, data.length);
  zipout.closeEntry();

  zipentry = new ZipEntry("xl/workbook.xml");
  zipout.putNextEntry(zipentry);
  data = xl_workbook_xml.getBytes();
  zipout.write(data, 0, data.length);
  zipout.closeEntry();

  // preparing the sheet data:

  Object[][] sheetData = new Object[][] {
   {"Text", "Value", "Formula"},
   {"Text1", 1.23456, "=SIN(B2)"},
   {"Text2", 2.34567, "=SQRT(B3)"},
   {"Text3", 123.456, "=B4/10"}
  };
  String sheetdata = "<sheetData>";
  int r = 0;
  char c = 'A'; --c;
  for (Object[] rowData : sheetData) {
   sheetdata += "<row r=\"" + ++r + "\">";
   c = 'A'; --c;
   for (Object cellData : rowData) {
    sheetdata += "<c r=\"" + Character.toString(++c) + r + "\"";
    if (cellData instanceof String && ((String)cellData).startsWith("=")) {
     sheetdata += "><f>" + ((String)cellData).replace("=", "") + "</f></c>";
    } else if (cellData instanceof String) {
     sheetdata += " t=\"inlineStr\"><is><t>" + ((String)cellData) + "</t></is></c>";
    } else if (cellData instanceof Double) {
     sheetdata += "><v>" + ((Double)cellData) + "</v></c>";
    }
   }
   sheetdata += "</row>";
  }
  sheetdata += "</sheetData>";

  // get the static sheet xml into a buffer for further processing
  StringBuffer xl_worksheets_sheet1_xml_buffer = new StringBuffer(xl_worksheets_sheet1_xml);

  // get position of the <dimension ref=\"A1\"/> in the static xl_worksheets_sheet1_xml
  int dimensionstart = xl_worksheets_sheet1_xml_buffer.indexOf("<dimension ref=\"A1\"/>");
  // replace the <dimension ref=\"A1\"/> with the new dimension
  xl_worksheets_sheet1_xml_buffer = xl_worksheets_sheet1_xml_buffer.replace(
   dimensionstart, 
   dimensionstart + "<dimension ref=\"A1\"/>".length(), 
   "<dimension ref=\"A1:" + Character.toString(c) + r + "\"/>");

  // get position of the <sheetData/> in the static xl_worksheets_sheet1_xml
  int sheetdatastart = xl_worksheets_sheet1_xml_buffer.indexOf("<sheetData/>");
  // replace the <sheetData/> with the prepared sheet date string
  xl_worksheets_sheet1_xml_buffer = xl_worksheets_sheet1_xml_buffer.replace(
   sheetdatastart, 
   sheetdatastart + "<sheetData/>".length(), 
   sheetdata);

  // create the xl/worksheets/sheet1.xml
  zipentry = new ZipEntry("xl/worksheets/sheet1.xml");
  zipout.putNextEntry(zipentry);
  data = xl_worksheets_sheet1_xml_buffer.toString().getBytes();
  zipout.write(data, 0, data.length);
  zipout.closeEntry();

  zipout.finish();

  // now ByteArrayOutputStream resultout contains the XLSX file data

  // writing this data into a file
  try (java.io.FileOutputStream fileout = new java.io.FileOutputStream("test.xlsx")) {
   resultout.writeTo(fileout);
   resultout.close();
  }

 }
}

###

It’s 2018. Use Microsoft Graph API and create the Excel file in O365.

Microsoft has published a few examples in Angular and C#. It’s not Java but it is a good starting point: https://developer.microsoft.com/en-us/graph/docs/concepts/excel-write-to-workbook.

The MS Graph Java SDK is Android compatible.

Limitation – there is no easy way to create an Excel file from scratch using the API. You may want to keep a blank workbook and clone it each time.

###

update of Axel Richter’s answer:

  • add cell dynamically
  • unlimited columns (not only 26)
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class CreatePlainXLSX {

    //some static parts of the XLSX file:

    static String content_types_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\"><Default ContentType=\"application/vnd.openxmlformats-package.relationships+xml\" Extension=\"rels\"/><Default ContentType=\"application/xml\" Extension=\"xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.extended-properties+xml\" PartName=\"/docProps/app.xml\"/><Override ContentType=\"application/vnd.openxmlformats-package.core-properties+xml\" PartName=\"/docProps/core.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml\" PartName=\"/xl/sharedStrings.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml\" PartName=\"/xl/styles.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\" PartName=\"/xl/workbook.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml\" PartName=\"/xl/worksheets/sheet1.xml\"/></Types>";

    static String docProps_app_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\"><Application>" + "Created Low level From Scratch" + "</Application></Properties>";

    static SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");

    static String docProps_core_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"><dcterms:created xsi:type=\"dcterms:W3CDTF\">" + formatter.format(Calendar.getInstance().getTime()) /*java.time.Instant.now().truncatedTo(java.time.temporal.ChronoUnit.SECONDS).toString()*/ + "</dcterms:created><dc:creator>" + "Axel Richter from scratch" + "</dc:creator></cp:coreProperties>";

    static String _rels_rels_xml  = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\"><Relationship Id=\"rId1\" Target=\"xl/workbook.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument\"/><Relationship Id=\"rId2\" Target=\"docProps/app.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties\"/><Relationship Id=\"rId3\" Target=\"docProps/core.xml\" Type=\"http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties\"/></Relationships>";

    static String xl_rels_workbook_xml_rels_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\"><Relationship Id=\"rId1\" Target=\"sharedStrings.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings\"/><Relationship Id=\"rId2\" Target=\"styles.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles\"/><Relationship Id=\"rId3\" Target=\"worksheets/sheet1.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet\"/></Relationships>";

    static String xl_sharedstrings_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><sst count=\"0\" uniqueCount=\"0\" xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"/>";

    static String xl_styles_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><numFmts count=\"0\"/><fonts count=\"1\"><font><sz val=\"11.0\"/><color indexed=\"8\"/><name val=\"Calibri\"/><family val=\"2\"/><scheme val=\"minor\"/></font></fonts><fills count=\"2\"><fill><patternFill patternType=\"none\"/></fill><fill><patternFill patternType=\"darkGray\"/></fill></fills><borders count=\"1\"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\"/></cellStyleXfs><cellXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/></cellXfs></styleSheet>";

    static String xl_workbook_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\"><workbookPr date1904=\"false\"/><bookViews><workbookView activeTab=\"0\"/></bookViews><sheets><sheet name=\"" + "Sheet1" + "\" r:id=\"rId3\" sheetId=\"1\"/></sheets></workbook>";

    static String xl_worksheets_sheet1_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><dimension ref=\"A1\"/><sheetViews><sheetView workbookViewId=\"0\" tabSelected=\"true\"/></sheetViews><sheetFormatPr defaultRowHeight=\"15.0\"/><sheetData/><pageMargins bottom=\"0.75\" footer=\"0.3\" header=\"0.3\" left=\"0.7\" right=\"0.7\" top=\"0.75\"/></worksheet>";


    ArrayList<ArrayList<Object>> sheetDat = new ArrayList<>();

    public CreatePlainXLSX() {
        sheetDat.add(0, new ArrayList<>());
    }

    public void addCell(int rowNum, int cellNum, Object value) {

        if (sheetDat.size() <= rowNum){
            for (int a = sheetDat.size(); a <= rowNum; a++) {
                sheetDat.add(new ArrayList<>());
            }
        }

        if (sheetDat.get(rowNum).size() <= cellNum) {
            for (int a = sheetDat.get(rowNum).size(); a <= cellNum; a++) {
                sheetDat.get(rowNum).add("");
            }
        }
        sheetDat.get(rowNum).set(cellNum, value);
    }

    private static int toNumber(String name) {
        int number = 0;
        for (int i = 0; i < name.length(); i++) {
            number = number * 26 + (name.charAt(i) - ('A' - 1));
        }
        return number;
    }

    private static String toName(int number) {
        StringBuilder sb = new StringBuilder();
        while (number-- > 0) {
            sb.append((char)('A' + (number % 26)));
            number /= 26;
        }
        return sb.reverse().toString();
    }

    private String incrementColumnR(String a){
        return toName(toNumber(a) + 1);
    }

    public void exportToFile(FileOutputStream fileout) throws Exception {

        // result goes into a ByteArrayOutputStream
        ByteArrayOutputStream resultout = new ByteArrayOutputStream();

        // needed objects
        ZipEntry zipentry;
        byte[] data;

        // create ZipOutputStream
        ZipOutputStream zipout = new ZipOutputStream(resultout);

        // create the static parts of the XLSX ZIP file:

        zipentry = new ZipEntry("[Content_Types].xml");
        zipout.putNextEntry(zipentry);
        data = content_types_xml.getBytes();
        zipout.write(data, 0, data.length);
        zipout.closeEntry();

        zipentry = new ZipEntry("docProps/app.xml");
        zipout.putNextEntry(zipentry);
        data = docProps_app_xml.getBytes();
        zipout.write(data, 0, data.length);
        zipout.closeEntry();

        zipentry = new ZipEntry("docProps/core.xml");
        zipout.putNextEntry(zipentry);
        data = docProps_core_xml.getBytes();
        zipout.write(data, 0, data.length);
        zipout.closeEntry();

        zipentry = new ZipEntry("_rels/.rels");
        zipout.putNextEntry(zipentry);
        data = _rels_rels_xml.getBytes();
        zipout.write(data, 0, data.length);
        zipout.closeEntry();

        zipentry = new ZipEntry("xl/_rels/workbook.xml.rels");
        zipout.putNextEntry(zipentry);
        data = xl_rels_workbook_xml_rels_xml.getBytes();
        zipout.write(data, 0, data.length);
        zipout.closeEntry();

        zipentry = new ZipEntry("xl/sharedStrings.xml");
        zipout.putNextEntry(zipentry);
        data = xl_sharedstrings_xml.getBytes();
        zipout.write(data, 0, data.length);
        zipout.closeEntry();

        zipentry = new ZipEntry("xl/styles.xml");
        zipout.putNextEntry(zipentry);
        data = xl_styles_xml.getBytes();
        zipout.write(data, 0, data.length);
        zipout.closeEntry();

        zipentry = new ZipEntry("xl/workbook.xml");
        zipout.putNextEntry(zipentry);
        data = xl_workbook_xml.getBytes();
        zipout.write(data, 0, data.length);
        zipout.closeEntry();

        // preparing the sheet data:

        String sheetdata = "<sheetData>";
        int r = 0;
        String c = toName(0);
        ArrayList<ArrayList<Object>> sheet = sheetDat;
        for (ArrayList<Object> rowData : sheet) {
            sheetdata += "<row r=\"" + ++r + "\">";
            c = toName(0);
            for (Object cellData : rowData) {
                c = incrementColumnR(c);
                sheetdata += "<c r=\"" + c + r + "\"";
                if (cellData instanceof String && ((String) cellData).startsWith("=")) {
                    sheetdata += "><f>" + ((String) cellData).replace("=", "") + "</f></c>";
                } else if (cellData instanceof String) {
                    sheetdata += " t=\"inlineStr\"><is><t>" + cellData + "</t></is></c>";
                } else if (cellData instanceof Double || cellData instanceof Integer) {
                    sheetdata += "><v>" + cellData + "</v></c>";
                }
            }
            sheetdata += "</row>";
        }
        sheetdata += "</sheetData>";

        // get the static sheet xml into a buffer for further processing
        StringBuffer xl_worksheets_sheet1_xml_buffer = new StringBuffer(xl_worksheets_sheet1_xml);

        // get position of the <dimension ref=\"A1\"/> in the static xl_worksheets_sheet1_xml
        int dimensionstart = xl_worksheets_sheet1_xml_buffer.indexOf("<dimension ref=\"A1\"/>");
        // replace the <dimension ref=\"A1\"/> with the new dimension
        xl_worksheets_sheet1_xml_buffer = xl_worksheets_sheet1_xml_buffer.replace(
                dimensionstart,
                dimensionstart + "<dimension ref=\"A1\"/>".length(),
                "<dimension ref=\"A1:" + c + r + "\"/>");

        // get position of the <sheetData/> in the static xl_worksheets_sheet1_xml
        int sheetdatastart = xl_worksheets_sheet1_xml_buffer.indexOf("<sheetData/>");
        // replace the <sheetData/> with the prepared sheet date string
        xl_worksheets_sheet1_xml_buffer = xl_worksheets_sheet1_xml_buffer.replace(
                sheetdatastart,
                sheetdatastart + "<sheetData/>".length(),
                sheetdata);

        // create the xl/worksheets/sheet1.xml
        zipentry = new ZipEntry("xl/worksheets/sheet1.xml");
        zipout.putNextEntry(zipentry);
        data = xl_worksheets_sheet1_xml_buffer.toString().getBytes();
        zipout.write(data, 0, data.length);
        zipout.closeEntry();

        zipout.finish();

        // now ByteArrayOutputStream resultout contains the XLSX file data

        // writing this data into a file
        if(fileout != null) {
            resultout.writeTo(fileout);
            resultout.close();
        }
    }
}

###

Use Apache POI and enable multi dex by simply adding compile "com.android.support:multidex:1.0.1" to your dependencies in build.gradle. You also need to set multiDexEnabled true. That should get rid of the 65k method limitation.

###

If you don’t need to read xlsx file from any sources i recommande you to use CSV excel is perfectly able to convert that type to any of its std output.

Even if you need formula, cell formatting, there are to way to did it use on of the lib you cite or using your own xml parser as said by many people before me, excel format is just a zip with xml inside. Those xml are describe here :

https://msdn.microsoft.com/en-us/library/dd979921(v=office.12).aspx

As you will discover excel is very complexe format and simple retrocompatibility is not their priority . So making your own restricted parser with XPath or JAXB will be an heavy work but not impossible.

But I don’t understand why you want restrict number of methods ? if its about making some embedded sofware, in my opinion, you shouldn’t use .xlsx, this is a to complicated and heavy file to just save grids …

###

I’m expanding on Axel Richter’s answer
My code below will add features for you to add columns and values dynamically.

import android.os.Build;

import androidx.annotation.RequiresApi;

import java.io.ByteArrayOutputStream;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.zip.*;

@SuppressWarnings("WeakerAccess")
@RequiresApi(api = Build.VERSION_CODES.O)
public class ExcelSpreadSheet {

//some static parts of the XLSX file:

String content_types_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\"><Default ContentType=\"application/vnd.openxmlformats-package.relationships+xml\" Extension=\"rels\"/><Default ContentType=\"application/xml\" Extension=\"xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.extended-properties+xml\" PartName=\"/docProps/app.xml\"/><Override ContentType=\"application/vnd.openxmlformats-package.core-properties+xml\" PartName=\"/docProps/core.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml\" PartName=\"/xl/sharedStrings.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml\" PartName=\"/xl/styles.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\" PartName=\"/xl/workbook.xml\"/><Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml\" PartName=\"/xl/worksheets/sheet1.xml\"/></Types>";

String docProps_app_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\"><Application>" + "Created Low level From Scratch" + "</Application></Properties>";

String docProps_core_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"><dcterms:created xsi:type=\"dcterms:W3CDTF\">" + java.time.Instant.now().truncatedTo(java.time.temporal.ChronoUnit.SECONDS).toString() + "</dcterms:created><dc:creator>" + "Axel Richter from scratch" + "</dc:creator></cp:coreProperties>";

String _rels_rels_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\"><Relationship Id=\"rId1\" Target=\"xl/workbook.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument\"/><Relationship Id=\"rId2\" Target=\"docProps/app.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties\"/><Relationship Id=\"rId3\" Target=\"docProps/core.xml\" Type=\"http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties\"/></Relationships>";

String xl_rels_workbook_xml_rels_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\"><Relationship Id=\"rId1\" Target=\"sharedStrings.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings\"/><Relationship Id=\"rId2\" Target=\"styles.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles\"/><Relationship Id=\"rId3\" Target=\"worksheets/sheet1.xml\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet\"/></Relationships>";

String xl_sharedstrings_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><sst count=\"0\" uniqueCount=\"0\" xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"/>";

String xl_styles_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><numFmts count=\"0\"/><fonts count=\"1\"><font><sz val=\"11.0\"/><color indexed=\"8\"/><name val=\"Calibri\"/><family val=\"2\"/><scheme val=\"minor\"/></font></fonts><fills count=\"2\"><fill><patternFill patternType=\"none\"/></fill><fill><patternFill patternType=\"darkGray\"/></fill></fills><borders count=\"1\"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\"/></cellStyleXfs><cellXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/></cellXfs></styleSheet>";

String xl_workbook_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\"><workbookPr date1904=\"false\"/><bookViews><workbookView activeTab=\"0\"/></bookViews><sheets><sheet name=\"" + "Sheet1" + "\" r:id=\"rId3\" sheetId=\"1\"/></sheets></workbook>";

String xl_worksheets_sheet1_xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><dimension ref=\"A1\"/><sheetViews><sheetView workbookViewId=\"0\" tabSelected=\"true\"/></sheetViews><sheetFormatPr defaultRowHeight=\"15.0\"/><sheetData/><pageMargins bottom=\"0.75\" footer=\"0.3\" header=\"0.3\" left=\"0.7\" right=\"0.7\" top=\"0.75\"/></worksheet>";

ArrayList<ArrayList<Object>> sheetDat = new ArrayList<>();
ArrayList<Object> sheetKeys = new ArrayList<>();

ExcelSpreadSheet() {
    sheetDat.add(0, new ArrayList<>());
}

int addFirstColumnKey(String key) {
    if (!sheetKeys.contains(key)) {
        if (sheetKeys.size() > 26) return -1;
        sheetKeys.add(key);
    }
    return sheetKeys.indexOf(key);
}

void addValueToRow(int rowID, String keyName, Object value) {
    addFirstColumnKey("ID");
    int columnEdit = addFirstColumnKey(keyName);
    if (columnEdit == -1) return;//ERROR
    int editingRow = -1;
    for (int i = 0; i < sheetDat.size(); i++) {
        if (sheetDat.get(i).get(0).equals(rowID)) {
            editingRow = i;
        }
    }
    if (editingRow == -1) {
        sheetDat.add(new ArrayList<>());
        editingRow = sheetDat.size() - 1;
    }
    if (sheetDat.get(editingRow).size() < sheetKeys.size()) {
        for (int a = 0; a < sheetKeys.size(); a++) {
            sheetDat.get(editingRow).add(0);
        }
    }
    sheetDat.get(editingRow).set(columnEdit, value);
    sheetDat.get(editingRow).set(0, rowID);
}

private class Sort implements Comparator<ArrayList<Object>> {
    int sortColumn;

    Sort(int columnToSortBy) {
        sortColumn = columnToSortBy;
    }

    public int compare(ArrayList<Object> a, ArrayList<Object> b) {
        if (a.size() < sortColumn || a.get(sortColumn) == null) {
            return 0;
        } else if (a.get(sortColumn) instanceof String) {
            return ((String) a.get(sortColumn)).compareTo(((String) b.get(sortColumn)));
        } else if (a.get(sortColumn) instanceof Integer) {
            return ((Integer) a.get(sortColumn)).compareTo(((Integer) b.get(sortColumn)));
        } else if (a.get(sortColumn) instanceof Long) {
            return ((Long) a.get(sortColumn)).compareTo(((Long) b.get(sortColumn)));
        } else if (a.get(sortColumn) instanceof Double) {
            return ((Double) a.get(sortColumn)).compareTo(((Double) b.get(sortColumn)));
        }
        return 0;
    }
}

void exportToFile() throws Exception {

    // result goes into a ByteArrayOutputStream
    ByteArrayOutputStream resultout = new ByteArrayOutputStream();

    // needed objects
    ZipEntry zipentry;
    byte[] data;

    // create ZipOutputStream
    ZipOutputStream zipout = new ZipOutputStream(resultout);

    // create the static parts of the XLSX ZIP file:

    zipentry = new ZipEntry("[Content_Types].xml");
    zipout.putNextEntry(zipentry);
    data = content_types_xml.getBytes();
    zipout.write(data, 0, data.length);
    zipout.closeEntry();

    zipentry = new ZipEntry("docProps/app.xml");
    zipout.putNextEntry(zipentry);
    data = docProps_app_xml.getBytes();
    zipout.write(data, 0, data.length);
    zipout.closeEntry();

    zipentry = new ZipEntry("docProps/core.xml");
    zipout.putNextEntry(zipentry);
    data = docProps_core_xml.getBytes();
    zipout.write(data, 0, data.length);
    zipout.closeEntry();

    zipentry = new ZipEntry("_rels/.rels");
    zipout.putNextEntry(zipentry);
    data = _rels_rels_xml.getBytes();
    zipout.write(data, 0, data.length);
    zipout.closeEntry();

    zipentry = new ZipEntry("xl/_rels/workbook.xml.rels");
    zipout.putNextEntry(zipentry);
    data = xl_rels_workbook_xml_rels_xml.getBytes();
    zipout.write(data, 0, data.length);
    zipout.closeEntry();

    zipentry = new ZipEntry("xl/sharedStrings.xml");
    zipout.putNextEntry(zipentry);
    data = xl_sharedstrings_xml.getBytes();
    zipout.write(data, 0, data.length);
    zipout.closeEntry();

    zipentry = new ZipEntry("xl/styles.xml");
    zipout.putNextEntry(zipentry);
    data = xl_styles_xml.getBytes();
    zipout.write(data, 0, data.length);
    zipout.closeEntry();

    zipentry = new ZipEntry("xl/workbook.xml");
    zipout.putNextEntry(zipentry);
    data = xl_workbook_xml.getBytes();
    zipout.write(data, 0, data.length);
    zipout.closeEntry();

    // preparing the sheet data:

    String sheetdata = "<sheetData>";
    int r = 0;
    char c = 'A';
    --c;
    ArrayList<ArrayList<Object>> sheet = sheetDat;
    sheet.add(0, sheetKeys);
    for (ArrayList<Object> rowData : sheet) {
        sheetdata += "<row r=\"" + ++r + "\">";
        c = 'A';
        --c;
        for (Object cellData : rowData) {
            sheetdata += "<c r=\"" + Character.toString(++c) + r + "\"";
            if (cellData instanceof String && ((String) cellData).startsWith("=")) {
                sheetdata += "><f>" + ((String) cellData).replace("=", "") + "</f></c>";
            } else if (cellData instanceof String) {
                sheetdata += " t=\"inlineStr\"><is><t>" + cellData + "</t></is></c>";
            } else if (cellData instanceof Double) {
                sheetdata += "><v>" + cellData + "</v></c>";
            }
        }
        sheetdata += "</row>";
    }
    sheetdata += "</sheetData>";

    // get the static sheet xml into a buffer for further processing
    StringBuffer xl_worksheets_sheet1_xml_buffer = new StringBuffer(xl_worksheets_sheet1_xml);

    // get position of the <dimension ref=\"A1\"/> in the static xl_worksheets_sheet1_xml
    int dimensionstart = xl_worksheets_sheet1_xml_buffer.indexOf("<dimension ref=\"A1\"/>");
    // replace the <dimension ref=\"A1\"/> with the new dimension
    xl_worksheets_sheet1_xml_buffer = xl_worksheets_sheet1_xml_buffer.replace(
            dimensionstart,
            dimensionstart + "<dimension ref=\"A1\"/>".length(),
            "<dimension ref=\"A1:" + c + r + "\"/>");

    // get position of the <sheetData/> in the static xl_worksheets_sheet1_xml
    int sheetdatastart = xl_worksheets_sheet1_xml_buffer.indexOf("<sheetData/>");
    // replace the <sheetData/> with the prepared sheet date string
    xl_worksheets_sheet1_xml_buffer = xl_worksheets_sheet1_xml_buffer.replace(
            sheetdatastart,
            sheetdatastart + "<sheetData/>".length(),
            sheetdata);

    // create the xl/worksheets/sheet1.xml
    zipentry = new ZipEntry("xl/worksheets/sheet1.xml");
    zipout.putNextEntry(zipentry);
    data = xl_worksheets_sheet1_xml_buffer.toString().getBytes();
    zipout.write(data, 0, data.length);
    zipout.closeEntry();

    zipout.finish();

    // now ByteArrayOutputStream resultout contains the XLSX file data

    // writing this data into a file
    try (java.io.FileOutputStream fileout = new java.io.FileOutputStream("working.xlsx")) {
        resultout.writeTo(fileout);
        resultout.close();
    }

}
}

###

xlsx is a zip file. So we can extract zip and get xml files inside and then we can parse it ourself to get sheet data.

This should work for both For both xls and xlsx.

Leave a Reply

Your email address will not be published. Required fields are marked *