// 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;
}
/**
* 罫線スタイルの<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); // 行高設定
}
/**
* @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);
}
}
}
}
}
public static String getCellData(int RowNum, int ColNum) throws Exception {
try {
Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
int dataType = Cell.getCellType();
if (dataType == 3) {
return "";
} else {
String CellData = Cell.getStringCellValue();
return CellData;
}
} catch (Exception e) {
System.out.println(e.getMessage());
throw (e);
}
}
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();
}
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;
}
/**
* 指定行を0で埋める
*
* @param sheet 編集対象シート
* @param nRow 行番号
* @param nStartColumn 開始列番号
* @param nEndColumn 終了列番号
*/
public static void setZero(XSSFSheet sheet, int nRow, int nStartColumn, int nEndColumn) {
XSSFRow row = getRowAnyway(sheet, nRow);
for (int nIndex = nStartColumn; nIndex <= nEndColumn; nIndex++) {
XSSFCell cell = getCellAnyway(row, nIndex);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue((double) 0);
}
}
/**
* 全てのシートの先頭セルにフォーカスをあてる
*
* @param workbook ワークブック
*/
public static void setFocusFistCell(XSSFWorkbook workbook) {
assert workbook != null;
for (int nIndex = 0; nIndex < workbook.getNumberOfSheets(); nIndex++) {
XSSFSheet sheet = workbook.getSheetAt(nIndex);
XSSFCell cell = getFirstCell(sheet);
assert cell != null;
cell.setAsActiveCell();
}
}
// 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;
}
@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();
}
/**
* セルの縮小して全体を表示する
*
* @param nRow 行データ
* @return 有効列数
*/
public static void setShrinkToFitForCell(XSSFWorkbook wb, XSSFSheet sheet, int nRow, int nCol) {
XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow);
XSSFCell cell = OoxmlUtil.getCellAnyway(row, nCol);
CellStyle styletmp = cell.getCellStyle();
CellStyle newStyletmp = wb.createCellStyle();
newStyletmp.cloneStyleFrom(styletmp);
newStyletmp.setWrapText(false); // 折り返して全体を表示する
newStyletmp.setShrinkToFit(true); // 縮小して全体を表示する
cell.setCellStyle(newStyletmp);
}
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 List<String> getColumnNames(String sheetname) {
XSSFSheet worksheet = _workbook.getSheet(sheetname);
Iterator<Cell> cellIterator = worksheet.getRow(0).cellIterator();
List<String> colnames = new ArrayList<String>();
while (cellIterator.hasNext()) {
XSSFCell cell = (XSSFCell) cellIterator.next();
colnames.add(cell.getStringCellValue());
}
return colnames;
}
/**
* 合并单元格后给合并后的单元格加边框
*
* @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);
}
}
}
public static void setCellValueOfCnX(XSSFCell cell, Object value) {
if (value instanceof String) {
cell.setCellValue(value.toString());
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
} else if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value == null) {
cell.setCellValue("");
} else {
cell.setCellValue(value.toString());
}
}
/**
* 列方向のセルの値を合算する
*
* @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;
}
/**
* セルの値を取得する
*
* @param row 行データ
* @param nColumn 列番号
* @return セルの値
*/
public static Object getData(XSSFRow row, int nColumn) {
if (row != null) {
XSSFCell cell = row.getCell(nColumn);
if (cell != null) {
if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
return cell.getNumericCellValue();
} else if (XSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
return cell.getStringCellValue();
}
}
}
return null;
}
/** 创建一个副标题 */
private void createSubHeadCell(
XSSFWorkbook workbook,
XSSFRow row,
XSSFCellStyle style,
String title,
int rowIndex,
int colIndex) {
XSSFCell cell = row.createCell(colIndex); // 创建单元格
cell.setCellValue(title);
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 1)); // 合并单元格
cell.setCellStyle(style);
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成 セル結合
*
* @param workbook ワークブック
* @param sheet シート
* @param nRowStart 開始行
* @param nRowEnd 終了行
* @param nColumnStart 開始列
* @param nColumnEnd 終了列
* @param style 罫線style
*/
public static void setMerger(
XSSFWorkbook workbook,
XSSFSheet sheet,
int nRowStart,
int nRowEnd,
int nColumnStart,
int nColumnEnd,
XSSFCellStyle style) {
assert sheet != null;
sheet.addMergedRegion(new CellRangeAddress(nRowStart, nRowEnd, nColumnStart, nColumnEnd));
XSSFRow row = getRowAnyway(sheet, nRowStart);
XSSFCell cell = getCellAnyway(row, nColumnStart);
cell.setCellStyle(style);
}
// returns the data from a cell
public String getCellData(String sheetName, int colNum, int rowNum) {
try {
if (rowNum <= 0) return "";
int index = workBook.getSheetIndex(sheetName);
if (index == -1) return "";
sheet = workBook.getSheetAt(index);
row = sheet.getRow(rowNum - 1);
if (row == null) return "";
cell = row.getCell(colNum);
if (cell == null) return "";
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.MONTH) + 1 + "/" + cal.get(Calendar.DAY_OF_MONTH) + "/" + 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 " + colNum + " does not exist in xls";
}
}
@Override
public void close() {
try {
int nbS = wb.getNumberOfSheets();
for (int i = 0; i < nbS; i++) {
XSSFSheet s = wb.getSheetAt(i);
for (int j = 0; j < s.getRow(0).getLastCellNum(); j++) {
XSSFCell c = s.getRow(0).getCell(j);
c.getSheet().autoSizeColumn(c.getColumnIndex());
}
XSSFCell firstCell = s.getRow(0).getCell(0);
XSSFCell lastCell = s.getRow(0).getCell((int) s.getRow(0).getLastCellNum() - 1);
s.setAutoFilter(
new CellRangeAddress(
firstCell.getRowIndex(),
lastCell.getRowIndex(),
lastCell.getRowIndex(),
lastCell.getColumnIndex()));
}
wb.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
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++);
}
}
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++);
}
}
/**
* 创建副标题
*
* @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();
}
}
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);
}
}
}
/**
* 指定範囲にセルを新規作成
*
* @param sheet シート
* @param startRow 開始行番号
* @param endRow 終了行番号
* @param startColumn 開始列番号
* @param endColumn 終了列番号
* @param style セルスタイル
*/
public static void setStyle(
XSSFSheet sheet,
int startRow,
int endRow,
int startColumn,
int endColumn,
XSSFCellStyle style) {
for (int nRow = startRow; nRow <= endRow; nRow++) {
XSSFRow row = getRowAnyway(sheet, nRow);
for (int nColumn = startColumn; nColumn <= endColumn; nColumn++) {
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellStyle(style);
}
}
}
/**
* 上線は太線のセル行を探す
*
* @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 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); // 写入头标题
}