dwhitzzz

ApchePoiExcel Custom Encoding

Dec 8th, 2019
127
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 3.19 KB | None | 0 0
  1. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  2. import org.apache.poi.ss.usermodel.Cell;
  3. import org.apache.poi.ss.usermodel.Row;
  4. import org.apache.poi.ss.usermodel.Sheet;
  5. import org.apache.poi.ss.usermodel.Workbook;
  6.  
  7. import java.io.ByteArrayOutputStream;
  8. import java.io.FileOutputStream;
  9. import java.io.IOException;
  10. import java.io.OutputStreamWriter;
  11. import java.nio.charset.Charset;
  12. import java.nio.charset.StandardCharsets;
  13.  
  14. /**
  15.  * Add this to your pom for apache poi
  16.  *
  17.  *      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
  18.  *      <dependency>
  19.  *          <groupId>org.apache.poi</groupId>
  20.  *          <artifactId>poi</artifactId>
  21.  *          <version>4.0.0</version>
  22.  *      </dependency>
  23.  *
  24.  *      ONly if you need ".xlsX"
  25.  *      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
  26.  *      <dependency>
  27.  *          <groupId>org.apache.poi</groupId>
  28.  *          <artifactId>poi-ooxml</artifactId>
  29.  *          <version>4.0.0</version>
  30.  *      </dependency>
  31.  *
  32.  */
  33. public class ExcelEncoding {
  34.  
  35.     public static void main(String[] args) throws Exception {
  36.         ApachePoiExcelEncoding excel1Encoded = new ApachePoiExcelEncoding("sheet 1");
  37.         excel1Encoded.writeData();
  38.         excel1Encoded.save("encoded_sheet_iso_8859-15_latin_9");
  39.  
  40.         ApachePoiExcelEncoding excel2Encoded = new ApachePoiExcelEncoding("sheet test 2", StandardCharsets.ISO_8859_1.name());
  41.         excel2Encoded.writeData();
  42.         excel2Encoded.save("encoded_sheet_ISO-8859-1");
  43.     }
  44.  
  45.     /**
  46.      * Write an excel with a custom encoding
  47.      */
  48.     static class ApachePoiExcelEncoding {
  49.         private String encoding = "ISO-8859-15";
  50.         private Workbook wb;
  51.         private Sheet sheet;
  52.  
  53.         ApachePoiExcelEncoding(String sheetName) {
  54.             wb = new HSSFWorkbook();
  55.             sheet = wb.createSheet(sheetName);
  56.         }
  57.  
  58.         public ApachePoiExcelEncoding(String sheetName, String encoding) {
  59.             this.wb = new HSSFWorkbook();
  60.             this.sheet = wb.createSheet(sheetName);
  61.             this.encoding = encoding;
  62.         }
  63.  
  64.         void writeData() {
  65.             // Create a row and put some cells in it. Rows are 0 based.
  66.             Row row = sheet.createRow(0);
  67.             Cell cell = row.createCell((short) 0);
  68.             cell.setCellValue(14654);
  69.  
  70.             //plain string value
  71.             row.createCell(2).setCellValue("This is a string cell");
  72.             //formula
  73.             row.createCell(5).setCellFormula("SUM(A2:B2)");
  74.  
  75.             row.createCell(6).setCellFormula("HYPERLINK(\"http://google.ch\",\"Google CH\")");
  76.         }
  77.  
  78.         void save(final String src) throws IOException {
  79.             String fullPath = getPathWithExtension(src);
  80.             // Invoking HSSFWorkbook.getBytes() does not return all the data
  81.             // necessary to re- construct a complete Excel file
  82.             // We need to use write method with the ByteArrayOutputStream to get at the byte array
  83.             ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
  84.             wb.write(byteOut);
  85.             // get the file in string
  86.             String fileString = new String(byteOut.toByteArray(), Charset.forName(encoding));
  87.             //write the file using stream writer
  88.             try (OutputStreamWriter outputStreamWriter = new OutputStreamWriter(new FileOutputStream(fullPath), encoding)) {
  89.                 outputStreamWriter.write(fileString);
  90.             }
  91.             byteOut.close();
  92.         }
  93.  
  94.         private String getPathWithExtension(String src) {
  95.             return !src.endsWith(".xls") ? src + ".xls" : src;
  96.         }
  97.     }
  98. }
Add Comment
Please, Sign In to add comment