com.joeyturczak.jtscanner.utils.CreateExcelSpreadsheet.java Source code

Introduction

Here is the source code for com.joeyturczak.jtscanner.utils.CreateExcelSpreadsheet.java

Source

package com.joeyturczak.jtscanner.utils;

import android.net.Uri;
import android.os.Environment;

import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * Created by joeyturczak on 9/12/15.
 * Copyright (C) 2015 Joey Turczak
 *
 *       Licensed under the Apache License, Version 2.0 (the "License");
 *       you may not use this file except in compliance with the License.
 *       You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *       Unless required by applicable law or agreed to in writing, software
 *       distributed under the License is distributed on an "AS IS" BASIS,
 *       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *       See the License for the specific language governing permissions and
 *       limitations under the License.
 */

public class CreateExcelSpreadsheet {

    private int mRowNum = 2;
    private HSSFSheet mSheet;
    private HSSFWorkbook mWorkbook;
    private String mDate;
    private String mFileName;

    public CreateExcelSpreadsheet(List<List> rowLists) {

        mDate = Utility.getTodayDateString();
        mFileName = Utility.getTodayDateAndTimeString();

        mWorkbook = new HSSFWorkbook();
        mSheet = mWorkbook.createSheet(mDate);
        HSSFCellStyle dateRowStyle = mWorkbook.createCellStyle();
        HSSFCellStyle headerRowStyle = mWorkbook.createCellStyle();
        HSSFCellStyle defaultStyle = mWorkbook.createCellStyle();
        HSSFCellStyle defaultAltStyle = mWorkbook.createCellStyle();
        HSSFFont headerFont = mWorkbook.createFont();
        HSSFFont defaultFont = mWorkbook.createFont();

        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 14);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        defaultFont.setFontName("Arial");
        defaultFont.setFontHeightInPoints((short) 14);

        dateRowStyle.setFont(headerFont);
        headerRowStyle.setFont(headerFont);
        defaultStyle.setFont(defaultFont);
        defaultAltStyle.setFont(defaultFont);

        dateRowStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
        dateRowStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        dateRowStyle.setAlignment(CellStyle.ALIGN_CENTER);

        headerRowStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
        headerRowStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        headerRowStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN);
        headerRowStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
        headerRowStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
        headerRowStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN);
        headerRowStyle.setAlignment(CellStyle.ALIGN_CENTER);

        defaultStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN);
        defaultStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
        defaultStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
        defaultStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN);
        defaultStyle.setAlignment(CellStyle.ALIGN_CENTER);

        defaultAltStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        defaultAltStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        defaultAltStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN);
        defaultAltStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
        defaultAltStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
        defaultAltStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN);
        defaultAltStyle.setAlignment(CellStyle.ALIGN_CENTER);

        try {
            Row dateRow = mSheet.createRow(0);
            dateRow.setHeight((short) 500);
            Cell dateCell = dateRow.createCell(0);
            dateCell.setCellValue(mDate);
            dateCell.setCellStyle(dateRowStyle);
            mSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

            for (int colIndex = 0; colIndex < rowLists.size(); colIndex++) {
                Row row = mSheet.createRow(mRowNum);
                mSheet.setColumnWidth(0, 1200);

                List<String> nextRowList = rowLists.get(colIndex);

                for (int rowIndex = 0; rowIndex < nextRowList.size(); rowIndex++) {
                    Cell cell = row.createCell(rowIndex);
                    cell.setCellValue(nextRowList.get(rowIndex));
                    row.setHeight((short) 400);
                    cell.setCellStyle(defaultStyle);
                    if (colIndex % 2 == 0) {
                        cell.setCellStyle(defaultAltStyle);
                    }
                    if (colIndex == 0) {
                        row.setHeight((short) 500);
                        cell.setCellStyle(headerRowStyle);
                    }
                    if (rowIndex > 0) {
                        mSheet.setColumnWidth(rowIndex, 4400);
                    }
                }

                mRowNum++;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Uri createExcelFile() throws IOException {
        File externalDir = Environment.getExternalStorageDirectory();
        String externalDirPath = externalDir.getPath();

        File scannerDir = new File(externalDirPath + "/JT_Scanner");
        scannerDir.mkdirs();

        String fileName = "JTS_" + mFileName + ".xls";
        String myFilePath = scannerDir + File.separator + fileName;
        File file = new File(myFilePath);

        file.setReadable(true);
        file.createNewFile();

        try {
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            mWorkbook.write(fileOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return Uri.fromFile(file);
    }
}