// removes a column and all the contents
public boolean removeColumn(String sheetName, int colNum) {
try {
if (!isSheetExist(sheetName)) return false;
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
sheet = workBook.getSheet(sheetName);
XSSFCellStyle style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
XSSFCreationHelper createHelper = workBook.getCreationHelper();
style.setFillPattern(HSSFCellStyle.NO_FILL);
for (int i = 0; i < getRowCount(sheetName); i++) {
row = sheet.getRow(i);
if (row != null) {
cell = row.getCell(colNum);
if (cell != null) {
cell.setCellStyle(style);
row.removeCell(cell);
}
}
}
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
private int createMonth(
XSSFSheet sheet, LocalDate dt, int dayRow, int monthRow, int row, CellCopyPolicy policy) {
int r = row;
final int nbDays = dt.dayOfMonth().getMaximumValue();
final int month = dt.monthOfYear().get();
/* Insert days for the month */
for (int day = 1; day <= nbDays; day++) {
XSSFRow inserted = copyRow(sheet, dayRow, r, policy);
inserted.getCell(COLUMN_MONTH).setCellValue(month);
inserted.getCell(COLUMN_DAY).setCellValue(day);
r++;
}
/* Insert month summary */
XSSFRow inserted = copyRow(sheet, monthRow, r, policy);
inserted.getCell(COLUMN_MONTH).setCellValue(month);
r++;
/* Group month rows */
int groupRow = r - 2;
sheet.groupRow(row, groupRow);
sheet.setRowGroupCollapsed(groupRow, true);
return r;
}
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>CellStyle</b>を生成 1行のみ描画する
*
* @param workbook ワークブック
* @param sheet シート
* @param nRow 行
* @param nColumn 列
* @param isBold 太字フラグ
* @param fontSize 文字サイズ
* @param fontHeight 行高
*/
public static void setCellStyleForLabel(
XSSFWorkbook workbook,
XSSFSheet sheet,
int nRow,
int nColumn,
boolean isBold,
short fontSize,
float fontHeight) {
assert sheet != null;
// style設定
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
if (isBold) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 文字太字
}
font.setFontHeightInPoints((short) fontSize); // 文字サイズ
font.setFontName(DEFAULT_FONT_NAME);
style.setFont(font); // 文字太字 と 文字サイズ
style.setAlignment(CellStyle.ALIGN_GENERAL); // 水平方向の標準
style.setVerticalAlignment(CellStyle.VERTICAL_TOP); // 垂直方向の上詰め
style.setWrapText(true); // 折り返して全体を表示する
// セルに罫線を描画
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellStyle(style);
row.setHeightInPoints(fontHeight); // 行高設定
}
public ArrayList<String> working_Section(
int SOOWDLocation, int EOOWDLocation, XSSFWorkbook workbook) {
ArrayList<String> workingSection = new ArrayList<String>();
XSSFRow row;
XSSFCell cell;
XSSFSheet Sheet = workbook.getSheet("Operation_Standard");
String genrateFormula;
for (int start = (SOOWDLocation - 1); start < EOOWDLocation; start++) {
try {
row = Sheet.getRow(start);
cell = row.getCell(2);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
genrateFormula = "Operation_Standard!C" + (row.getRowNum() + 1);
workingSection.add(genrateFormula);
break;
case Cell.CELL_TYPE_BLANK:
break;
default:
System.out.println("Error");
break;
}
} catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
return workingSection;
}
/**
* 指定した列番号の<b>XSSFCell</b>を取得する。有効範囲外のセルの場合は新規作成する。
*
* @param row <b>XSSFRow</b>
* @param nColumn 取得したいセルの列番号
* @return <b>XSSFCell</b>
*/
public static XSSFCell getCellAnyway(XSSFRow row, int nColumn) {
assert row != null;
XSSFCell cell = row.getCell(nColumn);
if (cell == null) {
cell = row.createCell(nColumn);
}
return cell;
}
/**
* Compares two <code>XSSFRow</code> objects. Two rows are equal if they belong to the same
* worksheet and their row indexes are equal.
*
* @param row the <code>XSSFRow</code> to be compared.
* @return the value <code>0</code> if the row number of this <code>XSSFRow</code> is equal to the
* row number of the argument <code>XSSFRow</code>; a value less than <code>0</code> if the
* row number of this this <code>XSSFRow</code> is numerically less than the row number of the
* argument <code>XSSFRow</code>; and a value greater than <code>0</code> if the row number of
* this this <code>XSSFRow</code> is numerically greater than the row number of the argument
* <code>XSSFRow</code>.
* @throws IllegalArgumentException if the argument row belongs to a different worksheet
*/
public int compareTo(XSSFRow row) {
int thisVal = this.getRowNum();
if (row.getSheet() != getSheet())
throw new IllegalArgumentException("The compared rows must belong to the same XSSFSheet");
int anotherVal = row.getRowNum();
return (thisVal < anotherVal ? -1 : (thisVal == anotherVal ? 0 : 1));
}
@Override
public String executeAction(HttpServletRequest request, List<FileItem> sessionFiles)
throws UploadActionException {
StringBuffer response = new StringBuffer();
for (FileItem item : sessionFiles) {
if (false == item.isFormField()) {
try {
if (item.getName().endsWith(".xls")) {
POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
HSSFWorkbook wb = new HSSFWorkbook(fs);
System.out.println("Sheet Num:" + wb.getNumberOfSheets());
// only get first sheet,ignore others
HSSFSheet sheet = wb.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
response.append(cell.toString() + ":");
}
response.append("\n");
}
} else if (item.getName().endsWith(".xlsx")) {
// POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
XSSFWorkbook wb = new XSSFWorkbook(item.getInputStream());
System.out.println("Sheet Num:" + wb.getNumberOfSheets());
// only get first sheet,ignore others
XSSFSheet sheet = wb.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
XSSFRow row = (XSSFRow) rows.next();
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
XSSFCell cell = (XSSFCell) cells.next();
response.append(cell.toString() + ":");
}
response.append("\n");
}
}
} catch (Exception e) {
throw new UploadActionException(e);
}
}
}
// / Remove files from session because we have a copy of them
removeSessionFileItems(request);
// / Send your customized message to the client.
return response.toString();
}
// 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;
}
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 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 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);
}
/**
* 合并单元格后给合并后的单元格加边框
*
* @param region
* @param cs
*/
public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {
int toprowNum = region.getFirstRow();
for (int i = toprowNum; i <= region.getLastRow(); i++) {
XSSFRow row = sheet.getRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
XSSFCell cell = row.getCell(j); // XSSFCellUtil.getCell(row,
// (short) j);
cell.setCellStyle(cs);
}
}
}
/**
* 列方向のセルの値を合算する
*
* @param sheet 編集対象シート
* @param nColumn 行番号
* @param nStartRow 開始列番号
* @param nEndRow 終了列番号
* @return 合算値
*/
public static int sumColumn(XSSFSheet sheet, int nColumn, int nStartRow, int nEndRow) {
int sum = 0;
for (int nIndex = nStartRow; nIndex <= nEndRow; nIndex++) {
XSSFRow row = sheet.getRow(nIndex);
assert row != null;
XSSFCell cell = row.getCell(nColumn);
assert cell != null;
if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
sum += cell.getNumericCellValue();
}
}
return sum;
}
// returns the data from a cell
public String getCellData(String sheetName, String colName, int rowNum) {
try {
if (rowNum <= 0) return "";
int index = workBook.getSheetIndex(sheetName);
int col_Num = -1;
if (index == -1) return "";
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.trim())) col_Num = i;
}
if (col_Num == -1) return "";
sheet = workBook.getSheetAt(index);
row = sheet.getRow(rowNum - 1);
if (row == null) return "";
cell = row.getCell(col_Num);
if (cell == null) return "";
// System.out.println(cell.getCellType());
if (cell.getCellType() == Cell.CELL_TYPE_STRING) return cell.getStringCellValue();
else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
|| cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
String cellText = String.valueOf(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// format in form of M/D/YY
double d = cell.getNumericCellValue();
Calendar cal = Calendar.getInstance();
cal.setTime(HSSFDateUtil.getJavaDate(d));
cellText = (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
cellText =
cal.get(Calendar.DAY_OF_MONTH) + "/" + cal.get(Calendar.MONTH) + 1 + "/" + cellText;
// System.out.println(cellText);
}
return cellText;
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return "";
else return String.valueOf(cell.getBooleanCellValue());
} catch (Exception e) {
e.printStackTrace();
return "row " + rowNum + " or column " + colName + " does not exist in xls";
}
}
/**
* @param srcSheet the sheet to copy.
* @param destSheet the sheet to create.
* @param srcRow the row to copy.
* @param destRow the row to create.
* @param styleMap -
*/
public static void copyRow(
XSSFSheet srcSheet,
XSSFSheet destSheet,
XSSFRow srcRow,
XSSFRow destRow,
Map<Integer, XSSFCellStyle> styleMap) {
Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
XSSFCell oldCell = srcRow.getCell(j);
XSSFCell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(oldCell, newCell, styleMap);
CellRangeAddress mergedRegion =
getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
if (mergedRegion != null) {
CellRangeAddress newMergedRegion =
new CellRangeAddress(
mergedRegion.getFirstRow(),
mergedRegion.getFirstColumn(),
mergedRegion.getLastRow(),
mergedRegion.getLastColumn());
CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
if (isNewMergedRegion(wrapper, mergedRegions)) {
mergedRegions.add(wrapper);
destSheet.addMergedRegion(wrapper.range);
}
}
}
}
}
/**
* 创建副标题
*
* @param workbook
*/
private void createSubHeads(XSSFWorkbook workbook, List subHeadList) {
XSSFCellStyle style = null;
int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2; // 副标题行数
int fristCol = 0;
int columnCount = 26;
String title = "";
XSSFRow row = null;
XSSFCell cell = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
/** 副标题每行放两个，一个居左，一个居右，所以除以2，多出来的一个，另起一行， */
for (int i = 0; i < rowCount; i++) {
row = workbook.getSheetAt(0).createRow(i + 1); // 创建一个行对象 ,加1是因为有一行头标题
for (int j = 0; j < 2; j++) {
if (subHeadList.size() <= (i * 2 + j)) break;
style = workbook.createCellStyle();
title = subHeadList.get(i * 2 + j).toString();
font.setFontHeightInPoints((short) 9); // 字号
style.setFont(font);
if (j == 0) {
/** 因为每行两个副标题，所以第一个标题占一半 */
fristCol = columnCount / 2;
cell = row.createCell(j); // 创建单元格
cell.setCellValue(title);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(i + 1, i + 1, 0, fristCol - 1)); // 合并单元格
} else {
/** 因为每行两个副标题，第一个标题占一半，第二个占剩下的所有单元格 */
fristCol = columnCount - (columnCount / 2) - (columnCount % 2);
cell = row.createCell(columnCount / 2); // 创建单元格
cell.setCellValue(title);
// 字号
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 水平居右
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(
new CellRangeAddress(i + 1, i + 1, fristCol, columnCount - 1)); // 合并单元格
}
}
}
}
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();
}
}
/**
* 指定セルの削除
*
* @param sheet シート
* @param startRow 開始行番号
* @param endRow 終了行番号
* @param startColumn 開始列番号
* @param endColumn 終了列番号
*/
public static void removeCell(
XSSFSheet sheet, int startRow, int endRow, int startColumn, int endColumn) {
assert sheet != null;
for (int nRow = startRow; nRow <= endRow; nRow++) {
XSSFRow row = sheet.getRow(nRow);
if (row != null) {
for (int nColumn = startColumn; nColumn <= endColumn; nColumn++) {
XSSFCell cell = row.getCell(nColumn);
if (cell != null) {
row.removeCell(cell);
}
}
}
}
}
private void fillRowWith(XSSFRow r, String[] data) {
XSSFCell c = null;
for (String s : data) {
if (c == null) c = r.createCell(0);
else c = r.createCell(c.getColumnIndex() + 1);
try {
double d = Double.valueOf(s);
c.setCellValue(d);
} catch (NumberFormatException e) {
c.setCellValue(s);
}
}
}
private int getColumnCountFromSheet() {
int lastRow = xssfSheet.getLastRowNum();
short lastCol = 0;
short lastCellInRow;
XSSFRow row = null;
for (int i = 0; i < lastRow; i++) {
row = xssfSheet.getRow(i);
lastCellInRow = row.getLastCellNum();
if (lastCellInRow > lastCol) {
lastCol = lastCellInRow;
}
}
return lastCol;
}
/**
* 上線は太線のセル行を探す
*
* @param nRow 行データ
* @return 有効列数
*/
public static int getRowForBold(XSSFSheet sheet, int nRow, int pageRowNum) {
int nRowIndex = nRow;
for (nRowIndex = nRow; nRowIndex > (nRow - pageRowNum); nRow--) {
XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow);
if (row != null) {
XSSFCell cell = row.getCell(0);
XSSFCellStyle styletmp = cell.getCellStyle();
short borderTopnum = styletmp.getBorderTop();
short borderBold = XSSFCellStyle.BORDER_MEDIUM;
if (styletmp.getBorderTop() == (XSSFCellStyle.BORDER_MEDIUM)) {
break;
}
}
}
return nRowIndex;
}
/**
* セルに設定された計算式を評価して値を取得する
*
* @param nRow 行番号
* @param nColumn 列番号
* @return セルの値
*/
public static Object getDataByEvaluateFormula(XSSFSheet sheet, int nRow, int nColumn) {
assert sheet != null;
XSSFRow row = getRowAnyway(sheet, nRow);
if (row != null) {
XSSFCell cell = row.getCell(nColumn);
if (cell != null) {
FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
if (eval != null) {
CellValue value = eval.evaluate(cell);
if (value != null) {
return value.getNumberValue();
}
}
}
}
return null;
}
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 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;
}
/**
* 创建标题，并设置字体、字号、加粗、颜色
*
* @param workbook
*/
private void setHeadCellFont(XSSFWorkbook workbook, String title) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFRow row = (XSSFRow) workbook.getSheetAt(0).createRow(0); // 创建一个行对象
XSSFCell cell = row.createCell(0); // 创建单元格
XSSFFont font = workbook.createFont(); // 创建字体对象
font.setFontName(HSSFFont.FONT_ARIAL); // 字体
font.setFontHeightInPoints((short) 16); // 字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
row.setHeightInPoints(23); // 设置行高23像素
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(0, 0, 0, 16)); // 四个参数分别是：起始行，结束行，起始列，结束列
cell.setCellValue(title); // 写入头标题
}
private void processRow(Element table, XSSFRow row, XSSFSheet sheet) {
Element tr = htmlDocumentFacade.createTableRow();
Iterator<Cell> cells = row.cellIterator();
if (row.isFormatted()) {
// TODO build row style...
}
if (row.getCTRow().getCustomHeight())
tr.setAttribute(
"style", "height:".concat(String.valueOf(row.getHeightInPoints())).concat("pt;"));
while (cells.hasNext()) {
Cell cell = cells.next();
if (cell instanceof XSSFCell) processCell(tr, (XSSFCell) cell);
}
table.appendChild(tr);
}
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();
}
}
/**
* @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));
}
}