Java tutorial
package com.assentisk.util; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.sql.ResultSet; import java.sql.SQLException; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Set; import javax.servlet.http.HttpServletRequest; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.apache.poi.hssf.usermodel.DVConstraint; import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.util.CellRangeAddressList; import com.assentisk.bean.BusinessProcessesBean; import com.assentisk.bean.CategoriesBean; import com.assentisk.bean.DepartmentBean; import com.assentisk.bean.LegalEntitiesBean; import com.assentisk.bean.LocationsBean; import org.apache.poi.hssf.usermodel.DVConstraint; import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.util.CellRangeAddressList; import com.assentisk.bean.ApplicationsBean; import com.assentisk.bean.BusinessProcessesBean; import com.assentisk.bean.CategoriesBean; import com.assentisk.bean.CountryBean; import com.assentisk.bean.DepartmentBean; import com.assentisk.bean.LegalEntitiesBean; import com.assentisk.bean.LocationsBean; import com.assentisk.dao.RegulationDao; import com.assentisk.dao.RegulationDaoImpl; import com.sun.faces.config.beans.ApplicationBean; public class FilesService extends JdbcDaoSupport { protected RegulationDao regulationDao; public RegulationDao getRegulationDao() { return regulationDao; } public void setRegulationDao(RegulationDao regulationDao) { this.regulationDao = regulationDao; } String query = null; public InputStream getImage() { return (InputStream) getJdbcTemplate().query("select CompanyLogo from assentisk_company where CompanyID=11", new ResultSetExtractor() { public InputStream extractData(ResultSet rs) throws SQLException, DataAccessException { InputStream input = rs.getBinaryStream("CompanyLogo"); return input; } }); } public void save(final Files file, String sql) { query = sql; try { synchronized (this) { getJdbcTemplate().update(new PreparedStatementCreator() { public java.sql.PreparedStatement createPreparedStatement(java.sql.Connection con) throws SQLException { java.sql.PreparedStatement statement = con.prepareStatement(query); statement.setBytes(1, file.getFile()); return statement; } }); } } catch (NullPointerException ex) { ex.printStackTrace(); } } public void saveCompanyLogo(final File file, String sql) { query = sql; try { synchronized (this) { getJdbcTemplate().update(new PreparedStatementCreator() { public java.sql.PreparedStatement createPreparedStatement(java.sql.Connection con) throws SQLException { java.sql.PreparedStatement statement = con.prepareStatement(query); statement.setString(1, file.getAbsolutePath()); return statement; } }); } } catch (NullPointerException ex) { ex.printStackTrace(); } } public void generatePositionProfileXls(HttpServletRequest req, String Doc_name, List catList) throws SQLException, IOException { Integer key = 1; try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(Doc_name); Map<String, Object[]> data = new LinkedHashMap<String, Object[]>(); data.put(key.toString(), new Object[] { "Control ID", "Risk", "Control Objective", "Control Description", "Test Steps", "Busines Process Name", "Control Category Name" }); if (catList != null) { for (int i = 0; i < catList.size(); i++) { Map map = (Map) catList.get(i); short colIndex = 0; data.put((++key).toString(), new Object[] { map.get("Control_ID"), map.get("Risk"), map.get("Control_Objective"), map.get("Control_Description"), map.get("Test_Steps"), map.get("businessProcess"), map.get("controlcategory") }); Set<String> keyset = data.keySet(); int rownum = 0; sheet.setColumnHidden((short) 14, true); for (String key2 : keyset) { HSSFRow row = sheet.createRow(rownum++); Object[] objArr = data.get(key2); short cellnum = 0; for (Object obj : objArr) { if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof Integer) row.createCell(cellnum++).setCellValue((Integer) obj); else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((Integer) obj); else if (obj instanceof Integer) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); } } } } FileOutputStream out = new FileOutputStream(new File("" + Doc_name + ".xls")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } } public void generateEmployeDemoXls(HttpServletRequest req, String docName, List<LocationsBean> getLocationList, List<DepartmentBean> getDepartmnentList, List<LegalEntitiesBean> divisionList) throws SQLException, IOException { Integer key = 1; try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(docName); String[] str = new String[getLocationList.size()]; if (getLocationList.size() > 0) { for (int i = 0; i < getLocationList.size(); i++) { Map m = (Map) getLocationList.get(i); str[i] = m.get("Name").toString(); } } String[] strDprtmnt = new String[getDepartmnentList.size()]; if (getDepartmnentList.size() > 0) { for (int i = 0; i < getDepartmnentList.size(); i++) { Map m = (Map) getDepartmnentList.get(i); strDprtmnt[i] = m.get("DeptName").toString(); } } String[] strDivision = new String[divisionList.size()]; if (divisionList.size() > 0) { for (int i = 0; i < divisionList.size(); i++) { Map m = (Map) divisionList.get(i); strDivision[i] = m.get("LegalEntityName").toString(); } } /*for(int i=0;i<locationName.size();i++){ str[i]=locationName.get(i).toString(); }*/ // for(int i=1;i<50;i++){ // CellRangeAddressList addressList = new CellRangeAddressList(i, i, 1, 1); // DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str); // DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation); // // CellRangeAddressList departmnentList = new CellRangeAddressList(i, i, 0, 0); // DVConstraint dvConstraint1 = DVConstraint.createExplicitListConstraint(strDprtmnt); // DataValidation dataValidation1 = new HSSFDataValidation(departmnentList, dvConstraint1); // dataValidation1.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation1); // // // CellRangeAddressList devisionList = new CellRangeAddressList(i, i, 9, 9); // DVConstraint dvConstraint2 = DVConstraint.createExplicitListConstraint(strDivision); // DataValidation dataValidation2 = new HSSFDataValidation(devisionList, dvConstraint2); // dataValidation2.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation2); // // } // Map<String, String[]> data = new LinkedHashMap<String, String[]>(); String[] stringVal = { "EmpName", "Department", "Location", "location Country", "location State", "location City", "location Contact Name", "location Email", "Divisions", "address", "city", "zip", "phone1", "phone2", "Email1", "Email2" }; // data.put(key.toString(), { "DepartmentID","LocationID","EmpName","Email", "address","city","state","country","zip","phone1","phone2","divisions","email2","isLicense","isActive","abc"}); data.put(key.toString(), stringVal); Set<String> keyset = data.keySet(); int rownum = 0; sheet.setColumnHidden((short) 17, true); for (String key2 : keyset) { HSSFRow row = sheet.createRow(rownum++); String[] objArr = data.get(key2); short cellnum = 0; for (String obj : objArr) { if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); } } FileOutputStream out = new FileOutputStream(new File("" + docName + ".xls")); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } } public void generateAssestDemoXls(HttpServletRequest req, String docName, List<CategoriesBean> getCategoryList, List<CountryBean> getCountryList, List<ApplicationsBean> appList2, List<BusinessProcessesBean> getBusinessList) throws SQLException, IOException { Integer key = 1; try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(docName); // HSSFSheet hidden = workbook.createSheet("hidden"); // HSSFSheet hidden1 = workbook.createSheet("hidden1"); // HSSFSheet hidden2 = workbook.createSheet("hidden2"); // HSSFSheet hidden3 = workbook.createSheet("hidden3"); String[] strCategorymnt = new String[getCategoryList.size()]; // if(getCategoryList.size()>0) // { // for (int i = 0; i < getCategoryList.size(); i++) { // Map m = (Map)getCategoryList.get(i); // strCategorymnt[i] = m.get("cname").toString(); // String name = strCategorymnt[i]; // HSSFRow row = hidden3.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // } // } // // String [] strCountry = new String[getCountryList.size()]; // if(getCountryList.size()>0) // { // for (int i = 0; i < getCountryList.size(); i++) { // // Map m = (Map)getCountryList.get(i); // strCountry[i] = m.get("CountryName").toString(); // String name = strCountry[i]; // HSSFRow row = hidden.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // // } // } // String [] strApllication = new String[appList2.size()]; // if(appList2.size()>0) // { // for (int i = 0; i < appList2.size(); i++) { // // Map m = (Map)appList2.get(i); // strApllication[i] = m.get("ApplicationName").toString(); // String name = strApllication[i]; // HSSFRow row = hidden2.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // // } // } // String [] strBusinessList = new String[getBusinessList.size()]; // if(getBusinessList.size()>0) // { // for (int i = 0; i < getBusinessList.size(); i++) { // // Map m = (Map)getBusinessList.get(i); // strBusinessList[i] = m.get("BusinessProcessName").toString(); // String name = strBusinessList[i]; // HSSFRow row = hidden1.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // } // } // Name namedCell = workbook.createName(); // namedCell.setNameName("hidden"); // namedCell.setRefersToFormula("hidden!$A$1:$A$" + strCountry.length); // Name namedCell1 = workbook.createName(); // namedCell1.setNameName("hidden1"); // namedCell1.setRefersToFormula("hidden1!$A$1:$A$" + strBusinessList.length); // // Name namedCell2 = workbook.createName(); // namedCell2.setNameName("hidden2"); // namedCell2.setRefersToFormula("hidden2!$A$1:$A$" + strCategorymnt.length); // // Name namedCell3 = workbook.createName(); // namedCell3.setNameName("hidden3"); // namedCell3.setRefersToFormula("hidden3!$A$1:$A$" + strApllication.length); // for(int i=1;i<50;i++){ // DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden"); // CellRangeAddressList country = new CellRangeAddressList(1, 1, 7, 7); // HSSFDataValidation validation = new HSSFDataValidation(country, constraint); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation); // DVConstraint constraint1 = DVConstraint.createFormulaListConstraint("hidden1"); // CellRangeAddressList businessList = new CellRangeAddressList(1, 1, 3,3); // HSSFDataValidation validation1 = new HSSFDataValidation(businessList, constraint1); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation1); // // DVConstraint constraint3 = DVConstraint.createFormulaListConstraint("hidden2"); // CellRangeAddressList appList = new CellRangeAddressList(1, 1, 2, 2); // HSSFDataValidation validation2 = new HSSFDataValidation(appList, constraint3); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation2); // // // DVConstraint constraint2 = DVConstraint.createFormulaListConstraint("hidden3"); // CellRangeAddressList catList = new CellRangeAddressList(1, 1, 1, 1); // HSSFDataValidation validation3 = new HSSFDataValidation(catList, constraint2); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation3); // CellRangeAddressList businessList = new CellRangeAddressList(i, i, 3, 3); // DVConstraint dvConstraint1 = DVConstraint.createExplicitListConstraint(strBusinessList); // DataValidation dataValidation = new HSSFDataValidation(businessList, dvConstraint1); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation); // CellRangeAddressList appList = new CellRangeAddressList(i, i, 2, 2); // DVConstraint dvConstraint2 = DVConstraint.createExplicitListConstraint(strApllication); // DataValidation dataValidation2 = new HSSFDataValidation(appList, dvConstraint2); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation2); // // CellRangeAddressList catList = new CellRangeAddressList(i, i, 1,1); // DVConstraint dvConstraint3 = DVConstraint.createExplicitListConstraint(strCategorymnt); // DataValidation dataValidation3 = new HSSFDataValidation(catList, dvConstraint3); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation3); // } Map<String, String[]> data = new LinkedHashMap<String, String[]>(); String[] stringVal = { "AssetName", "Category", "Application", "BusinessProcess", "InitialValue", "Quantity", "Location", "Location Country", "Location State", "Location City", "Location Contact Name", "Location Email" }; data.put(key.toString(), stringVal); Set<String> keyset = data.keySet(); int rownum = 0; sheet.setColumnHidden((short) 12, true); for (String key2 : keyset) { HSSFRow row = sheet.createRow(rownum++); String[] objArr = data.get(key2); short cellnum = 0; for (String obj : objArr) { if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } } } FileOutputStream out = new FileOutputStream(new File("" + docName + ".xls")); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } } }