We can create a
dynamic macro enabled excel by java:
We need to create
a excel sheet template with macro on server.
we can copy the
macro in a new excel sheet and write the data in that excel sheet.
We can create a
rule by which we can poluate the list in excel on the basis of
previous selected cell value
For this we need a
jar: POI 3.08
/*
* class MacroEnabledExcelTemplateAction
*/
package com.tcs.reuse.action;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import com.tcs.reuse.MacroEnabledExcelTemplateBus;
// TODO: Auto-generated Javadoc
/**
* The Class MacroEnabledExcelTemplateAction.
*
*/
public class MacroEnabledExcelTemplateAction extends Action {
/** The logger. */
private static Logger logger = Logger
.getLogger(com.tcs.reuse.action.MacroEnabledExcelTemplateAction.class);
static String strHost = null;
/**
* Instantiates a new macro enabled excel template action.
*/
public MacroEnabledExcelTemplateAction() {
super();
logger.debug("****In the constructor of MacroEnabledExcelTemplateAction**");
}
/*
* (non-Javadoc)
*
* @see org.apache.struts.action.Action#execute(org.apache.struts.action.
* ActionMapping, org.apache.struts.action.ActionForm,
* javax.servlet.http.HttpServletRequest,
* javax.servlet.http.HttpServletResponse)
*/
@Override
public ActionForward execute(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
logger.debug("ENTRY MacroEnabledExcelTemplateAction execute");
/*
* Variables declaration
*/
HttpSession session = null;
String userId = "";
String tempPath = "";
int newCellMainSheet = 0;
int newRowMainSheet = 1;
int totalNumberOfRows = 10;
boolean existFolderAtServer = true;
boolean deletedDir = false;
ArrayList<String> errors = null;
ArrayList<String> msg = null;
final boolean hasAccess = true;
InputStream inputFile = null;
FileOutputStream outputFile = null;
InputStream inputFile2 = null;
File file = null;
File existFile = null;
DataValidationHelper dvHelper = null;
DataValidationConstraint dvConstraint = null;
DataValidation validation = null;
CellRangeAddressList addressList = null;
Row mainSheetRow = null;
Cell mainSheetCell = null;
HSSFCellStyle cs = null;
HSSFCellStyle cs1 = null;
HSSFCellStyle headerCS = null;
HSSFCellStyle backgroundColor = null;
HSSFFont font = null;
HSSFFont font1 = null;
HSSFFont headerFont = null;
HSSFWorkbook wb = null;
HSSFWorkbook wb2 = null;
MacroEnabledExcelTemplateBus macroEnabledExcelTemplateBus;
try {
session = request.getSession(true);
macroEnabledExcelTemplateBus = new MacroEnabledExcelTemplateBus();
errors = new ArrayList<String>();
msg = new ArrayList<String>();
/*
* Server folder path
*/
tempPath = "C:\Documents and Settings\Raj\Desktop\Excel work";
// Use the server path where excel sheet templated to be uploaded
logger.debug("Temp Folder path i.e. Server Folder :" + tempPath);
if (userId != null) {
logger.debug("user id not null");
try {
if (hasAccess) {
// temp folder path for creating a temp template
// We are creating a tempoarary template (person sprecific) so that more than one user can use the functuionality and
// original template remain uncorrupted
tempFolderPath = tempPath + "\" + userId;
existFile = new File(tempFolderPath);
logger.debug("existFile");
/*
* checking whether the folder already exists on the
* given path
*/
if (existFile.exists()) {
// deleting the existing directory
existFolderAtNAS = MacroEnabledExcelTemplateBus.deleteDir(existFile);
logger.debug("existFolderAtServer "+ existFolderAtServer);
}
if (existFolderAtServer) {
file = new File(tempFolderPath);
// Creates the directory
boolean b = file.mkdir();
if (b) {
// template with macros at sever
inputFile = new FileInputStream(tempPath+ "\Template.xls");
/*
* copy the excel template with macro. True
* is for copying all the attributes of
* workbook
*/
wb = new HSSFWorkbook(new POIFSFileSystem(inputFile), true);
/*
* writing the copied workbook to a new temp
* template for processing
*/
outputFile = new FileOutputStream(tempFolderPath+ "\tempTemplate.xls");
wb.write(outputFile);
/*
* Processing the temp template
*/
inputFile2 = new FileInputStream(tempFolderPath+ "\tempTemplate.xls");
wb2 = new HSSFWorkbook(new POIFSFileSystem(inputFile2), true);
/*
* Building a data sheet and hide it. Data
* sheet number is 1.
*/
macroEnabledExcelTemplateBus.buildDataSheet(wb2);
wb2.setSheetHidden(1, true);
/*
* Writing the data in main sheet
*/
Sheet outputSheet = wb2.getSheetAt(0);
// Setting the name of main sheet
wb2.setSheetName(0, "MAIN DATA Sheet");
// Creating the style for main sheet
cs = wb2.createCellStyle();
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
// creating the font for main sheet
font = wb2.createFont();
font.setFontName("Arial"); // name of font
font.setFontHeight((short) 200);
// for setting the font we need to multiply the font size by 20.
// For eg. setting font size 10, you need to multiply it by 20 i.e. 200.
cs.setFont(font);
/*
* Getting the data for main sheet from DB
*/
mainSheetData = macroEnabledExcelTemplateBus.getDataForMainSheet(parameter1, parameter2);
totalNumberOfRows = mainSheetData.length;
/*
* adding Action Type validations
*/
addressList = new CellRangeAddressList(1,totalNumberOfRows, 0, 0);
dvHelper = outputSheet.getDataValidationHelper();
dvConstraint = dvHelper.createFormulaListConstraint("Action"); // name od the list to be populated in excel sheet
validation = dvHelper.createValidation(dvConstraint, addressList);
outputSheet.addValidationData(validation);
addressList = null;
if (mainSheetData != null && mainSheetData.length > 0) {
for (int rowCount = 0; rowCount < mainSheetData.length; ++rowCount) {
// creating a new row for every record
mainSheetRow = outputSheet.createRow(newRowMainSheet);
// creating a new cell
mainSheetCell = mainSheetRow.createCell(newCellMainSheet);
// writing the value in the cell
mainSheetCell.setCellValue(""); // fetched it from db mainSheetData[rowCount][6]
// apply the css
mainSheetCell.setCellStyle(cs);
// setting the column width
//we can give the integer value in palce of auto.
outputSheet.setColumnWidth(newCellMainSheet, auto);
// INCREASE THE VALUE TO CREATE A NEW CELL
++newCellMainSheet;
// entity id data
mainSheetCell = mainSheetRow.createCell(newCellMainSheet);
// setting the cell datatype by default it is string
mainSheetCell.setCellType(Cell.CELL_TYPE_NUMERIC);
//writing the integer or double value
mainSheetCell.setCellValue(Double.parseDouble(mainSheetData[rowCount][6]));
outputSheet.setColumnWidth(newCellMainSheet, 2560);
mainSheetCell.setCellStyle(cs);
++newCellMainSheet;
/*
* adding validations dat geeting populated on the basis of previous selected cellvalue
*/
// 5 is column number from which the below validation applies
// 6 is column number upto which the below validation applies
addressList = new CellRangeAddressList(rowCount + 1, rowCount + 1,5, 6);
dvHelper = outputSheet.getDataValidationHelper();
dvConstraint = dvHelper
.createFormulaListConstraint("INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E$"
+ (rowCount + 2)+ ","&",""),"-",""),"/",""),",",""),"~","")," ",""))");
validation = dvHelper.createValidation(dvConstraint,addressList);
outputSheet.addValidationData(validation);
addressList = null;
// reset the cell value to 0 for creatibng the cell for new row
newCellMainSheet = 0;
// creating the new row.
++newRowMainSheet;
}
}
// setting the content to response
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml");
// setting the header name of excel sheet
response.setHeader("Content-Disposition","attachment; filename=MACROEXCEL.xls");
wb2.write(response.getOutputStream());
// deleting the temp template and directory
deletedDir = macroEnabledExcelTemplateBus.deleteDir(existFile);
} else {
logger.debug("Couldn't make a new folder.");
// Couldn't make a new folder
}
} else {
logger.debug("Folder already exists. getting error while deleting");
// Folder already exists. getting error while deleting it.
}
}
}
}
} catch (FileNotFoundException fnfe) {
logger.debug("error " + fnfe);
} catch (IOException ioe) {
logger.debug("error " + ioe);
} catch (Exception e) {
logger.debug("error " + e);
} finally {
// Flushing all the file streams
inputFile.close();
outputFile.close();
inputFile2.close();
wb2 = null;
wb = null;
file = null;
existFile = null;
}
}else {
logger.debug("user id is null");
}
} catch (NullPointerException npe) {
logger.debug("error " + npe);
} catch (Exception e1) {
logger.debug("error " + e1);
}
return null;
}
}
==============================================================================
/*
* class MacroEnabledExcelTemplateBus
*/
package com.tcs.reuse.business;
import java.io.File;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
// TODO: Auto-generated Javadoc
/**
* The Class MacroEnabledExcelTemplateBus.
*
*/
@SuppressWarnings("deprecation")
public class MacroEnabledExcelTemplateBus {
/** The logger. */
private static Logger logger = Logger.getLogger(package com.tcs.reuse.business.MacroEnabledExcelTemplateBus.class);
/**
* Builds the data sheet as tthe dummay databse to fetch the values for list in excel.
*
* @param dataSheet
* the data sheet
* @throws SQLException
* the sQL exception
*/
public void buildDataSheet(HSSFWorkbook workbook) throws SQLException{
Row row = null;
Cell cell = null;
Name name = null;
String[][] countriesList = null;
String[][] coverageTypeList = null;
String poleTypeList[][] = null;
String[][] iFGList = null;
String[][] businessList = null;
String[][] subBusinessList = null;
String cellValue = "";
String columnName = "A";
int newCell = 0;
int newRow = 0;
Sheet dataSheet = workbook.createSheet("DataSheet");
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom((short) 10); // single line border
style.setFillForegroundColor(HSSFColor.RED.index);
/**
* action data mapping starts new row value = 1 new cell number = 0;
*/
// CREATING CELL FOR HEADER IN DATASHEET
row = dataSheet.createRow(newRow);
++newRow;
cell = row.createCell(newCell);
cell.setCellValue("Action");
cell.setCellStyle(style);
// WRITING ALL THE Actions
row = dataSheet.createRow(newRow);
++newRow;
++newRow;
cell = row.createCell(newCell);
cell.setCellValue(new HSSFRichTextString("Add"));
++newCell;
cell = row.createCell(newCell);
cell.setCellValue(new HSSFRichTextString("Update"));
++newCell;
cell = row.createCell(newCell);
cell.setCellValue(new HSSFRichTextString("Delete"));
// CREATING THE FORMULA RANGE FOR Action
columnName = getCellColumnCount(3);
String actionFormulaRange = "Datasheet!$A$" + (newRow - 1) + ":$"+ columnName + "$" + (newRow - 1);
// SETTING THE NAME OF THE Action LIST
name = dataSheet.getWorkbook().createName();
// Name of List
name.setNameName("Action");
name.setRefersToFormula(actionFormulaRange);
newCell = 0;
// password protecting the datasheet
dataSheet.protectSheet("password");
} // end of build datasheet method
/**
* Gets the data for main sheet.
*
* @param listSize
* the list size
* @return the data for main sheet
*
* @storeProcedureParams
*/
public String[][] getDataForMainSheet(String parameter1, String parameter2
) throws SQLException {
String[][] dataForMainSheet = null;
ArrayList tempData = null;
DBUtilParam params = new DBUtilParam(3);
try {
// fetch the resuklt set from DB
} catch (WFException wfe) {
throw wfe;
} catch (Exception e) { //
} finally {
params = null;
}
return dataForMainSheet;
}
/**
* Cell column count for determining the last column number in excel.
*
* @param listSize
* the list size
* @return the string
* @limitations maximum listSize 702
*/
public String getCellColumnCount(int listSize) {
String columnName = "A";
String alphabetArray[] = { "A", "B", "C", "D", "E", "F", "G", "H", "I",
"J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U",
"V", "W", "X", "Y", "Z" };
int firstCharIndex = listSize / 26;
int secondCharIndex = listSize % 26;
if (firstCharIndex == 0 && secondCharIndex != 0) {
columnName = alphabetArray[secondCharIndex - 1];
} else if (firstCharIndex != 0 && secondCharIndex == 0) {
columnName = alphabetArray[firstCharIndex - 1];
} else {
columnName = alphabetArray[firstCharIndex - 1]
+ alphabetArray[secondCharIndex - 1];
}
return columnName;
}
/**
* Replace special char from list name with blank.
*
* @param listName
* the list name
* @return the string
*/
public String replaceSpecialCharFromName(String listName) {
String arrSpecialChar[] = { "&", "-", "/", ",", "~", " " };
String newString = listName;
for (int i = 0; i < arrSpecialChar.length; i++)
newString = StringUtils.replace(newString, arrSpecialChar[i], "");
return newString;
}
/**
* Deletes all files and subdirectories under dir. Returns true if all
* deletions were successful. If a deletion fails, the method stops
* attempting to delete and returns false.
*
* @param dir
* @return boolean
*/
public static boolean deleteDir(File dir) {
if (dir.isDirectory()) {
String[] children = dir.list();
for (int i = 0; i < children.length; i++) {
boolean success = deleteDir(new File(dir, children[i]));
if (!success) {
return false;
}
}
}
// The directory is now empty so delete it
return dir.delete();
}
}