private void printPreferencias() {
int rowNum;
XSSFSheet sheet = report.createSheet("Preferencias");
rowNum = 0;
for (Preferencia p : AlumnosCursosModel.getInstance().getPreferencias()) {
XSSFRow row = sheet.createRow(rowNum++);
int cellNum = 0;
if (rowNum == 1) {
XSSFCell cell = row.createCell(cellNum++);
cell.setCellValue("Id 1");
cell = row.createCell(cellNum++);
cell.setCellValue("Id 2");
cell = row.createCell(cellNum++);
cell.setCellValue("Satisfecha?");
cell = row.createCell(cellNum++);
cellNum = 0;
row = sheet.createRow(rowNum++);
}
XSSFCell cell = row.createCell(cellNum++);
cell.setCellValue(p.getAlumno1().getId());
cell = row.createCell(cellNum++);
cell.setCellValue(p.getAlumno2().getId());
cell = row.createCell(cellNum++);
cell.setCellValue(p.getIsSatisfecha().toString());
cell = row.createCell(cellNum++);
}
}
private XSSFSheet createTypeSheet(XSSFWorkbook workBook) {
XSSFSheet typeSheet = workBook.createSheet(PRODUCTTYPE_SHEETNAME);
List<ProductType> allTypes = productTypeDao.getAll();
Collections.sort(allTypes, new LftRgtTreeNodeComparator()); // 按左值排序。
List<AdjacencyNode<ProductType>> adjacencyNodes =
new LftRgtTreeMenuTool<ProductType>().toAdjacencyNode(allTypes);
int rownum = 0;
XSSFRow typeTitleRow = typeSheet.createRow(rownum++);
typeTitleRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue("类别名称");
typeTitleRow.createCell(1, Cell.CELL_TYPE_STRING).setCellValue("类别编号");
typeTitleRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("父类编号");
for (AdjacencyNode<ProductType> productType : adjacencyNodes) {
XSSFRow row = typeSheet.createRow(rownum);
XSSFCell typeCell = row.createCell(0, Cell.CELL_TYPE_STRING);
typeCell.setCellValue(productType.getNode().getName());
XSSFCell itemCell = row.createCell(1, Cell.CELL_TYPE_STRING);
itemCell.setCellValue(productType.getNode().getItem());
if (productType.getParent() != null) {
XSSFCell parentItemCell = row.createCell(2, Cell.CELL_TYPE_STRING);
parentItemCell.setCellValue(productType.getParent().getItem());
}
rownum++;
}
return typeSheet;
}
private void printCurso(Curso c) {
int rowNum;
XSSFSheet sheet =
report.createSheet("Curso " + c.getNombre() + (c.getIsMixto() ? "(Mixto)" : "(No Mixto)"));
rowNum = 0;
for (Alumno a : c.getAlumnos()) {
XSSFRow row = sheet.createRow(rowNum++);
int cellNum = 0;
if (rowNum == 1) {
XSSFCell cell = row.createCell(cellNum++);
cell.setCellValue("Id");
cell = row.createCell(cellNum++);
cell.setCellValue("Apellido");
cell = row.createCell(cellNum++);
cell.setCellValue("Nombre");
cell = row.createCell(cellNum++);
cellNum = 0;
row = sheet.createRow(rowNum++);
}
XSSFCell cell = row.createCell(cellNum++);
cell.setCellValue(a.getId());
cell = row.createCell(cellNum++);
cell.setCellValue(a.getApellido());
cell = row.createCell(cellNum++);
cell.setCellValue(a.getNombres());
cell = row.createCell(cellNum++);
}
}
public static void main(String[] args) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFRow row = sheet.createRow((short) 2);
XSSFCell cell = row.createCell(1);
XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");
XSSFFont font1 = wb.createFont();
font1.setBold(true);
font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
rt.applyFont(0, 10, font1);
XSSFFont font2 = wb.createFont();
font2.setItalic(true);
font2.setUnderline(XSSFFont.U_DOUBLE);
font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
rt.applyFont(10, 19, font2);
XSSFFont font3 = wb.createFont();
font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
rt.append(" Jumped over the lazy dog", font3);
cell.setCellValue(rt);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx");
wb.write(fileOut);
fileOut.close();
}
/**
* 指定した行番号の<b>XSSFRow</b>を取得する。有効範囲外の行の場合は新規作成する。
*
* @param sheet <b>XSSFSheet</b>
* @param nRow 取得したい行の行番号
* @return <b>XSSFRow</b>
*/
public static XSSFRow getRowAnyway(XSSFSheet sheet, int nRow) {
assert sheet != null;
XSSFRow row = sheet.getRow(nRow);
if (row == null) {
row = sheet.createRow(nRow);
}
return row;
}
// 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 void generate4Dispatches(
List<String> sheetNames, List<String[]> headersList, List<List<String[]>> contents)
throws IOException {
for (int i = 0; i < sheetNames.size(); i++) {
// Create a blank sheet
XSSFSheet spreadsheet = workbook.createSheet(sheetNames.get(i));
// Create row object
XSSFRow row;
// This data needs to be written (Object[])
Map<Integer, String[]> contentMap = new TreeMap<>();
contentMap.put(1, headersList.get(i));
for (int j = 0; j < contents.get(i).size(); j++) {
contentMap.put(j + 2, contents.get(i).get(j));
}
// Iterate over data and write to sheet
Set<Integer> keyid = contentMap.keySet();
int rowid = 0;
for (Integer key : keyid) {
row = spreadsheet.createRow(rowid++);
String[] objectArr = contentMap.get(key);
int cellid = 0;
for (String obj : objectArr) {
if (obj.contains("\r\n")) {
String[] strArray = obj.split("\r\n");
for (String str : strArray) {
Cell cell = row.createCell(cellid);
cell.setCellValue(str);
row = spreadsheet.createRow(rowid++);
}
} else {
Cell cell = row.createCell(cellid++);
cell.setCellValue(obj);
}
}
}
// for (int c = 0; c < headersList.get(i).length; c++) {
// spreadsheet.autoSizeColumn(c);
// }
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File(fileName));
workbook.write(out);
out.close();
}
}
public <S, L> void rulewriter(Map<S, L> map) throws IOException {
FileOutputStream fos = new FileOutputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet xsheet = workbook.createSheet();
int count = 0;
XSSFRow row = xsheet.createRow(count++);
row.createCell(0).setCellValue("Error ID");
row.createCell(1).setCellValue("Rule ID");
for (Entry<S, L> entry : map.entrySet()) {
row = xsheet.createRow(count);
row.createCell(0).setCellValue((String) entry.getKey());
row.createCell(1).setCellValue(entry.getValue().toString());
count++;
}
workbook.write(fos);
fos.close();
}
public static void setCellStyleX(int row, int col, XSSFSheet sheet, XSSFCellStyle style) {
XSSFRow r = sheet.getRow(row);
if (null == r) {
r = sheet.createRow(row);
}
XSSFCell cell = r.getCell(col);
if (null == cell) {
cell = sheet.getRow(row).createCell(col);
}
cell.setCellStyle(style);
}
public static void setCellsX(int row, int col, Object colValue, XSSFFont font, XSSFSheet sheet) {
XSSFRow r = sheet.getRow(row);
if (null == r) {
r = sheet.createRow(row);
}
XSSFCell cell = r.getCell(col);
if (null == cell) {
cell = sheet.getRow(row).createCell(col);
}
setCellValueOfCnX(cell, colValue);
cell.getCellStyle().setFont(font);
}
public static void addErrorDescriptionRow(int rowNum, String field, String errorDescription) {
try {
XSSFRow destRow = sheetWrite2.createRow(rowCount);
XSSFCell cell = destRow.createCell(0);
cell.setCellValue(Integer.toString(rowNum));
cell = destRow.createCell(1);
cell.setCellValue(field);
cell = destRow.createCell(2);
cell.setCellValue(errorDescription);
} catch (Exception ex) {
System.out.println(
"Error in writing descriptionRow in reject excel file :: " + ex.getMessage());
ex.printStackTrace();
}
}
public void fillData(List<Mobile> list, XSSFWorkbook workbook, XSSFSheet sheet) {
int j = 0;
int rowIndex = 2;
int stt = 1;
// XSSFCellStyle style1 = workbook.createCellStyle();
// style1.setFillForegroundColor(HSSFColor.AQUA.index);
// style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
// XSSFCreationHelper createHelper = workbook.getCreationHelper();
// XSSFCellStyle style2 = workbook.createCellStyle();
// style2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
ExcelUtil ex = new ExcelUtil(workbook);
for (int i = 0; i < list.size(); i++) {
Row row = sheet.createRow(rowIndex++);
j = 0;
row.createCell(j++).setCellValue(stt++);
row.createCell(j++).setCellValue(list.get(i).getWeb());
row.createCell(j++).setCellValue(list.get(i).getType());
row.createCell(j++).setCellValue(list.get(i).getBrand());
if (list.get(i).getPriceNumber() != null) {
ex.formatNumber(row, j++, list.get(i).getPriceNumber(), ex.number);
} else {
j++;
}
row.createCell(j++).setCellValue(list.get(i).getItemCode());
row.createCell(j++).setCellValue(list.get(i).getName());
row.createCell(j++).setCellValue(list.get(i).getModel());
row.createCell(j++).setCellValue(list.get(i).getStorage());
row.createCell(j++).setCellValue(list.get(i).getRam());
row.createCell(j++).setCellValue(list.get(i).getScreen());
row.createCell(j++).setCellValue(list.get(i).getCpu());
row.createCell(j++).setCellValue(list.get(i).getBackCamera());
row.createCell(j++).setCellValue(list.get(i).getFrontCamera());
row.createCell(j++).setCellValue(list.get(i).getOs());
row.createCell(j++).setCellValue(list.get(i).getBattery());
row.createCell(j++).setCellValue(list.get(i).getSim());
row.createCell(j++).setCellValue(list.get(i).getColor());
row.createCell(j++).setCellValue(list.get(i).getPromotion());
// Cell cell = row.createCell(j++);
// cell.setCellValue(list.get(i).getLastUpdate());
// cell.setCellStyle(style2);
ex.formatDate(row, j++, list.get(i).getLastUpdate(), ex.date);
row.createCell(j++).setCellValue(list.get(i).getLink());
}
}
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 XSSFRow createRowFrom(IRecordable o) {
XSSFRow lastRow, r;
String[] data;
if (dicoLocations.containsKey(o.getClassement()))
lastRow = dicoLocations.get(o.getClassement());
else {
XSSFSheet logSheet = wb.createSheet(o.getClassement());
lastRow = logSheet.createRow(0);
data = o.getTitles();
fillRowWith(lastRow, data);
dicoLocations.put(o.getClassement(), lastRow);
}
r = lastRow.getSheet().createRow(lastRow.getRowNum() + 1);
data = o.getRecords();
fillRowWith(r, data);
dicoLocations.replace(o.getClassement(), r);
return r;
}
/**
* @param newSheet the sheet to create from the copy.
* @param sheet the sheet to copy.
* @param copyStyle true copy the style.
*/
public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
int maxColumnNum = 0;
Map<Integer, XSSFCellStyle> styleMap =
(copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
XSSFRow srcRow = sheet.getRow(i);
XSSFRow destRow = newSheet.createRow(i);
if (srcRow != null) {
XSSFCopySheet.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public static void addErrorRow(XSSFRow srcRow) {
try {
XSSFRow destRow = sheetWrite.createRow(rowCount);
// XSSFRow destRow =null;
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
XSSFCell oldCell = srcRow.getCell(j); // ancienne cell
XSSFCell newCell = destRow.getCell(j); // new cell
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(oldCell, newCell);
}
}
} catch (Exception ex) {
System.out.println("Error in writing Row in reject excel file :: " + ex.getMessage());
ex.printStackTrace();
}
}
public static void main(String[] args) throws IOException {
File file = new File("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.getRow(0);
XSSFCell cell = row.getCell(0);
System.out.println(cell);
XSSFSheet write = workbook.createSheet();
XSSFRow rowwrite = write.createRow(0);
XSSFCell cell1 = rowwrite.createCell(1);
cell1.setCellValue("Yamini");
FileOutputStream file1 =
new FileOutputStream("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
workbook.write(file1);
file1.close();
}
// returns true if column is created successfully
public boolean addColumn(String sheetName, String colName) {
// System.out.println("**************addColumn*********************");
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
int index = workBook.getSheetIndex(sheetName);
if (index == -1) return false;
XSSFCellStyle style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
sheet = workBook.getSheetAt(index);
row = sheet.getRow(0);
if (row == null) row = sheet.createRow(0);
// cell = row.getCell();
// if (cell == null)
// System.out.println(row.getLastCellNum());
if (row.getLastCellNum() == -1) cell = row.createCell(0);
else cell = row.createCell(row.getLastCellNum());
cell.setCellValue(colName);
cell.setCellStyle(style);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
public static void WriteCol(String QueryType, String num) throws IOException {
Map<String, Object[]> data = new HashMap<String, Object[]>();
List<String> sqlDML = new ArrayList<String>();
File myfile = new File("C://Users/Administrator/Documents/Heckathon/Result.xlsx");
FileInputStream fis = new FileInputStream(myfile);
XSSFWorkbook myworkbook = new XSSFWorkbook(fis);
XSSFSheet mysheet = myworkbook.getSheetAt(2);
data.put(num, new Object[] {QueryType, num});
Set<String> newRows = data.keySet();
int rownum = mysheet.getPhysicalNumberOfRows();
for (String Key : newRows) {
Row row = mysheet.createRow(rownum++);
Object[] objArr = data.get(Key);
int cellnum = 0;
for (Object obj : objArr) {
Cell cell = row.createCell(cellnum++);
cell.setCellValue((String) obj);
}
}
FileOutputStream os = new FileOutputStream(myfile);
myworkbook.write(os);
System.out.println("Record Entered");
}
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();
}
public static boolean writeArray2XLSXFile(
String FileName, String[] titleNames, List<String[]> arry) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = titleNames.length;
int rowNum = arry.size();
// for (int i = 0; i < columnNum; i++) {
// sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
// }
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
XSSFFont headFont = wb.createFont();
headFont.setFontName("Courier New");
headFont.setFontHeightInPoints((short) 10);
headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
headStyle.setLocked(true);
headStyle.setFont(headFont);
headStyle.setBorderTop((short) 2);
headStyle.setBorderBottom((short) 1);
XSSFCellStyle contentStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
XSSFFont contentFont = wb.createFont();
contentFont.setFontName("Courier New");
contentFont.setFontHeightInPoints((short) 9);
// headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
contentFont.setColor(HSSFColor.BLACK.index);
contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
contentStyle.setFont(contentFont);
// create titile row
XSSFRow row = sheet1.createRow(0);
int heandLine = 0;
XSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
heandLine++;
}
for (int i = 0; i < rowNum; i++) {
row = sheet1.createRow((i + heandLine));
String[] line = (String[]) arry.get(i);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(FileName);
wb.write(fileOut);
fileOut.close();
return true;
}
public static boolean writeArray2XLSXSheet(
XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception {
int rowNum = arry.size();
if (rowNum == 0) {
System.err.println("No input data!");
return false;
}
String[] titleNames = null;
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20)));
}
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
XSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
XSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell((short) i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = (String[]) arry.get(i);
columnNum = line.length;
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[0] != null) {
cell.setCellStyle(markedBodyStyle);
} else {
cell.setCellStyle(bodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue("");
}
}
}
return true;
}
public static boolean convertTextFile2XLSXFile(
String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception {
BufferedReader br = LocalFileFunc.getBufferedReader(inFileName);
String line = br.readLine();
if (line == null) {
return false;
}
String[] cells1 = Util.tokenize(line, '\t');
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = cells1.length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
}
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom font to the text in the comment
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
String lastKey = null;
int switcher = -1;
XSSFCell cell = null;
if (hasHead) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(cells1[i]);
cell.setCellStyle(headStyle);
}
} else {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(cells1[i]);
cell.setCellStyle(bodyStyle);
}
}
rowIndex++;
while ((line = br.readLine()) != null) {
cells1 = Util.tokenize(line, '\t');
row = sheet1.createRow((rowIndex));
columnNum = cells1.length;
if (indexKey >= 0) {
if (lastKey == null && cells1[indexKey] != null) {
lastKey = cells1[indexKey];
switcher *= -1;
} else if (lastKey != null && cells1[indexKey] == null) {
lastKey = cells1[indexKey];
switcher *= -1;
} else if (lastKey == null && cells1[indexKey] == null) {
} else {
if (!lastKey.equals(cells1[indexKey])) {
switcher *= -1;
lastKey = cells1[indexKey];
}
}
} else {
switcher = 1;
}
// System.out.println(cells1[0]);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (cells1[j] != null) {
if (Util.isNumeric(cells1[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(cells1[j]));
} else {
cell.setCellValue(cells1[j]);
}
} else {
cell.setCellValue(".");
}
}
rowIndex++;
}
br.close();
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(outFileName);
wb.write(fileOut);
fileOut.close();
return true;
}
public static boolean writeMultArray2XLSXFile(
String fileName,
List<List<String[]>> arrys,
List<String> sheetLabels,
boolean hasHead,
int indexKey)
throws Exception {
if (arrys.isEmpty()) {
System.err.println("No input data!");
return false;
}
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom font to the text in the comment
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
String lastKey = null;
int switcher = -1;
XSSFCell cell = null;
String[] titleNames = null;
int d = 0;
for (List<String[]> arry : arrys) {
XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d));
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
}
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
int rowNum = arry.size();
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = (String[]) arry.get(i);
columnNum = line.length;
if (indexKey >= 0) {
if (lastKey == null && line[indexKey] != null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey != null && line[indexKey] == null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey == null && line[indexKey] == null) {
} else {
if (!lastKey.equals(line[indexKey])) {
switcher *= -1;
lastKey = line[indexKey];
}
}
} else {
switcher = 1;
}
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
d++;
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
return true;
}
private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
XSSFWorkbook wb = null;
try {
if (isXssf) {
XSSFWorkbook w = new XSSFWorkbook();
} else {
HSSFWorkbook w = new HSSFWorkbook();
}
wb = new XSSFWorkbook();
XSSFDataFormat format = wb.createDataFormat();
XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
XSSFDataFormat passwordFormat = wb.createDataFormat();
passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
List<ExcelHeader> headers = getHeaderList(clz);
Collections.sort(headers);
sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
Row r0 = sheet.createRow(0);
Cell cell = r0.createCell(0);
r0.setHeightInPoints(28);
cell.setCellValue(message + "备份记录");
Row r = sheet.createRow(1);
r.setHeightInPoints(25);
cell.setCellStyle(cellStyle);
// 输出标题
for (int i = 0; i < headers.size(); i++) {
Cell cell1 = r.createCell(i);
if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
else cell1.setCellStyle(cellStyle);
cell1.setCellValue(headers.get(i).getTitle());
}
Object obj = null;
// 输出用户资料信息
if (message.indexOf("用户资料 ") > 0) {
sheet.setColumnWidth(3, 32 * 150);
sheet.setColumnWidth(4, 32 * 110);
sheet.setColumnWidth(7, 32 * 120);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
copyDefaultCellStyle(null, cell2, cellStyle, 0);
if (getMethodName(headers.get(j)).equals("nabled"))
cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
else if (getMethodName(headers.get(j)).equals("password")) {
cell2.setCellStyle(passwordCellStyle);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
} else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 输出房间使用信息数据
else {
sheet.setColumnWidth(0, 32 * 80);
sheet.setColumnWidth(2, 32 * 100);
sheet.setColumnWidth(3, 32 * 190);
sheet.setColumnWidth(4, 32 * 190);
sheet.setColumnWidth(5, 32 * 190);
sheet.setColumnWidth(10, 32 * 130);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
if (j == 3 || j == 4 || j == 5) {
XSSFCellStyle cs3 = wb.createCellStyle();
cell2.setCellValue(new Date());
copyDefaultCellStyle(format, cell2, cs3, 1);
}
if (j == 10) {
XSSFCellStyle cs2 = wb.createCellStyle();
copyDefaultCellStyle(format, cell2, cs2, 2);
}
copyDefaultCellStyle(null, cell2, cellStyle, 0);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 设置行列的默认宽度和高度
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NoSuchMethodException e) {
e.printStackTrace();
logger.error(e);
}
return wb;
}
@Command
public void exportar() throws CloneNotSupportedException {
// if(itemSel == null){
// Clientes.showWarning("Seleccione una factura para realizar esta operacion");
// return;
// }
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Articulos");
// This data needs to be written (Object[])
// Map<String, Object[]> data = new TreeMap<String, Object[]>();
// data.put("1", new Object[] { "ID", "COD.GRUPO"
// , "GRUPO"
// , "COD.ART"
// , "COD.ART"
// , "ARTICULO"
// , "MARCA"
// , "PROCEDENCIA"
// , "U.MEDIDA"
// , "CANT"
// });
Filtro f = new Filtro();
// f.setId_facturacion(itemSel.getId());
f.setInicio(0);
f.setFin(0);
java.util.List itemsModel = itemsDao.selectPagos(f); // detalleFacturasMapper.selectDetalle(f);
Iterator<Articulos> det = itemsModel.iterator();
int rownum = 1;
// cabeceras
Row row0 = sheet.createRow(0);
int i = 0;
row0.createCell(i++).setCellValue("COD.GRUPO");
row0.createCell(i++).setCellValue("GRUPO");
row0.createCell(i++).setCellValue("COD.ART");
row0.createCell(i++).setCellValue("ARTICULO");
row0.createCell(i++).setCellValue("MARCA");
row0.createCell(i++).setCellValue("PROCEDENCIA");
row0.createCell(i++).setCellValue("U.MEDIDA");
row0.createCell(i++).setCellValue("CANTIDAD");
// FacturasItemsExample fie = new FacturasItemsExample();
// fie.createCriteria().andId_factEqualTo(itemSel.getId());
// List<FacturasItems> flist = facturasItemsMapper.selectByExample(fie);
// Iterator<FacturasItems> flistItem = flist.iterator();
// int i = 5;
// while (flistItem.hasNext()) {
// FacturasItems ff = flistItem.next();
// row0.createCell(i++).setCellValue(ff.getDescripcion());
// }
// datos
while (det.hasNext()) {
Articulos df = det.next();
Row row = sheet.createRow(rownum++);
i = 0;
row.createCell(i++).setCellValue(df.getCodigogrupo());
row.createCell(i++).setCellValue(df.getGrupo());
row.createCell(i++).setCellValue(df.getCodigo());
row.createCell(i++).setCellValue(df.getArticulo());
row.createCell(i++).setCellValue(df.getMarca());
row.createCell(i++).setCellValue(df.getProcedencia());
row.createCell(i++).setCellValue(df.getUnidadmedida());
row.createCell(i++).setCellValue(new BigDecimal("0").doubleValue());
// DetalleFacturasItemsExample de = new DetalleFacturasItemsExample();
// de.createCriteria().andId_dfEqualTo(df.getId());
// List<DetalleFacturasItems> dflist = detalleFacturasItemsMapper
// .selectByExample(de);
// Iterator<DetalleFacturasItems> dfit = dflist.iterator();
// i = 5;
// while (dfit.hasNext()) {
// DetalleFacturasItems dfi = dfit.next();
// row.createCell(i++).setCellValue(dfi.getValor().doubleValue());
// }
}
try {
// Write the workbook in file system
ParametrosKey pk = new ParametrosKey();
pk.setId("DATA_FOLDER");
pk.setId_app(UsuarioLogueado.getIdApp());
String dataFolder = parametrosMapper.selectByPrimaryKey(pk).getValor();
String path = dataFolder + File.separatorChar + "export_articulos_tmp.xlsx";
FileOutputStream out = new FileOutputStream(new File(path));
workbook.write(out);
out.close();
File iof = new File(path);
if (!iof.exists()) {
Clientes.showWarning("No se puede encontrar el archivo generado");
return;
}
java.io.InputStream is =
new FileInputStream(iof); // desktop.getWebApp().getResourceAsStream(path);
if (is != null) {
AMedia am = new AMedia(iof, null, null);
SimpleDateFormat fmt = new SimpleDateFormat("MM-yyyy");
String fe = fmt.format(new java.util.Date());
String me = fe.substring(0, 7);
Filedownload.save(is, am.getContentType(), "Db_productos_" + me);
} else {
Clientes.showWarning("No se puede encontrar el archivo generado");
}
// System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
} catch (Exception e) {
e.printStackTrace();
}
}
@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;
}
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
File excelFile = new File("export_parts.xls");
// Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Parts Data");
String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
String[] columns = header.split(";");
Map<Integer, String[]> data = new HashMap<>();
String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
data.put(1, headerFormatted);
Map<Integer, String[]> commentsData = new HashMap<>();
String[] headerComments = createXLSHeaderRowComments(header, columns);
commentsData.put(1, headerComments);
List<String> selects = queryResult.getQuery().getSelects();
int i = 1;
for (QueryResultRow row : queryResult.getRows()) {
i++;
data.put(i, createXLSRow(selects, row, baseURL));
commentsData.put(i, createXLSRowComments(selects, row));
}
// Iterate over data and write to sheet
Set<Integer> keyset = data.keySet();
int rownum = 0;
for (Integer key : keyset) {
Row row = sheet.createRow(rownum++);
String[] objArr = data.get(key);
int cellnum = 0;
for (String obj : objArr) {
Cell cell = row.createCell(cellnum++);
cell.setCellValue(obj);
}
CreationHelper factory = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
String[] commentsObjArr = commentsData.get(key);
cellnum = 0;
for (String commentsObj : commentsObjArr) {
if (commentsObj.length() > 0) {
Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);
// When the comment box is visible, have it show in a 1x3 space
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum() + 1);
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString(commentsObj);
comment.setString(str);
// Assign the comment to the cell
cell.setCellComment(comment);
}
cellnum++;
}
}
// Define header style
Font headerFont = workbook.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 10);
headerFont.setFontName("Courier New");
headerFont.setItalic(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// Set header style
for (int j = 0; j < columns.length; j++) {
Cell cell = sheet.getRow(0).getCell(j);
cell.setCellStyle(headerStyle);
if (cell.getCellComment() != null) {
String comment = cell.getCellComment().getString().toString();
if (comment.equals(QueryField.CTX_PRODUCT_ID)
|| comment.equals(QueryField.CTX_SERIAL_NUMBER)
|| comment.equals(QueryField.PART_MASTER_NUMBER)) {
for (int k = 0; k < queryResult.getRows().size(); k++) {
Cell grayCell =
sheet.getRow(k + 1).getCell(j) != null
? sheet.getRow(k + 1).getCell(j)
: sheet.getRow(k + 1).createCell(j);
grayCell.setCellStyle(headerStyle);
}
}
}
}
try {
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(excelFile);
workbook.write(out);
out.close();
} catch (Exception e) {
LOGGER.log(Level.FINEST, null, e);
}
return excelFile;
}
@Override
public XSSFWorkbook exportAllStatistics(
List<StatisticData> statistics, Interview interview, ByteArrayOutputStream out)
throws IOException {
/*NULL is correct value*/
try (XSSFWorkbook book = new XSSFWorkbook()) {
XSSFSheet sheet = book.createSheet(SHEET_NAME);
int rowNumber = 0;
XSSFRow mainHeader = sheet.createRow(rowNumber++);
XSSFCell cell = mainHeader.createCell(0);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(HEADER_PREFIX + interview.getName() + "\"");
makeCellAutosizeAndBold(book, mainHeader);
rowNumber++;
for (StatisticData statistic : statistics) {
/*Add question text*/
XSSFRow row = sheet.createRow(rowNumber++);
XSSFCell questionText = row.createCell(0);
questionText.setCellType(Cell.CELL_TYPE_STRING);
questionText.setCellValue(QUESTION_PREFIX + statistic.getQuestionText());
XSSFRow tableHeader = sheet.createRow(rowNumber++);
XSSFCell c1 = tableHeader.createCell(0);
XSSFCell c2 = tableHeader.createCell(1);
XSSFCell c3 = tableHeader.createCell(2);
XSSFCell c4 = tableHeader.createCell(3);
c1.setCellType(Cell.CELL_TYPE_STRING);
c2.setCellType(Cell.CELL_TYPE_NUMERIC);
c3.setCellType(Cell.CELL_TYPE_NUMERIC);
c4.setCellType(Cell.CELL_TYPE_STRING);
c1.setCellValue(ANSWER_CELL_HEADER);
c2.setCellValue(PEOPLE_COUNT_HEADER);
c3.setCellValue(PERCENT_HEADER);
c4.setCellValue(RESPONDENTS_HEADER);
makeCellAutosizeAndBold(book, tableHeader);
Map<String, Object[]> answerData = statistic.getAnswerData();
for (String key : answerData.keySet()) {
XSSFRow answer = sheet.createRow(rowNumber++);
XSSFCell answerText = answer.createCell(0);
XSSFCell peopleResponded = answer.createCell(1);
XSSFCell percentResponded = answer.createCell(2);
XSSFCell respondents = answer.createCell(3);
answerText.setCellType(Cell.CELL_TYPE_STRING);
peopleResponded.setCellType(Cell.CELL_TYPE_NUMERIC);
percentResponded.setCellType(Cell.CELL_TYPE_NUMERIC);
respondents.setCellType(Cell.CELL_TYPE_STRING);
answerText.setCellValue(key);
Object[] values = answerData.get(key);
peopleResponded.setCellValue(values[0].toString());
percentResponded.setCellValue(values[1].toString().replace(",", "."));
respondents.setCellValue(userAnswerService.getRespondentListHowLine(interview, key));
makeCellsAutosize(book, answer);
}
rowNumber++;
}
book.write(out);
return book;
}
}
public XSSFWorkbook writeCTGI(
XSSFWorkbook cTGWorkbook,
MultipartFile fileIn,
String classId,
MultipartFile staplesMasterStyleGuide,
MultipartFile attributeReport) {
try {
List<XSSFCell> attributeCell = writeStyle(staplesMasterStyleGuide, classId);
List<String> attributes = getAttributes(attributeReport, classId);
XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream());
XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
XSSFSheet worksheetOut = cTGWorkbook.getSheetAt(0);
int i = 1;
int j = 4;
XSSFCell cell = null;
for (i = 1; i < worksheetIn.getLastRowNum(); i++) {
XSSFRow rowIn = worksheetIn.getRow(i);
XSSFRow rowOut = worksheetOut.createRow(j);
if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) {
XSSFCell cell1 = rowOut.createCell(13);
if (rowIn.getCell(2) != null) cell1.setCellValue(rowIn.getCell(2).getStringCellValue());
XSSFCell cell2 = rowOut.createCell(15);
cell2.setCellValue(classId);
XSSFCell cell3 = rowOut.createCell(19);
if (rowIn.getCell(3) != null) cell3.setCellValue(rowIn.getCell(3).getStringCellValue());
XSSFCell cell4 = rowOut.createCell(20);
if (rowIn.getCell(0) != null) cell4.setCellValue(rowIn.getCell(0).getStringCellValue());
XSSFCell cell5 = rowOut.createCell(24);
if (rowIn.getCell(3) != null) cell5.setCellValue(rowIn.getCell(3).getStringCellValue());
XSSFCell cell6 = rowOut.createCell(179);
if (rowIn.getCell(5) != null) cell6.setCellValue(rowIn.getCell(5).getNumericCellValue());
XSSFCell cell7 = rowOut.createCell(26);
cell7 = attributeCell.get(0);
int z = 1;
for (int k = 36; k < 48; k++) {
cell = rowOut.createCell(k);
cell.setCellValue((attributeCell.get(z)).getStringCellValue());
z++;
}
int counter = 1;
int cellCount = 63;
for (String attribute : attributes) {
if (counter <= 50) {
String lable = attribute.split("###")[0];
String value = attribute.split("###")[1];
cell = rowOut.createCell(cellCount);
cell.setCellValue(lable);
cell = rowOut.createCell(cellCount + 1);
cell.setCellValue(value);
counter++;
cellCount += 2;
}
}
}
j++;
}
} catch (IOException e) {
e.printStackTrace();
}
return cTGWorkbook;
}
