private File writeSingleXLS() throws ReportException {
File file = null;
HSSFWorkbook wb = null;
OutputStream out = null;
try {
file = getReportFile();
wb = new HSSFWorkbook();
ReportDefineFrm definedReport = executeFrm.getDefinedReport();
String sheetName = definedReport.getReportName();
HSSFSheet sheet = wb.createSheet(sheetName);
sheet.setDisplayGridlines(false); // 不显示网格线
HSSFCellStyle dataStyle = createDataStyle(wb);
HSSFCellStyle headerStyle = createHeaderStyle(wb);
if (isFirstFile) {
writeDataHeader(sheet, headerStyle);
}
writeData2Excel(sheet, dataStyle, headerStyle);
autoReSizeSheet(sheet);
out = new FileOutputStream(file);
wb.write(out);
isFirstFile = false;
} catch (Throwable ex) {
Logger.logError(ex);
throw new ReportException(ex.getMessage());
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException ex) {
Logger.logError(ex);
}
}
return file;
}
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
}
/**
* ************************************************************************* Public methods
* ************************************************************************
*/
@Override
public void open(String filepath) throws Exception {
this.filepath = filepath;
String author = ini.getValue("Excel", "Author", "Toël Hartmann");
String keywords = ini.getValue("Excel", "Keywords", "");
wb = new HSSFWorkbook();
// Set some properties
wb.createInformationProperties();
wb.getSummaryInformation().setAuthor(author);
wb.getSummaryInformation().setKeywords(keywords);
wb.getSummaryInformation().setCreateDateTime(new Date());
// Create the cell style for column titles
titleStyle = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(font);
String sheetName = getConfigValue("Excel", "sheet", null);
if (sheetName == null || sheetName.isEmpty())
sheetName = FileUtils.getFileNameWithoutExtention(filepath);
sheet = wb.createSheet(sheetName);
if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) {
sheet.createFreezePane(0, 1);
}
}
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 void doService() throws Exception {
// String filePath = "D:\\home\\filsoft\\financelease\\birtReport" +
String filePath =
"\\\\"
+ LeaseUtil.getIPAddress()
+ "\\home\\filsoft\\financelease\\birtReport"
+ File.separator
+ "待补-逾期"
+ File.separator
+ DateUtil.dateToString(new Date(), "yyyy_MM_dd");
String fileName = "待补-逾期情况表.xls";
OutputStream out = null;
HSSFWorkbook wb = null;
Map<String, Object> paramMap = new HashMap<String, Object>();
try {
ReportDateTo reportDate = ReportDateUtil.getDateByDate(DateUtil.dateToStr(new Date()));
if (reportDate.getMonth() == 1) {
reportDate.setMonth((short) 12);
reportDate.setYear(reportDate.getYear() - 1);
} else {
reportDate.setMonth((short) (reportDate.getMonth() - 1));
}
reportDate =
ReportDateUtil.getDateByYearAndMonth(reportDate.getYear(), reportDate.getMonth());
File path = new File(filePath);
path.mkdirs();
File f = new File(path, fileName);
wb = new HSSFWorkbook();
// sheet1
HSSFSheet sheet1 = wb.createSheet("当月拨款案件待补情况");
List<Map<String, Object>> data4Of1 =
(List<Map<String, Object>>) this.queryForList("job.getDataForHr_lack", reportDate);
this.drawSheet(sheet1, data4Of1, getHeadByHardcode1());
// sheet2
HSSFSheet sheet2 = wb.createSheet("当月新增逾期91天以上案件");
List<Map<String, Object>> data4Of2 =
(List<Map<String, Object>>) this.queryForList("job.getDataForHr_dun", reportDate);
this.drawSheet(sheet2, data4Of2, getHeadByHardcode2());
out = new FileOutputStream(f);
wb.write(out);
// 发送Email
MailSettingTo mailSettingTo = new MailSettingTo();
mailSettingTo.setEmailAttachPath(f.getPath());
mailUtilService.sendMail(132, mailSettingTo);
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
if (out != null) {
out.flush();
out.close();
}
}
}
@Override
public void setAnchor(Object anchor) {
if (anchor instanceof String) {
sheet = workbook.getSheet((String) anchor);
if (sheet == null) sheet = workbook.createSheet((String) anchor);
} else if (anchor instanceof Integer) {
sheet = workbook.getSheetAt((Integer) anchor);
if (sheet == null) sheet = workbook.createSheet();
}
this.anchor = anchor;
}
@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;
}
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);
}
public void testShapeIds() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet();
HSSFPatriarch patriarch1 = sheet1.createDrawingPatriarch();
for (int i = 0; i < 2; i++) {
patriarch1.createSimpleShape(new HSSFClientAnchor());
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet1 = wb.getSheetAt(0);
patriarch1 = sheet1.getDrawingPatriarch();
EscherAggregate agg1 = HSSFTestHelper.getEscherAggregate(patriarch1);
// last shape ID cached in EscherDgRecord
EscherDgRecord dg1 = agg1.getEscherContainer().getChildById(EscherDgRecord.RECORD_ID);
assertEquals(1026, dg1.getLastMSOSPID());
// iterate over shapes and check shapeId
EscherContainerRecord spgrContainer = agg1.getEscherContainer().getChildContainers().get(0);
// root spContainer + 2 spContainers for shapes
assertEquals(3, spgrContainer.getChildRecords().size());
EscherSpRecord sp0 =
((EscherContainerRecord) spgrContainer.getChild(0)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1024, sp0.getShapeId());
EscherSpRecord sp1 =
((EscherContainerRecord) spgrContainer.getChild(1)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1025, sp1.getShapeId());
EscherSpRecord sp2 =
((EscherContainerRecord) spgrContainer.getChild(2)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1026, sp2.getShapeId());
}
public void beforeBody(TableModel model) {
logger.debug("XlsView.init()");
moneyFormat =
model
.getPreferences()
.getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.money");
if (StringUtils.isEmpty(moneyFormat)) {
moneyFormat = DEFAULT_MONEY_FORMAT;
}
percentFormat =
model
.getPreferences()
.getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.percent");
if (StringUtils.isEmpty(percentFormat)) {
percentFormat = DEFAULT_PERCENT_FORMAT;
}
wb = new HSSFWorkbook();
sheet = wb.createSheet();
wb.setSheetName(0, "Export Workbook");
styles = initStyles(wb);
ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short) 1);
ps.setFitWidth((short) 1);
createHeader(model);
}
public void addSheet(String name) {
if (name.length() > 30) {
name = name.substring(0, 25);
}
boolean found = false;
String tempName = name.replace("/", "-");
int i = 0;
while (true) {
if (i > 1000) {
break;
}
for (int j = 0; j < workbook.getNumberOfSheets(); j++) {
found = workbook.getSheetName(j).equalsIgnoreCase(tempName);
if (found) {
break;
}
}
if (!found) {
break;
} else {
i++;
tempName = name + " (" + i + ")";
}
}
activeSheet = workbook.createSheet(tempName);
nextRowId = 0;
}
public void testCreateCFRuleRecord() {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
CFRuleRecord record = CFRuleRecord.create(sheet, "7");
testCFRuleRecord(record);
// Serialize
byte[] serializedRecord = record.serialize();
// Strip header
byte[] recordData = new byte[serializedRecord.length - 4];
System.arraycopy(serializedRecord, 4, recordData, 0, recordData.length);
// Deserialize
record = new CFRuleRecord(TestcaseRecordInputStream.create(CFRuleRecord.sid, recordData));
// Serialize again
byte[] output = record.serialize();
// Compare
assertEquals("Output size", recordData.length + 4, output.length); // includes sid+recordlength
for (int i = 0; i < recordData.length; i++) {
assertEquals("CFRuleRecord doesn't match", recordData[i], output[i + 4]);
}
}
@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();
}
/** @Description: 创建所有的Sheet */
private static HSSFSheet[] getSheets(int num, String[] names) {
HSSFSheet[] sheets = new HSSFSheet[num];
for (int i = 0; i < num; i++) {
sheets[i] = wb.createSheet(names[i]);
}
return sheets;
}
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 testClearShapesForPatriarch() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createCellComment(new HSSFClientAnchor());
EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch);
assertEquals(agg.getShapeToObjMapping().size(), 6);
assertEquals(agg.getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 3);
patriarch.clear();
assertEquals(agg.getShapeToObjMapping().size(), 0);
assertEquals(agg.getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(agg.getShapeToObjMapping().size(), 0);
assertEquals(agg.getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
}
public void testRotation() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFSimpleShape rectangle =
patriarch.createSimpleShape(
new HSSFClientAnchor(0, 0, 100, 100, (short) 0, 0, (short) 5, 5));
rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
assertEquals(rectangle.getRotationDegree(), 0);
rectangle.setRotationDegree((short) 45);
assertEquals(rectangle.getRotationDegree(), 45);
rectangle.setFlipHorizontal(true);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);
assertEquals(rectangle.getRotationDegree(), 45);
rectangle.setRotationDegree((short) 30);
assertEquals(rectangle.getRotationDegree(), 30);
patriarch.setCoordinates(0, 0, 10, 10);
rectangle.setString(new HSSFRichTextString("1234"));
}
public void exportXLS(HttpServletResponse response) {
// 1.创建一个 workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// 2.创建一个 worksheet
HSSFSheet worksheet = workbook.createSheet("数据统计报表");
// 3.定义起始行和列
int startRowIndex = 0;
int startColIndex = 0;
// 4.创建title,data,headers
// 5.填充数据
// 6.设置reponse参数
String fileName = "Report.xls";
response.setHeader("Content-Disposition", "inline; filename=" + fileName);
// 确保发送的当前文本格式
response.setContentType("application/vnd.ms-excel");
// 7. 输出流
}
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");
}
}
}
/**
* @param SheetName excel里面的sheet的名字 （可以任意写，一般和excelname一样）
* @param response 要写的流
* @param HeadName 数据的表头 即列名
* @param data 数据 二维数组
* @return
* @throws IOException
*/
public static OutputStream creatExcel(
String SheetName, File file, List<String> HeadName, String[][] data, Integer[] ColumnWidth)
throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(SheetName);
CellStyle s = new CellStyle();
Font f12 = new Font();
HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12);
HSSFCellStyle style = ExcelUtil.getNewStyle(workbook, s, font12);
// sheet.setDefaultColumnWidth(50);
sheet.setDefaultRowHeight((short) 400);
sheet.setColumnWidth(0, 18 * 256);
OutputStream os = new FileOutputStream(file);
// 设置excel的表头
for (int i = 0; i < HeadName.size(); i++) {
ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style);
}
// 设置excel数据
for (int i = 0; i < data.length; i++) {
for (int j = 0; j < data[i].length; j++) {
ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style);
}
}
// 设置excel 表格的列宽
for (int i = 0; i < HeadName.size(); i++) {
sheet.setColumnWidth(i, ColumnWidth[i]);
}
workbook.write(os);
return os;
}
@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("导出成功！");
}
/** Sets up a test file */
private ByteArrayOutputStream setupRunFile(HSSFWorkbook book) throws Exception {
ByteArrayOutputStream stream = new ByteArrayOutputStream();
HSSFSheet sheet = book.createSheet("Test");
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue(10.5);
book.write(stream);
return stream;
}
private void initialize(Map<String, ?> cfg) {
workbook = new HSSFWorkbook();
if (cfg.containsKey("header")) header = (List<String>) cfg.get("header");
if (cfg.containsKey("type")) type = (List<Class>) cfg.get("type");
if (cfg.containsKey("anchor")) anchor = cfg.get("anchor");
if (anchor instanceof String) {
sheet = workbook.createSheet((String) anchor);
} else {
sheet = workbook.createSheet();
}
if (cfg.containsKey("dateTimePattern")) {
setDateTimePattern((String) cfg.get("dateTimePattern"));
} else {
setDateTimePattern(Environment.getDateTimePattern());
}
}
/**
* 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);
}
public void run() {
int pagesize = PAGESIZE;
try {
while (!m_spool.isHasError() && !m_spool.isEmpty()) {
if (linecounter >= pagesize) {
// 处理组合单元格
if (!mergeCells.isEmpty()) {
Region[] regs = new Region[mergeCells.size()];
mergeCells.values().toArray(regs);
int len = regs.length;
for (int i = 0; i < len; i++) m_sheet.addMergedRegion(regs[i]);
}
try {
flushToFile();
} catch (Exception e) {
Logger.error(e.getMessage(), e);
m_spool.setErrorMsg(e.getMessage());
} finally {
if (out != null)
try {
out.close();
} catch (IOException e) {
Logger.error(e.getMessage(), e);
}
}
m_workbook = new HSSFWorkbook(); // 创建工作薄
m_sheet = m_workbook.createSheet(); // 创建表单
mergeCells = new Hashtable(); // 存放所有组合单元格
styles = new Hashtable();
linecounter = 0;
sheetCounter++;
}
PrintCellData[] data = null;
data = (PrintCellData[]) m_spool.retrieve();
linecounter = linecounter + data.length; // 其实为单元格数目，非行数
fileUtil.exportSheet2(m_templateData, data, m_sheet, m_workbook, styles, mergeCells);
/* 每往Excel写入一行，进度条+1，已导出的行数+1 */
m_spool.setExportedLineCount(m_spool.getExportedLineCount() + 1);
stepProgressBar(1);
setProgressBarHintMsg();
}
} catch (Exception e) {
Logger.error(e.getMessage(), e);
m_spool.setErrorMsg(e.getMessage());
} catch (OutOfMemoryError e) {
Logger.error(e.getMessage(), e);
m_spool.setErrorMsg(
nc.ui.ml.NCLangRes.getInstance()
.getStrByID(
"10100108", "UPP10100108-000860") /*@res "内存不足，请修改Java Plugin参数，将内存调整大一些"*/);
}
}
@Override
public void buildExcelWorkBook(HSSFWorkbook workBook, Map<String, Object> map) {
Sheet sheet = workBook.createSheet();
generateHeaderRow(workBook, sheet, getColumnConfig());
if (getDataJSONArray() != null && getDataJSONArray().length() > 0) {
generateDatasRowByArray(sheet);
return;
}
}
private static void populateWorkbook(NodeList sheetList, Map cellStyles, HSSFWorkbook workbook)
throws ExcelTransformerException {
if (LOG.isLoggable(Level.FINE)) {
LOG.entering(
SimpleExcelRenderer.class.getName(),
"populateWorkbook",
String.valueOf(sheetList.getLength()));
}
for (int k = 0; k < sheetList.getLength(); k++) {
Element sheet = (Element) sheetList.item(k);
NodeList rowList = sheet.getElementsByTagName("row");
if (rowList.getLength() > 0) {
HSSFSheet hSheet = workbook.createSheet(sheet.getAttribute("name"));
for (int i = 0; i < rowList.getLength(); i++) {
Element row = (Element) rowList.item(i);
HSSFRow hRow = hSheet.createRow(i);
short cellCounter = 0;
NodeList cells = row.getChildNodes();
for (short j = 0; j < cells.getLength(); j++) {
Node cell = cells.item(j);
if (cell.getNodeType() == Node.ELEMENT_NODE && cell.getNodeName().equals("cell")) {
Element cellE = (Element) cell;
String value = XmlUtils.getElementText(cellE);
String style = cellE.getAttribute("style");
String colSpan = cellE.getAttribute("colspan");
int colSpanI = 0;
if (Utils.hasContent(colSpan)) {
colSpanI = Integer.parseInt(colSpan);
hSheet.addMergedRegion(
new Region(i, cellCounter, i, (short) (cellCounter + colSpanI - 1)));
}
HSSFCell hCell = hRow.createCell(cellCounter);
if (Utils.hasContent(style))
hCell.setCellStyle((HSSFCellStyle) cellStyles.get(style));
hCell.setCellValue(value);
cellCounter++;
}
}
}
}
}
if (LOG.isLoggable(Level.FINE)) {
LOG.exiting(SimpleExcelRenderer.class.getName(), "populateWorkbook");
}
}
/**
* @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 void testCorrectOrderInOptRecord() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor());
EscherOptRecord opt = HSSFTestHelper.getOptRecord(textbox);
String opt1Str = opt.toXml();
textbox.setFillColor(textbox.getFillColor());
EscherContainerRecord container = HSSFTestHelper.getEscherContainer(textbox);
EscherOptRecord optRecord = container.getChildById(EscherOptRecord.RECORD_ID);
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineStyle(textbox.getLineStyle());
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineWidth(textbox.getLineWidth());
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineStyleColor(textbox.getLineStyleColor());
assertEquals(opt1Str, optRecord.toXml());
} finally {
wb.close();
}
}
