반응형
Excel 파일(.xlsx)을 작성 및 쓰는 방법
자바 개발자들을 위한 라이브러리는 안드로이드에서 작동하지 않는 수많은 방법들을 가지고 있다.
처음에는 OpenCSV와 같은 라이브러리로 작업을 시작했지만 안타깝게도 Excel은 CSV 파일을 여는 데 문제가 있다는 것을 알고 있었습니다.
그 후 다음을 사용하려고 했습니다.
- Apache POI - 확실히 방법이 너무 많습니다.
- JEXcelAPI - 동작하지만 오래된 바이너리 .xls 파일에서만 동작합니다.
- docx4j - 역시 Android에 포함되어 있지 않은 JAXB 기반이기 때문에 jar가 너무 많습니다.
질문입니다만, 어떻게 하면 65k의 메서드를 넘지 않고 .xlsx 형식의 간단한 Excel 파일을 만들 수 있을까요?
첫 번째 답변: 서버 측에서 실시합니다.
이것이 불가능할 경우 JExecelAPI를 사용하십시오.xlsx 파일을 읽는 거의 모든 것이 xls 파일도 읽습니다.
다른 모든 Excel 라이브러리는 너무 커질 것입니다.
또 다른 고려사항은 수동으로 또는 사용 가능한 많은 CSV 라이브러리 중 하나를 사용하여 csv 파일을 쓰는 것입니다.다시 말하지만 Excel 파일을 읽는 대부분의 응용 프로그램은 csv 파일도 읽습니다.
"Excel용 하는 가장 입니까?"라는 것입니다.는는것것것것다다*.xlsx 「 를하고, .java.lang,java.io ★★★★★★★★★★★★★★★★★」java.util.zip.
A *.xlsxfile은 디렉토리 구조 내의 XML 파일 및 기타 파일을 포함하는 ZIP 아카이브에 지나지 않습니다.따라서 ZIP 아카이브의 작성, 읽기, 쓰기와 XML 파일의 작성, 읽기, 쓰기가 가능합니다. 은 ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★」java.util.zipXML 부분에서는 문자열 조작을 사용하고 있습니다.문자열 조작을 통해 XML을 만들고 조작하는 이 방법은 XML을 조작하는 가장 권장되는 방법은 아니지만 XML 라이브러리를 추가할 필요가 없기 때문에 가장 가벼운 방법입니다.
완전한 예:
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();
}
}
}
2018년입니다.Microsoft Graph API를 사용하여 O365에서 Excel 파일을 만듭니다.
Microsoft 는, Angular 및 C# 에 몇개의 예를 게재하고 있습니다.Java는 아니지만 https://developer.microsoft.com/en-us/graph/docs/concepts/excel-write-to-workbook에서 시작하는 것이 좋습니다.
MS Graph Java SDK는 Android와 호환됩니다.
제한 - API를 사용하여 처음부터 Excel 파일을 만드는 쉬운 방법은 없습니다.빈 워크북을 보관하고 매번 복제할 수 있습니다.
액셀 리히터의 답변 업데이트:
- 셀을 동적으로 추가하다
- 무제한 컬럼(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();
}
}
}
Apache POI를 사용하여 추가만으로 멀티덱스 구현compile "com.android.support:multidex:1.0.1"에 있어서의 당신의 의존에 대해서build.gradlemultiDexEnabled도 true로 설정해야 합니다.그러면 65k 메서드의 제한이 없어집니다.
xlsx 파일을 읽을 필요가 없는 경우 CSV Excel을 사용하면 해당 유형을 std 출력으로 완벽하게 변환할 수 있습니다.
공식이나 셀 포맷이 필요한 경우에도 인용한 lib에 사용할 수 있는 방법이 있습니다.또한 많은 분들이 말씀하신 것처럼 자신의 xml 파서를 사용할 수 있습니다.엑셀 포맷은 xml이 안에 있는 zip일 뿐입니다.다음 xml에 대해 설명하겠습니다.
https://msdn.microsoft.com/en-us/library/dd979921(v=office.12).aspx
보시다시피 엑셀은 매우 복잡한 포맷으로 단순한 레트로 호환성이 우선시되지 않습니다.따라서 XPath 또는 JAXB로 제한된 파서를 만드는 것은 힘든 일이지만 불가능하지는 않습니다.
하지만 왜 메서드 수를 제한해야 하는지 이해할 수 없습니다.만약 임베디드 소프트웨어를 만드는 것이라면 .xlsx를 사용하면 안 된다고 생각합니다.이것은 그리드를 저장하는 것만으로 복잡하고 무거운 파일입니다.
Axel Richter의 답변에 대해 자세히 설명하겠습니다. 아래 내 코드는 열과 값을 동적으로 추가할 수 있는 기능을 추가합니다.
import Android.os.구축
import Androidx.information.필요한 Api
java.io 를 Import 합니다.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();
}
}
}
또는 AZ 행까지 수동으로 값을 추가해야 하는 경우 Axel Richter의 답변 중 다음 부분을 변경합니다.
부터
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>";
로.
String sheetdata = "<sheetData>";
int column = 0;
int r = 0;
char c = 'A';
--c;
for (Object[] rowData : sheetData) {
sheetdata += "<row r=\"" + ++r + "\">";
column = 0;
c = 'A';
--c;
for (Object cellData : rowData) {
if (column > 25 || ((Character.isLetter(c) && Character.isUpperCase(c)) || c == '@')) {
if (column > 25) {
if (column == 26) {
c = 'A';
--c;
}
sheetdata += "<c r=\"" + "A" + Character.toString(++c) + r + "\"";
} else if ((Character.isLetter(c) && Character.isUpperCase(c)) || c == '@') {
sheetdata += "<c r=\"" + Character.toString(++c) + r + "\"";
}
column++;
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>";
}
xlsx는 zip 파일입니다.zip을 추출하여 xml 파일을 내부에 가져온 다음 직접 해석하여 시트 데이터를 가져올 수 있습니다.
xls 와 xlsx 의 양쪽 모두에서 동작합니다.
언급URL : https://stackoverflow.com/questions/50738119/how-to-create-and-write-to-excel-file-xlsx
반응형
'code' 카테고리의 다른 글
| Swift에서 첫 번째 ViewController에서 탐색 모음을 숨기려면 어떻게 해야 합니까? (0) | 2023.04.18 |
|---|---|
| Git에서 파일 이동/이름 변경 및 이력 유지가 가능합니까? (0) | 2023.04.18 |
| n일보다 오래된 디렉토리를 삭제하는 셸 스크립트 (0) | 2023.04.18 |
| 매개 변수가 없는 SQL 주입 방지 (0) | 2023.04.18 |
| SQL 쿼리를 사용하여 테이블 이름을 변경하려면 어떻게 해야 합니까? (0) | 2023.04.18 |