private void createSummerySheet() {
sheet0 = workbook.createSheet("Summary");
PrintSetup printSetup = sheet0.getPrintSetup();
printSetup.setLandscape(true);
sheet0.setFitToPage(true);
sheet0.setHorizontallyCenter(true);
// title row
Row titleRow = sheet0.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("File Health Report");
titleCell.setCellStyle(styles.get("title"));
sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
for (int i = 0; i < titles.length; i++) {
Row _row = sheet0.createRow(i + 1);
Cell headerCell = _row.createCell(0);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
_row.setHeightInPoints(20);
}
// finally set column widths, the width is measured in units of 1/256th
// of a character width
sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
}
private static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
// 报表标题字体
Font fontTitle = worksheet.getWorkbook().createFont();
fontTitle.setBoldweight((short) Font.BOLDWEIGHT_BOLD);
fontTitle.setFontHeight((short) 280);
// 标题单元格格式
HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleTitle.setWrapText(true);
cellStyleTitle.setFont(fontTitle);
HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
rowTitle.setHeight((short) 500);
HSSFCell cellTitle = rowTitle.createCell(startColIndex);
cellTitle.setCellValue("学生列表");
cellTitle.setCellStyle(cellStyleTitle);
worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); // 标题合并列
Date date = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
HSSFCell cellDate = dateTitle.createCell(startColIndex);
cellDate.setCellValue("这个报表创建于: " + dateFormat.format(date));
}
private void writeHeader(HSSFSheet sheet) {
this.rowCount = 0;
short column = 0;
// first row
HSSFRow row = sheet.createRow(rowCount++);
write(row, column++, "Project Name", style);
write(row, column++, project.getName());
// Second row
column = 0;
row = sheet.createRow(rowCount++);
write(row, column++, "Version", style);
write(row, column++, "Field", style);
write(row, column++, "Type", style);
// write locales
for (Locale locale : getLocales()) {
String displayStr = LanguageUtils.getShortDisplayString(locale, Locale.ENGLISH);
write(row, column++, displayStr, style);
}
assert rowCount == 2;
}
/**
* Builds the report title and the date header
*
* @param worksheet
* @param startRowIndex starting row offset
* @param startColIndex starting column offset
*/
public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
// Create font style for the report title
Font fontTitle = worksheet.getWorkbook().createFont();
fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
fontTitle.setFontHeight((short) 280);
// Create cell style for the report title
HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleTitle.setWrapText(true);
cellStyleTitle.setFont(fontTitle);
// Create report title
HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
rowTitle.setHeight((short) 500);
HSSFCell cellTitle = rowTitle.createCell(startColIndex);
cellTitle.setCellValue("Compensation Report");
cellTitle.setCellStyle(cellStyleTitle);
// Create merged region for the report title
worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));
// Create date header
HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
HSSFCell cellDate = dateTitle.createCell(startColIndex);
cellDate.setCellValue("This report was generated at " + new Date());
}
public static void writeExcel(String excelPath, String testCase) throws Exception {
/**
* create Excel file in excelPath build two sheet which names are "TestSummary" and testcase
* detailinfo
*/
FileOutputStream fos = new FileOutputStream(excelPath);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s1 = wb.createSheet();
wb.setSheetName(0, "TestSummary");
HSSFSheet s2 = wb.createSheet();
wb.setSheetName(1, testCase + "_DetailInfo");
HSSFRow s1row = s1.createRow(0);
HSSFRow s2row = s2.createRow(0);
s1row.createCell(0).setCellValue("Total TCs");
s1row.createCell(1).setCellValue("Run TCs");
s1row.createCell(2).setCellValue("Passed TCs");
s1row.createCell(3).setCellValue("Failure TCs");
s1row.createCell(4).setCellValue("NoRun TCs");
s2row.createCell(0).setCellValue("SubTestCase");
s2row.createCell(1).setCellValue("TestResult");
s2row.createCell(2).setCellValue("AssertPointCheck");
s2row.createCell(3).setCellValue("ScreenShot");
s2row.createCell(4).setCellValue("Processor");
wb.write(fos);
fos.close();
}
public static void writeExcel(List<TongJiModel> list) throws Exception {
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
int columnCount = 5;
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");
HSSFRow first = sheet.createRow(0); // 下标为0的行开始
String[] firstRow = new String[columnCount];
firstRow[0] = "姓名";
firstRow[1] = "组名";
firstRow[2] = "邀请投资次数";
firstRow[3] = "邀请投资总金额";
firstRow[4] = "邀请注册人数";
for (int i = 0; i < columnCount; i++) {
first.createCell(i).setCellValue(new HSSFRichTextString(firstRow[i]));
}
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(i + 1);
TongJiModel tongJiModel = list.get(i);
row.createCell(0).setCellValue(new HSSFRichTextString(tongJiModel.getName()));
row.createCell(1).setCellValue(new HSSFRichTextString(tongJiModel.getGroupName()));
row.createCell(2).setCellValue(new HSSFRichTextString(tongJiModel.getInvestAcount()));
row.createCell(3).setCellValue(new HSSFRichTextString(tongJiModel.getInvestSumAmount()));
row.createCell(4).setCellValue(new HSSFRichTextString(tongJiModel.getRegisteCount()));
}
FileOutputStream os = new FileOutputStream("/Users/pansen/Downloads/20160428.xls");
wb.write(os);
os.close();
}
public void testRemoveNewRow_bug46312() {
// To make bug occur, rowIndex needs to be >= ValueRecordsAggregate.records.length
int rowIndex = 30;
ValueRecordsAggregate vra = new ValueRecordsAggregate();
try {
vra.removeAllCellsValuesForRow(rowIndex);
} catch (IllegalArgumentException e) {
if (e.getMessage().equals("Specified rowIndex 30 is outside the allowable range (0..30)")) {
throw new AssertionFailedError("Identified bug 46312");
}
throw e;
}
if (false) { // same bug as demonstrated through usermodel API
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(rowIndex);
if (false) { // must not add any cells to the new row if we want to see the bug
row.createCell(0); // this causes ValueRecordsAggregate.records to auto-extend
}
try {
sheet.createRow(rowIndex);
} catch (IllegalArgumentException e) {
throw new AssertionFailedError("Identified bug 46312");
}
}
}
@SuppressWarnings("unchecked")
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
resp.setContentType("application/vnd.ms-excel");
resp.setHeader("Content-Disposition", "attachment;filename=Rezultati.xls");
OutputStream out = resp.getOutputStream();
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("results");
HSSFRow headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("Voting option");
headerRow.createCell(1).setCellValue("Votes received");
int t = 1;
for (VotingResultEntry vre :
(List<VotingResultEntry>) req.getSession().getAttribute("results")) {
HSSFRow row = sheet.createRow(t++);
row.createCell(0).setCellValue(vre.getName());
row.createCell(1).setCellValue(vre.getVotes());
}
hwb.write(out);
out.flush();
out.close();
}
@Override
public ModelAndView getReportTemplate() {
List<MonthlyTraffic> monthlyTrafficReports = monthlyTraffictDAO.getMonthlyTraffic(monthDate);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Monthly Traffic Report");
int rownum = 0;
Row row0 = sheet.createRow(rownum++);
row0.createCell(0).setCellValue("Source Country");
row0.createCell(1).setCellValue("Service");
row0.createCell(2).setCellValue("Destination Country");
row0.createCell(3).setCellValue("Total Call Minutes");
for (MonthlyTraffic mt : monthlyTrafficReports) {
Row row = sheet.createRow(rownum++);
row.createCell(0).setCellValue(mt.getFromCountry());
row.createCell(1).setCellValue(mt.getServiceName());
row.createCell(2).setCellValue(mt.getToCountry());
row.createCell(3).setCellValue(mt.getMinutesOfCalls());
}
Map<String, HSSFWorkbook> parameterMap = new HashMap<String, HSSFWorkbook>();
parameterMap.put("excelBook", workbook);
ModelAndView modelAndView = new ModelAndView();
modelAndView.addAllObjects(parameterMap);
return modelAndView;
}
@SuppressWarnings("deprecation")
public void Daochu() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("库存盘点表");
HSSFRow row = sheet.createRow((int) 0);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("快递编号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("目的地");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("入库日期");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("区号");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("排号");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("架号");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue("位号");
cell.setCellStyle(style);
cell = row.createCell((short) 7);
cell.setCellValue("中转中心");
cell.setCellStyle(style);
FileInputStream fis = new FileInputStream("src/main/java/data/save/instock.txt");
ObjectInputStream ois = new ObjectInputStream(fis);
@SuppressWarnings("unchecked")
List<InStoringpo> list = (List<InStoringpo>) ois.readObject();
ois.close();
InStoringpo[] sp = new InStoringpo[list.size()];
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((int) i + 1);
sp[i] = list.get(i);
// 第四步，创建单元格，并设置值
row.createCell((short) 0).setCellValue(sp[i].bianhao);
row.createCell((short) 1).setCellValue(sp[i].destination);
row.createCell((short) 2).setCellValue(sp[i].year + "-" + sp[i].month + "-" + sp[i].day);
row.createCell((short) 3).setCellValue(sp[i].quhao);
row.createCell((short) 4).setCellValue(sp[i].paihao);
row.createCell((short) 5).setCellValue(sp[i].jiahao);
row.createCell((short) 6).setCellValue(sp[i].weihao);
row.createCell((short) 7).setCellValue(sp[i].zhongzhuan);
}
try {
FileOutputStream fout = new FileOutputStream("C:/大作业代码/kucundaochu.xls");
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("导出成功！");
}
/**
* to build the response as excel file.
*
* @see org.springframework.web.servlet.view.document.AbstractExcelView#
* buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook,
* javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
@SuppressWarnings(AppConstants.SUPPRESS_WARNINGS_UNCHECKED)
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook myWorkBook,
HttpServletRequest arg2,
HttpServletResponse arg3)
throws Exception {
List<QuestionDTO> questionDTOs = (List<QuestionDTO>) model.get("questions");
HSSFSheet questionsSheet = myWorkBook.createSheet(AppConstants.EXCEL_SHEET_NAME);
HSSFRow headerRow = questionsSheet.createRow(0);
HSSFCellStyle wrapCellStyle = myWorkBook.createCellStyle();
wrapCellStyle.setWrapText(true);
wrapCellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY);
createNewCell(headerRow, QUESTIONID_CELLNUM, "Question ID", wrapCellStyle);
createNewCell(headerRow, QUESTION_CELLNUM, "Question", wrapCellStyle);
createNewCell(headerRow, ANSWER_CELLNUM, "Answer", wrapCellStyle);
createNewCell(
headerRow, CATEGORIES_CELLNUM, "Categories (Category seperated by comma)", wrapCellStyle);
HSSFRow dataRow = null;
if (!CoreUtil.isEmpty(questionDTOs)) {
int i = 0;
for (QuestionDTO questionDTO : questionDTOs) {
dataRow = questionsSheet.createRow(i + 1);
createNewCell(dataRow, QUESTIONID_CELLNUM, Long.toString(questionDTO.getId()), null);
createNewCell(dataRow, QUESTION_CELLNUM, questionDTO.getQuestion(), wrapCellStyle);
createNewCell(dataRow, ANSWER_CELLNUM, questionDTO.getAnswer(), wrapCellStyle);
createNewCell(
dataRow,
CATEGORIES_CELLNUM,
parseCategoryDTOs(questionDTO.getCategoryDTOs()),
wrapCellStyle);
// Increment row
i = i + 1;
}
}
questionsSheet.autoSizeColumn(QUESTIONID_CELLNUM);
questionsSheet.autoSizeColumn(QUESTION_CELLNUM);
questionsSheet.autoSizeColumn(ANSWER_CELLNUM);
questionsSheet.autoSizeColumn(CATEGORIES_CELLNUM);
questionsSheet.setDefaultColumnStyle(QUESTION_CELLNUM, wrapCellStyle);
questionsSheet.setDefaultColumnStyle(ANSWER_CELLNUM, wrapCellStyle);
questionsSheet.setDefaultColumnStyle(CATEGORIES_CELLNUM, wrapCellStyle);
}
/**
* Discription:[写入excel文件,可用于导出,修改excel文件.目前只产生xls文件,并不产生xlsx文件]
*
* @param sheetName 写入excel的sheet名称
* @param dataSet 要写入文件的内容(一个集合). 集合中存放符合javaBean格式的对象。 支持数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param out 要写入的文件流, 可用于导出, 或者写入硬盘文件
* @param map 要导出的字段。（格式：字段名 <-> 字段中文名）其中字段名应该是对应javaBean中的某属性。<br>
* 若该字段为公式的话，必须符合一定的格式：公式标识符formula公式名称&计算起始单元格列&计算终止单元格列&计算单元格行&分隔符（,:）&操作符<br>
* 如：SUM&A&C&2&,&- 它返回的公司是：SUM(A3,-C3)。具体参照： <code>com.integral.util.office.ExcelFormula
* </code>
* @param dateFormat 日期格式(可选)，默认：yyyy-MM-dd
* @author:[代超]
* @throws IOException
* @throws Exception
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
@SuppressWarnings("deprecation")
public void writExcelFile(
String sheetName, Collection<T> dataSet, OutputStream out, Map map, String dateFormat)
throws Exception {
if (dataSet == null || dataSet.size() < 1) {
return;
}
if (map == null || map.size() < 1) {
return;
}
if (dateFormat == null || "".equals(dateFormat.trim())) {
dateFormat = "yyyy-MM-dd";
}
sheetName = sheetName == null ? "" : sheetName;
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth(15);
// 设置表格的样式
CellStyle headerStyle = setRootSheetSysle(workbook);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
// 将第一行冻结
sheet.createFreezePane(1, 1);
if (map != null) {
Object[] obj = map.values().toArray();
for (int i = 0; i < obj.length; i++) {
HSSFCell cell = row.createCell(i);
String header = obj[i] == null ? "" : obj[i].toString();
cell.setCellStyle(headerStyle);
if (obj[i] != null && obj[i].toString().indexOf("formula") > -1) {
// 公式
header = obj[i].toString().replace("formula", "");
}
HSSFRichTextString text = new HSSFRichTextString(header);
cell.setCellValue(text);
}
}
// 遍历数据集合，产生数据行
Iterator<T> it = dataSet.iterator();
for (int i = 1; it.hasNext(); i++) {
row = sheet.createRow(i);
T t = it.next();
// BeanMap bm = new BeanMap(t);
Map b = PropertyUtils.describe(t);
Map c = new TreeMap();
// 排序
c.putAll(b);
writeRow(row, c, map, dateFormat, t);
}
workbook.write(out);
}
/**
* @see javax.servlet.http.HttpServlet#doGet( javax.servlet.http.HttpServletRequest,
* javax.servlet.http.HttpServletResponse)
* <p>Method requires 3 parameters: a, b and n. It creates a Microsoft excel file with n
* sheets, each sheet containing 2 columns. 1st Column contains numbers from a to b and the
* 2nd column consists of the 1st column number's power depending on the sheet number.
*/
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
Integer a = null;
Integer b = null;
Integer n = null;
try {
a = Integer.valueOf(req.getParameter("a"));
} catch (Exception e) {
req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
}
if (a < -100 || a > 100) {
req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
}
try {
b = Integer.valueOf(req.getParameter("b"));
} catch (Exception e) {
req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
}
if (b < -100 || b > 100) {
req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
}
try {
n = Integer.valueOf(req.getParameter("n"));
} catch (Exception e) {
req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
}
if (n < 1 || n > 5) {
req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
}
HSSFWorkbook hwb = new HSSFWorkbook();
for (int i = 0; i < n; i++) {
HSSFSheet sheet = hwb.createSheet("Sheet " + (i + 1));
HSSFRow rowhead = sheet.createRow(0);
rowhead.createCell(0).setCellValue("Value");
rowhead.createCell(1).setCellValue("Power " + (i + 1));
for (int j = a; j <= b; j++) {
HSSFRow row = sheet.createRow(j - a + 1);
row.createCell(0).setCellValue(j);
row.createCell(1).setCellValue(Math.pow(j, i + 1));
}
}
resp.setContentType("application/vnd.ms-excel; charset=UTF-8");
resp.setHeader("Content-Disposition", "inline");
ServletOutputStream fileOut = resp.getOutputStream();
hwb.write(fileOut);
fileOut.close();
}
public static boolean saveAsExcelFile(File file, List<StatementsEx> statementsExs) {
final HSSFWorkbook wb = new HSSFWorkbook();
boolean needToWrite = false;
for (StatementsEx statementsEx : statementsExs) {
final String title = statementsEx.title;
final Statements statements = statementsEx.statements;
assert (statements != null);
if (statements.getType() == Statement.Type.Unknown) {
continue;
}
needToWrite = true;
final HSSFSheet sheet = wb.createSheet(title);
// Do not obtain "type" through statements, as there is possible that
// statements is empty.
final List<String> strings =
Statement.typeToStrings(statements.getType(), statements.getGUIBundleWrapper());
final int columnCount = strings.size();
// First row. Print out table header.
{
final HSSFRow row = sheet.createRow(0);
for (int i = 0; i < columnCount; i++) {
row.createCell(i).setCellValue(new HSSFRichTextString(strings.get(i)));
}
}
final int rowCount = statements.size();
for (int i = 0; i < rowCount; i++) {
final HSSFRow row = sheet.createRow(i + 1);
for (int j = 0; j < columnCount; j++) {
// Value shouldn't be null, as we prevent atom with null value.
final Object value = statements.get(i).getAtom(j).getValue();
final HSSFCell cell = row.createCell(j);
POIUtils.invokeSetCellValue(cell, value);
}
}
}
if (needToWrite == false) {
return needToWrite;
}
boolean status = false;
FileOutputStream fileOut = null;
try {
fileOut = new FileOutputStream(file);
wb.write(fileOut);
status = true;
} catch (FileNotFoundException ex) {
log.error(null, ex);
} catch (IOException ex) {
log.error(null, ex);
} finally {
org.yccheok.jstock.gui.Utils.close(fileOut);
}
return status;
}
/**
* @param excel_name 生成的Excel文件路径+名称
* @param headList Excel文件Head标题集合
* @param field_list Excel文件Field标题集合
* @param dataList Excel文件数据内容部分
* @throws Exception
*/
public static void createExcel(
String excel_name,
List<String> headList,
List<String> fieldList,
List<Map<String, Object>> dataList)
throws Exception {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表，其名为缺省值
// 如要新建一名为"效益指标"的工作表，其语句为：
// HSSFSheet sheet = workbook.createSheet("效益指标");
HSSFSheet sheet = workbook.createSheet();
// 在索引0的位置创建行（最顶端的行）
HSSFRow row = sheet.createRow(0);
// ===============================================================
for (int i = 0; i < headList.size(); i++) {
// 在索引0的位置创建单元格（左上端）
HSSFCell cell = row.createCell(i);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 在单元格中输入一些内容
cell.setCellValue(headList.get(i));
}
// ===============================================================
for (int n = 0; n < dataList.size(); n++) {
// 在索引1的位置创建行（最顶端的行）
HSSFRow row_value = sheet.createRow(n + 1);
Map<String, Object> dataMap = dataList.get(n);
// ===============================================================
for (int i = 0; i < fieldList.size(); i++) {
// 在索引0的位置创建单元格（左上端）
HSSFCell cell = row_value.createCell(i);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 在单元格中输入一些内容
cell.setCellValue(objToString(dataMap.get(fieldList.get(i))));
}
// ===============================================================
}
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(excel_name);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束，关闭文件
fOut.close();
// System.out.println("[" + excel_name + "]" + "文件生成...");
}
public static void writeDataToExcelFile(
final String commaDelimitedHeaderColumns,
final String commaDelimitedModelAttributes,
final List<?> modelList,
final String fileName) {
FileOutputStream out = null;
try {
String[] headerColumns = commaDelimitedHeaderColumns.split(COMMA);
String[] modelAttributes = commaDelimitedModelAttributes.split(COMMA);
HSSFWorkbook excelWorkBook = new HSSFWorkbook();
Map<String, CellStyle> styles = createStyles(excelWorkBook);
HSSFSheet excelSheet = excelWorkBook.createSheet();
HSSFRow excelHeaderRow;
HSSFCell excelHeaderCell;
HSSFRow excelRow;
HSSFCell excelCell;
excelHeaderRow = excelSheet.createRow(0);
for (int columnIndex = 0; columnIndex < headerColumns.length; columnIndex++) {
excelHeaderCell = excelHeaderRow.createCell(columnIndex);
excelHeaderCell.setCellValue(new HSSFRichTextString(headerColumns[columnIndex]));
excelHeaderCell.setCellStyle(styles.get("header"));
}
if (TGPObjectUtil.isDefined(modelList)) {
for (int rowIndex = 0; rowIndex < modelList.size(); rowIndex++) {
excelRow = excelSheet.createRow(rowIndex + 1);
for (int columnIndex = 0; columnIndex < modelAttributes.length; columnIndex++) {
excelCell = excelRow.createCell(columnIndex);
Object object = modelList.get(rowIndex);
Class<?> objectClass = object.getClass();
Field objectField = objectClass.getDeclaredField(modelAttributes[columnIndex]);
objectField.setAccessible(true);
excelCell.setCellValue(new HSSFRichTextString((String) objectField.get(object)));
}
}
}
for (int i = TGPProcurementConstants.TGP_INTEGER_ZERO; i < headerColumns.length; i++) {
excelSheet.autoSizeColumn(i);
}
out = new FileOutputStream(fileName);
excelWorkBook.write(out);
} catch (Exception e) {
LOGGER.error(e.toString());
} finally {
IOUtils.closeQuietly(out);
}
}
/**
* 生成文件
*
* @throws IOException
*/
public byte[] run() throws IOException {
// 初始化参数
initParameter();
// 实例化decorator
initDecorator();
// 初始化
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
sheet.setDefaultColumnWidth((short) 20); // 设置默认宽度
try {
wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16);
} catch (Exception ex) {
logger.error("生成表单时出错：怀疑是表单名导致。说明：poi对中文的支持不好，可能会有问题（仅表单名）如中文?－－中文字加英文标点的情况");
}
baseCellStyle = wb.createCellStyle();
baseCellStyle.setAlignment(align);
baseCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
dateCellStyle = wb.createCellStyle();
dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat));
// 创建标题行
HSSFRow row = sheet.createRow((short) 0);
// 创建标题
for (int i = 0; i < nameList.size(); i++) {
HSSFCell cell = row.createCell((short) i);
writeCell(cell, (String) nameList.get(i));
}
// 创建数据
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow((short) i + 1);
for (int j = 0; j < fieldList.size(); j++) {
HSSFCell cell = row.createCell((short) j);
Object value = getValue(dataList.get(i), (String) fieldList.get(j));
writeCell(cell, value);
}
}
// 写入文件
// FileOutputStream fileOut = new FileOutputStream(fileName);
ByteArrayOutputStream out = new ByteArrayOutputStream();
wb.write(out);
return out.toByteArray();
// fileOut.close();
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
/*
* if (workbook.getNumberOfSheets() == 0) { setUrl("/resources/xls/Product_Locator_API_Usage_Report"); renderMergedOutputModel(model, request, response); } else {
*/
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCellStyle ordinStyle = workbook.createCellStyle();
ordinStyle.setBorderRight(CellStyle.BORDER_THIN);
ordinStyle.setBorderLeft(CellStyle.BORDER_THIN);
HSSFCellStyle lineStyle = workbook.createCellStyle();
lineStyle.setBorderTop(CellStyle.BORDER_THIN);
short currentRow = 1;
Report report = (Report) model.get(SEARCH_LIST_KEY);
if (report != null) {
List<ReportAPIUsage> list = report.getRepotAPIUsageList();
HSSFRow row = sheet.getRow(0);
row.createCell(0)
.setCellValue("Date Range: " + report.getDateFrom() + " - " + report.getDateTo());
for (ReportAPIUsage object : list) {
currentRow++;
row = sheet.createRow(currentRow);
HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN);
cellBrName.setCellValue(object.getClientKey());
cellBrName.setCellStyle(ordinStyle);
HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN);
cellAnyPac.setCellValue(object.getClientName());
cellAnyPac.setCellStyle(ordinStyle);
HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN);
cellSpecPac.setCellValue(object.getCountOfQueries());
cellSpecPac.setCellStyle(ordinStyle);
}
currentRow++;
row = sheet.createRow(currentRow);
HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN);
cellBrName.setCellStyle(lineStyle);
HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN);
cellAnyPac.setCellStyle(lineStyle);
HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN);
cellSpecPac.setCellStyle(lineStyle);
}
/* } */
}
private HSSFRow createRow(HSSFSheet sheet, int rownum) {
HSSFRow newRow = sheet.createRow(rownum);
if (rownum == 0) {
newRow.setRowStyle(newStyle);
}
return newRow;
}
public Boolean insertRow(int startRow, int rows) {
Boolean flag = false;
sheet.shiftRows(startRow + 1, sheet.getLastRowNum(), rows, true, false);
startRow = startRow - 1;
for (int i = 0; i < rows; i++) {
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
short m;
startRow = startRow + 1;
sourceRow = sheet.getRow(startRow);
targetRow = sheet.createRow(startRow + 1);
targetRow.setHeight(sourceRow.getHeight());
for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
sourceCell = sourceRow.getCell(m);
targetCell = targetRow.createCell(m);
// targetCell.setEncoding(sourceCell.get);
targetCell.setCellStyle(sourceCell.getCellStyle());
targetCell.setCellType(sourceCell.getCellType());
}
}
flag = true;
return flag;
}
private HSSFRow getRow(int rowIndex) {
HSSFRow row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
return row;
}
@Override
public void writeRow(List<?> row) {
Row record = sheet.createRow(sheet.getLastRowNum() + 1);
for (int i = 0; i < row.size(); i++) {
Cell cell = record.createCell(i);
Object value = row.get(i);
if (value == null) {
cell.setCellValue("");
} else if (value instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) value);
} else if (value instanceof Number) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(converters.convert(value, Double.class));
} else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(dateCellStyle);
cell.setCellValue(converters.convert(value, Date.class));
} else if (value instanceof Boolean) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellValue((Boolean) value);
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(converters.convert(value, String.class));
}
}
}
/**
* 业务数据类型生成
*
* @param wb excel工作页
* @param btList 模型对象数组
*/
public void BusTypeWriter(HSSFWorkbook wb, List<PDMBusinessDataType> btList) {
HSSFSheet sheet = wb.getSheet(BT_SHEET);
for (int i = 0; i < btList.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
PDMBusinessDataType bt = btList.get(i);
// 1
HSSFCell cell = row.createCell(1);
cell.setCellValue(bt.getTypeName());
cell.setCellStyle(getTextStyle(wb));
// 2
cell = row.createCell(2);
cell.setCellValue(bt.getTypeChineseName());
cell.setCellStyle(getTextStyle(wb));
// 3
cell = row.createCell(3);
cell.setCellValue(bt.getStandardTypeName());
cell.setCellStyle(getTextStyle(wb));
// 4
cell = row.createCell(4);
cell.setCellValue(bt.getLength());
cell.setCellStyle(getTextStyle(wb));
// 5
cell = row.createCell(5);
cell.setCellValue(bt.getPrecision());
cell.setCellStyle(getTextStyle(wb));
// 6
cell = row.createCell(6);
cell.setCellValue(bt.getDefaultValue());
cell.setCellStyle(getTextStyle(wb));
// 7
cell = row.createCell(7);
cell.setCellValue(bt.getComment());
cell.setCellStyle(getTextStyle(wb));
}
}
/** Print header */
private void printHeader(HSSFSheet sheet) {
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(MEASURE);
row.createCell(1).setCellValue(TYPE);
row.createCell(2).setCellValue(DESCRIPTION);
row.createCell(3).setCellValue(VALUE);
}
private void addOrderHeader(final HSSFSheet sheet, final Locale locale) {
HSSFRow header = sheet.createRow(0);
HSSFCell cell0 = header.createCell(0);
cell0.setCellValue(
translationService.translate(
"qualityControls.qualityControl.report.product.number", locale));
xlsHelper.setCellStyle(sheet, cell0);
HSSFCell cell1 = header.createCell(1);
cell1.setCellValue(
translationService.translate(
"qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.number.label",
locale));
xlsHelper.setCellStyle(sheet, cell1);
HSSFCell cell2 = header.createCell(2);
cell2.setCellValue(
translationService.translate(
"qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.controlledQuantity.label",
locale));
xlsHelper.setCellStyle(sheet, cell2);
HSSFCell cell3 = header.createCell(3);
cell3.setCellValue(
translationService.translate(
"qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.rejectedQuantity.label",
locale));
xlsHelper.setCellStyle(sheet, cell3);
HSSFCell cell4 = header.createCell(4);
cell4.setCellValue(
translationService.translate(
"qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.acceptedDefectsQuantity.label",
locale));
xlsHelper.setCellStyle(sheet, cell4);
}
public void body(TableModel model, Column column) {
if (column.isFirstColumn()) {
rownum++;
cellnum = 0;
hssfRow = sheet.createRow(rownum);
}
String value = column.getCellDisplay();
if (StringUtils.isNumeric(value)) {
value = column.getValue() == null ? "" : column.getValue().toString();
}
value = value.replaceAll("\t", "").replaceAll("\n", "");
value = ExportViewUtils.parseXLS(value);
HSSFCell hssfCell = hssfRow.createCell(cellnum);
// modify by springside
// hssfCell.setEncoding(HSSFCell.ENCODING_UTF_16);
// end of modify by springside
if (column.isEscapeAutoFormat()) {
writeToCellAsText(hssfCell, value);
} else {
writeToCellFormatted(hssfCell, value);
}
cellnum++;
}
/**
* @param
* @return void
*/
private void generateColumn(
HSSFSheet sheet,
TableColumn tc,
int maxlevel,
int rownum,
int colnum,
HSSFCellStyle headerstyle) {
HSSFRow row = sheet.getRow(rownum);
if (row == null) row = sheet.createRow(rownum);
HSSFCell cell = row.createCell(colnum);
cell.setCellValue(tc.getDisplay());
if (headerstyle != null) cell.setCellStyle(headerstyle);
if (tc.isComplex()) {
CellRangeAddress address =
new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1);
sheet.addMergedRegion(address);
fillMergedRegion(sheet, address, headerstyle);
int cn = colnum;
for (int i = 0; i < tc.getChildren().size(); i++) {
if (i != 0) {
cn = cn + tc.getChildren().get(i - 1).getLength();
}
generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle);
}
} else {
CellRangeAddress address =
new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum);
sheet.addMergedRegion(address);
fillMergedRegion(sheet, address, headerstyle);
}
sheet.autoSizeColumn(colnum, true);
}
private void writeData2Excel(HSSFSheet sheet, HSSFCellStyle dataStyle, HSSFCellStyle headerStyle)
throws ContainerException {
RowSet searchResult = executeFrm.getSearchResult();
if (searchResult != null && !searchResult.isEmpty()) {
List<ReportViewFrm> definedViews = executeFrm.getDefinedViews();
int fieldCount = definedViews.size();
int dataCount = searchResult.getSize();
int xlsRowNum = 0;
for (int i = 0; i < dataCount; i++) {
Row row = searchResult.getRow(i);
xlsRowNum = i;
if (isFirstFile) {
xlsRowNum++;
}
HSSFRow xlsRow = sheet.createRow(xlsRowNum);
xlsRow.setHeightInPoints(18);
for (short j = 0; j < fieldCount; j++) {
ReportViewFrm viewFrm = definedViews.get(j);
String fieldName = viewFrm.getFieldName();
HSSFCell xlsCell = xlsRow.createCell(j);
xlsCell.setCellStyle(dataStyle);
HSSFRichTextString richText = new HSSFRichTextString(row.getStrValue(fieldName));
xlsCell.setCellValue(richText);
}
}
}
}
private void writeHeaderRow(Entity entity, HSSFSheet sheet) {
HSSFRow headerRow = sheet.createRow(0);
int colnum = 0;
for (Map.Entry<String, Object> component : getComponents(entity)) {
String componentName = component.getKey();
headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName));
ComponentDescriptor cd = entity.descriptor().getComponent(componentName);
PrimitiveType primitiveType;
if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor)
primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType();
else
throw new UnsupportedOperationException(
"Can only export simple type attributes, "
+ "failed to export "
+ entity.type()
+ '.'
+ cd.getName());
Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class);
String formatString = null;
if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern();
else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern();
else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern();
else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern();
else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern();
if (formatString != null) {
HSSFDataFormat dataFormat = workbook.createDataFormat();
CellStyle columnStyle = workbook.createCellStyle();
columnStyle.setDataFormat(dataFormat.getFormat(formatString));
sheet.setDefaultColumnStyle(colnum, columnStyle);
}
colnum++;
}
}
public void gerar(String caminho, int qtdLinhas, int qtdColunas) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("planilha um");
for (int i = 0; i < qtdLinhas; i++) {
HSSFRow row = sheet1.createRow(i);
HSSFCellStyle style = wb.createCellStyle();
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);
for (int j = 0; j < qtdColunas; j++) {
HSSFRichTextString strValor = new HSSFRichTextString("Linha: " + i + " Coluna " + j);
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
cell.setCellValue(strValor);
}
}
FileOutputStream stream = new FileOutputStream(caminho + "/planilha1.xls");
wb.write(stream);
}