Class to read template excel sheet, modify and encrypt it and return encypted excel sheet as byte array.

ExcelEncryption.java (text/plain), 11.03 KB, created by Jacob on 2018-01-17 15:59:50 UTC

(hide)

Description: Class to read template excel sheet, modify and encrypt it and return encypted excel sheet as byte array.

Filename:

MIME Type:

Creator: Jacob

Created: 2018-01-17 15:59:50 UTC

Size: 11.03 KB

patch

obsolete

>package excelTesting; >import java.io.ByteArrayOutputStream; >import java.io.File; >import java.io.FileNotFoundException; >import java.io.FileOutputStream; >import java.io.IOException; >import java.io.InputStream; >import java.io.OutputStream; >import java.text.DecimalFormat; >import java.util.Calendar; >import java.util.Date; > >import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey; >import org.apache.poi.openxml4j.opc.OPCPackage; >import org.apache.poi.openxml4j.opc.PackageAccess; >import org.apache.poi.poifs.crypt.EncryptionInfo; >import org.apache.poi.poifs.crypt.EncryptionMode; >import org.apache.poi.poifs.crypt.Encryptor; >import org.apache.poi.poifs.filesystem.POIFSFileSystem; >import org.apache.poi.ss.usermodel.BorderStyle; >import org.apache.poi.ss.usermodel.Cell; >import org.apache.poi.ss.usermodel.DataFormat; >import org.apache.poi.ss.usermodel.Header; >import org.apache.poi.ss.usermodel.HorizontalAlignment; >import org.apache.poi.ss.usermodel.Sheet; >import org.apache.poi.ss.usermodel.VerticalAlignment; >import org.apache.poi.util.LittleEndianInput; >import org.apache.poi.xssf.usermodel.XSSFCell; >import org.apache.poi.xssf.usermodel.XSSFCellStyle; >import org.apache.poi.xssf.usermodel.XSSFFont; >import org.apache.poi.xssf.usermodel.XSSFRow; >import org.apache.poi.xssf.usermodel.XSSFSheet; >import org.apache.poi.xssf.usermodel.XSSFWorkbook; > >import com.ibx.consumer.common.javautils.DateUtils; >import com.ibx.consumer.common.javautils.FileUtils; > > >public class ExcelEncryption { > > > XSSFWorkbook workbook = null; > > private static XSSFFont claimBoldFont = null; > private static XSSFFont smallBoltFont = null; > > private static XSSFFont boldFont = null; > private static XSSFFont patientboldFont = null; > private static XSSFFont smallFont = null; > private static ReportColumn[] claimColumns = null; > private static ReportColumn[] patientColumns = null; > private static ReportColumn[] patientTotalColumns = null; > private static ReportColumn[] patientOnlyColumns = null; > private static ReportColumn[] patientOver18 = null; > private static String patientOver18Msg1 = "This member on your account is over 18. " > + "To get their tax year report information, they must log in to their own account "; > private static String patientOver18Msg2 = "to generate a tax year report."; > > public static int ELIG_DEPENDENT_AGE = 18; > > > > private void initCERXlsx() { > boldFont = workbook.createFont(); > boldFont.setBold(true); > > claimBoldFont = workbook.createFont(); > claimBoldFont.setFontHeightInPoints((short) 10); > claimBoldFont.setBold(true); > > smallBoltFont = workbook.createFont(); > smallBoltFont.setFontHeightInPoints((short) 10); > smallBoltFont.setBold(true); > > smallFont = workbook.createFont(); > smallFont.setFontHeightInPoints((short) 10); > > claimColumns = new ReportColumn[] { > new ReportColumn("patientIdentifierKey", "Member Name", FormatType.TEXT,17), > new ReportColumn("serviceDate", "Service Date", FormatType.DATE,10), > new ReportColumn("claimNumber", "Claim Number", FormatType.TEXT,12), > new ReportColumn("claimType", "Type of Claim", FormatType.TEXT,11), > new ReportColumn("provider", "Provider", FormatType.TEXT,24), > new ReportColumn("claimAmt", "Total Amount of Claim", FormatType.MONEY,13), > new ReportColumn("patientResponsibility", "Member Cost", FormatType.MONEY,10) }; > } > > private void writeCell(XSSFRow row, int col, Object value, > FormatType formatType, Short bgColor, XSSFFont font, > XSSFCellStyle style) throws Exception { > System.out.println("starting write Cell "); > try { > XSSFCell cell = row.createCell(col); > > if (value == null) { > return; > } > if (style == null) { > style = workbook.createCellStyle(); > style.setVerticalAlignment(VerticalAlignment.CENTER); > } else { > style.setVerticalAlignment(VerticalAlignment.CENTER); > } > > if (font != null) { > style.setFont(font); > } else { > style.setFont(smallFont); > } > DataFormat format = workbook.createDataFormat(); > switch (formatType) { > case TEXT: > cell.setCellValue(value.toString().replaceAll("Name :", "Name:")); > style.setAlignment(HorizontalAlignment.LEFT); > style.setWrapText(true); > cell.setCellStyle(style); > break; > case INTEGER: > cell.setCellValue(((Number) value).intValue()); > style.setDataFormat(format.getFormat("#,##0")); > style.setAlignment(HorizontalAlignment.LEFT); > cell.setCellStyle(style); > break; > case NUMBER: > cell.setCellValue(value.toString()); > style.setDataFormat(format.getFormat("###0")); > style.setAlignment(HorizontalAlignment.LEFT); > cell.setCellStyle(style); > cell.setCellType(Cell.CELL_TYPE_NUMERIC); > break; > case FLOAT: > cell.setCellValue(((Number) value).doubleValue()); > style.setDataFormat(format.getFormat("#,##0.00")); > style.setAlignment(HorizontalAlignment.LEFT); > cell.setCellStyle(style); > break; > case DATE: > cell.setCellValue((Date) value); > style.setDataFormat(format.getFormat("mm/dd/yyyy")); > style.setAlignment(HorizontalAlignment.LEFT); > cell.setCellStyle(style); > break; > case MONEY: > /* > * cell.setCellValue(((Number) value).doubleValue()); > * style.setDataFormat(format.getFormat("$#,##0.00;$#,##0.00")); > * style.setAlignment(HorizontalAlignment.LEFT); > * cell.setCellStyle(style); > */ > cell.setCellType(Cell.CELL_TYPE_STRING); > style.setAlignment(HorizontalAlignment.RIGHT); > cell.setCellStyle(style); > DecimalFormat df = new DecimalFormat("#,###,###.00"); > cell.setCellValue("$" > + df.format(((Number) value).doubleValue())); > break; > case PERCENTAGE: > cell.setCellValue(((Number) value).doubleValue()); > style.setDataFormat(format.getFormat("0.00%")); > cell.setCellStyle(style); > default: > break; > } > if (bgColor != null) { > > } > System.out.println("end write Cell "); > } catch (Exception e) { > System.out.println(e.getMessage() + " (ECM3212)"); > e.fillInStackTrace(); > throw new Exception(e); > } > } > > private void writeTitleRow(XSSFRow row) throws Exception{ > int numCols = claimColumns.length; > > try{ > for (int x = 0; x < numCols; x++) { > XSSFCellStyle style = null; > style = workbook.createCellStyle(); > style.setBorderTop(BorderStyle.MEDIUM); > style.setBorderBottom(BorderStyle.MEDIUM); > > > //style.setBorderBottom(XSSFCellStyle.BORDER_DOTTED); > style.setBorderLeft(BorderStyle.MEDIUM); > style.setBorderRight(BorderStyle.MEDIUM); > > style.setAlignment(HorizontalAlignment.RIGHT); > //style.setWrapText(false); > //writeCell(row, x, claimColumns[x].getHeader(), > // FormatType.TEXT, null, claimBoldFont ,style); > > writeCell(row, x, claimColumns[x].getHeader(), > FormatType.TEXT, null, claimBoldFont ,style); > } > }catch (Exception e) { > System.out.println(e.getMessage() + " (ECM3197)"); > e.fillInStackTrace(); > throw new Exception(e); > } > > } > > public byte[] doReport(String brand) throws Exception { > byte[] xlsBytes = null; > try { > String fileName = "Headertemplate.xlsx"; > InputStream file = FileUtils.readDataFile(brand, fileName); > String dateRangeLabel = "Date Range of Report: "; > String dateRangeVal = "2017"; > > OPCPackage opc = OPCPackage.open(file); > workbook = new XSSFWorkbook(opc); > initCERXlsx(); > XSSFSheet my_worksheet = workbook.getSheetAt(0); > Header header = my_worksheet.getHeader(); > String rightHeader = header.getRight() + DateUtils.currentDate("MM/dd/yyyy"); > header.setRight(rightHeader); > > my_worksheet.setMargin(Sheet.LeftMargin,0.1); > my_worksheet.setMargin(Sheet.RightMargin,0.1); > >// addRowsToSheet(taxYearBean.getPatientList(), my_worksheet, dateRangeVal); > XSSFRow row; > int currentRow = 0; > row = my_worksheet.createRow(currentRow++); > writeTitleRow(row); > > Calendar cal = Calendar.getInstance(); > cal.setTime(new Date()); > long timinmillis = cal.getTimeInMillis(); > > > my_worksheet.enableLocking(); > my_worksheet.lockDeleteColumns(true); > my_worksheet.lockDeleteRows(true); > my_worksheet.lockFormatCells(true); > my_worksheet.lockFormatColumns(true); > my_worksheet.lockFormatRows(true); > my_worksheet.lockInsertColumns(true); > my_worksheet.lockInsertRows(true); > > //try { > //FileOutputStream fileOut = new FileOutputStream(CacheEnv.APP_HOME + "/TYR/" + userid + "excel.xlsx"); >// FileOutputStream fileOut = new FileOutputStream("c:/Temp/" + userid + "excel.xlsx"); > //InputStream inStream; > //inStream.read(workbook); > > //workbook.write(fileOut); > //fileOut.close(); >// } catch (IOException e) { >// e.printStackTrace(); >// } // Add password protection and encrypt the file > POIFSFileSystem fs = new POIFSFileSystem(); > EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile); > > Encryptor enc = info.getEncryptor(); > enc.confirmPassword("Password1"); > > //OPCPackage opc = OPCPackage.open(new File(CacheEnv.APP_HOME + "/TYR/" + userid + "excel.xlsx"), >// OPCPackage opc = OPCPackage.open(new File("c:/Temp/" + userid + "excel.xlsx"), >// PackageAccess.READ_WRITE); > OutputStream os = enc.getDataStream(fs); > opc.save(os); > opc.close(); > > > //Biff8EncryptionKey.setCurrentUserPassword("Password1"); > > ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); > > //FileOutputStream fos = new FileOutputStream("c:/Temp/excel.xlsx"); > workbook.write(outputStream); > fs.writeFilesystem(outputStream); > //fs.writeFilesystem(outputStream); > //workbook.write(outputStream); > //outputStream.flush(); > outputStream.close(); > > //fos.close(); > > System.out.println("closing the template file"); > file.close(); > // my_worksheet.protectSheet("blue"); > xlsBytes = outputStream.toByteArray(); > >// //File f = new File(CacheEnv.APP_HOME + "/TYR/" + userid + "excel.xlsx"); >// File f = new File("c:/Temp/" + userid + "excel.xlsx"); >// if (f.delete()) { >// System.out.println("File Deleted!"); >// } else { >// System.out.println("Failed to delete..."); >// } >// > } catch (FileNotFoundException e1) { > System.out.println(e1.getMessage() + " (ECM3211)"); > e1.printStackTrace(); > throw new Exception(e1); > } catch (IOException ioe) { > System.out.println(ioe.getMessage() + " (ECM3211)"); > ioe.fillInStackTrace(); > throw new Exception(ioe); > } catch (Exception e) { > System.out.println(e.getMessage() + " (ECM3211)"); > e.fillInStackTrace(); > throw new Exception(e); > } > return xlsBytes; > } > > > > public static void main(String[] args) throws Exception { > ExcelEncryption ee = new ExcelEncryption(); > byte[] typeBytes = ee.doReport("IBC"); > EmailMediator em = new EmailMediator(); > em.emailTaxYearReportXls("consumeribx", typeBytes, > "jacob.bigelow@ibx.com", EmailMediator.STATUS_CLOSE, > username, password); > } >} > > View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]