// returns true if data is set successfully else false
public boolean setCellData(
String sheetName, String colName, int rowNum, String data, String url) {
// System.out.println("setCellData setCellData******************");
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
if (rowNum <= 0) return false;
int index = workBook.getSheetIndex(sheetName);
int colNum = -1;
if (index == -1) return false;
sheet = workBook.getSheetAt(index);
// System.out.println("A");
row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
// System.out.println(row.getCell(i).getStringCellValue().trim());
if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i;
}
if (colNum == -1) return false;
sheet.autoSizeColumn(colNum); // ashish
row = sheet.getRow(rowNum - 1);
if (row == null) row = sheet.createRow(rowNum - 1);
cell = row.getCell(colNum);
if (cell == null) cell = row.createCell(colNum);
cell.setCellValue(data);
XSSFCreationHelper createHelper = workBook.getCreationHelper();
// cell style for hyperlinks
// by default hypelrinks are blue and underlined
CellStyle hlink_style = workBook.createCellStyle();
XSSFFont hlink_font = workBook.createFont();
hlink_font.setUnderline(XSSFFont.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
// hlink_style.setWrapText(true);
XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
link.setAddress(url);
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
public boolean setCellData(String sheetName, String colName, int rowNum, String data) {
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
if (rowNum <= 0) return false;
int index = workBook.getSheetIndex(sheetName);
int colNum = -1;
if (index == -1) return false;
sheet = workBook.getSheetAt(index);
row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
// System.out.println(row.getCell(i).getStringCellValue().trim());
if (row.getCell(i).getStringCellValue().trim().equals(colName)) colNum = i;
}
if (colNum == -1) return false;
sheet.autoSizeColumn(colNum);
row = sheet.getRow(rowNum - 1);
if (row == null) row = sheet.createRow(rowNum - 1);
cell = row.getCell(colNum);
if (cell == null) cell = row.createCell(colNum);
// cell style
// CellStyle cs = workbook.createCellStyle();
// cs.setWrapText(true);
// cell.setCellStyle(cs);
cell.setCellValue(data);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
private void generateExcelDoc(String docName) throws FileNotFoundException, IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet overall = workbook.createSheet("Overall");
XSSFRow row = overall.createRow(0);
XSSFCellStyle topStyle = workbook.createCellStyle();
topStyle.setAlignment(CellStyle.ALIGN_CENTER);
XSSFCell theme = row.createCell(0);
theme.setCellValue("Theme");
overall.autoSizeColumn(0);
XSSFCell occurs = row.createCell(1);
occurs.setCellValue("Occurrences");
overall.autoSizeColumn(1);
XSSFCell prev = row.createCell(2);
prev.setCellValue("Prevalence");
overall.autoSizeColumn(2);
theme.setCellStyle(topStyle);
occurs.setCellStyle(topStyle);
prev.setCellStyle(topStyle);
for (int i = 0; i < themes.size(); i++) {
XSSFRow r = overall.createRow((i + 1));
XSSFCell c = r.createCell(0);
c.setCellValue(themes.get(i).getName());
XSSFCell c1 = r.createCell(1);
c1.setCellValue(themes.get(i).getTotalOccurs());
XSSFCell c2 = r.createCell(2);
c2.setCellValue(calculatePrevalence(themes.get(i).getTotalOccurs(), lineCount));
}
// This could be done in the previous loop but since we don't need
// indices as much, we may as well use the cleaner for each loop
for (Theme t : themes) {
XSSFSheet themeSheet = workbook.createSheet(t.getName());
XSSFRow row1 = themeSheet.createRow(0);
XSSFCell keyword = row1.createCell(0);
keyword.setCellValue("Keyword");
keyword.setCellStyle(topStyle);
XSSFCell occ = row1.createCell(1);
occ.setCellValue("Occurrences");
occ.setCellStyle(topStyle);
XSSFCell themePrev = row1.createCell(2);
themePrev.setCellValue("Prevalence");
themePrev.setCellStyle(topStyle);
for (int i = 0; i < t.getKeywords().size(); i++) {
Keyword k = t.getKeywords().get(i);
XSSFRow r = themeSheet.createRow((i + 1));
XSSFCell c = r.createCell(0);
c.setCellValue(k.getName());
XSSFCell c1 = r.createCell(1);
c1.setCellValue(k.getNumOccurs());
XSSFCell c2 = r.createCell(2);
c2.setCellValue(calculatePrevalence(k.getNumOccurs(), t.getTotalOccurs()));
}
}
FileOutputStream output = new FileOutputStream(docName);
workbook.write(output);
output.close();
}
@Override
public Workbook writeWorkbook(
Workbook workbook, String sheetTitle, List<List<String>> tableDataList, int headerNum) {
if (workbook == null) workbook = new XSSFWorkbook();
int exportRecordNum = tableDataList.size(); // 导出的总记录数
int tableStartRowNum = headerNum > 0 ? headerNum : 0; // 表体数据开始行数
int sheetNum =
exportRecordNum / (CommonConst.EXCEL_MAX_EXPORT_NUM - headerNum)
+ 1; // 工作表的页数
List<XSSFSheet> sheetList = new ArrayList<XSSFSheet>();
if (StringUtils.isEmpty(sheetTitle)) sheetTitle = "sheet";
for (int i = 0; i < sheetNum; i++) {
// 在Excel工作簿中建一工作表
String sTitle = sheetTitle + (i + 1);
XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sTitle);
sheet.setSelected(true); // 设置工作薄为选中
sheet.setAutobreaks(true);
sheet.setPrintGridlines(true);
sheetList.add(sheet);
}
/** ***********************输出表头********************************* */
if (headerNum > 0) {
for (XSSFSheet sheet : sheetList) {
XSSFRow headRow = sheet.createRow(0);
headRow.setHeightInPoints(20);
for (int i = 0; i < headerNum; i++) {
List<String> headerRowDataList = tableDataList.get(i);
for (int j = 0; j < headerRowDataList.size(); j++) {
XSSFCellStyle cellStyle = (XSSFCellStyle) createDefHeaderCellStyle(workbook); // 默认表头样式
createCell(headRow, j, headerRowDataList.get(j), cellStyle);
}
}
// 固定表头
sheet.createFreezePane(0, 1);
}
}
/** *********************输出表体内容************************* */
// 设置列样式
XSSFCellStyle columnStyle = (XSSFCellStyle) workbook.createCellStyle();
columnStyle.setFillBackgroundColor(HSSFColor.GREEN.index);
columnStyle.setWrapText(true);
if (tableDataList.size() > (tableStartRowNum + 1)) {
for (int i = tableStartRowNum; i < exportRecordNum; i++) {
List<String> rowDataList = tableDataList.get(i);
XSSFRow row = null;
int currentSheet = i / CommonConst.EXCEL_MAX_EXPORT_NUM; // 当前工作表的页数
XSSFSheet sheet = sheetList.get(currentSheet);
int rowIndex = i - CommonConst.EXCEL_MAX_EXPORT_NUM * currentSheet;
row = sheet.createRow(rowIndex);
for (int colIndex = 0, colLength = rowDataList.size(); colIndex < colLength; colIndex++) {
createCell(row, colIndex, rowDataList.get(colIndex));
}
}
}
// 调整列的宽度(取第一列为基准)
for (XSSFSheet sheet : sheetList) {
for (int i = 0; i < tableDataList.get(0).size(); i++) {
sheet.autoSizeColumn((short) i);
sheet.setColumnWidth((short) i, (short) (sheet.getColumnWidth((short) i) + 1000));
}
}
return workbook;
}
@Override
public byte[] getImportTemplate(Boolean editProductType) throws IOException {
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet productSheet = workBook.createSheet(PRODUCT_SHEETNAME);
XSSFSheet typeSheet = createTypeSheet(workBook);
if (!(true == editProductType)) {
workBook.setSheetHidden(
workBook.getSheetIndex(typeSheet), XSSFWorkbook.SHEET_STATE_VERY_HIDDEN);
}
XSSFRow titleRow = productSheet.createRow(0);
List<TransformSetting> transSettings = new TransformSettingFactory().getProductSetting();
XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
for (int i = 0; i < transSettings.size(); i++) {
TransformSetting setting = transSettings.get(i);
XSSFCell titleCell = null;
titleCell = titleRow.createCell(i, Cell.CELL_TYPE_STRING);
String cellValue = setting.getDispName();
titleCell.setCellValue(cellValue);
productSheet.autoSizeColumn(i, true);
int columnWidth = productSheet.getColumnWidth(i);
int needWidth = cellValue.getBytes().length * 256;
if (columnWidth < needWidth) {
productSheet.setColumnWidth(i, needWidth);
}
if ("productTypeName".equals(setting.getPropertyName())) {
Name type = workBook.createName();
String name = "type";
type.setNameName(name); // 定义名称
type.setRefersToFormula(
"OFFSET("
+ typeSheet.getSheetName()
+ "!$A$3,0,0,COUNTA("
+ typeSheet.getSheetName()
+ "!$A:$A)-2)");
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint) helper.createFormulaListConstraint(name); // 使用定义的名称
CellRangeAddressList regions = new CellRangeAddressList(1, 10, i, i);
DataValidation dataValidation = helper.createValidation(constraint, regions);
productSheet.addValidationData(dataValidation);
} else if ("productTypeItem".equals(setting.getPropertyName())) { // item是通过查询前面的名称来自动取得
for (int j = 0; j < 10; j++) {
XSSFRow row = productSheet.createRow(j + 1);
XSSFCell itemCell = row.createCell(i, Cell.CELL_TYPE_STRING);
int cellnum = j + 2;
itemCell.setCellFormula(
"IF(A"
+ cellnum
+ "=\"\",\"\",VLOOKUP(A"
+ cellnum
+ ",OFFSET(productType!$A$3,,,COUNTA(productType!$A:$A),2),2,0))");
}
} else if ("costMethod".equals(setting.getPropertyName())) {
int len = CostMethod.values().length;
String[] costNames = new String[len];
for (int ci = 0; ci < len; ci++) {
costNames[ci] = CostMethod.values()[ci].getMethodName();
}
// XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint) helper.createExplicitListConstraint(costNames);
CellRangeAddressList addressList = new CellRangeAddressList(1, 10, i, i);
XSSFDataValidation validation =
(XSSFDataValidation) helper.createValidation(constraint, addressList);
validation.setShowErrorBox(true);
productSheet.addValidationData(validation);
}
}
workBook.setActiveSheet(workBook.getSheetIndex(productSheet));
ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
workBook.write(baos);
return baos.toByteArray();
}
