// ����Excel
public String ExcelExport() throws Exception {
HttpServletRequest request = ServletActionContext.getRequest();
String ids = request.getParameter("ids");
List<Dise> list = new ArrayList<Dise>();
String[] array = ids.split(",");
int[] id = new int[array.length];
for (int i = 0; i < id.length; i++) {
Dise dise = DiseService.findById(Integer.valueOf(array[i]));
list.add(dise);
}
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("ѧ����Ϣ");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("ѧ��");
row.createCell(1).setCellValue("����");
row.createCell(2).setCellValue("����");
row.createCell(3).setCellValue("�Ա�");
row.createCell(4).setCellValue("��ַ");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
for (int i = 1; i <= list.size(); i++) {
Dise stu = list.get(i - 1);
row = sheet.createRow(i);
row.createCell(0).setCellValue(stu.getIds());
row.createCell(1).setCellValue(stu.getName());
row.createCell(2).setCellValue(stu.getA());
row.createCell(3).setCellValue(stu.getB());
row.createCell(4).setCellValue(stu.getC());
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
excelFile = new ByteArrayInputStream(baos.toByteArray());
baos.close();
return "excel";
}
public void make(File source, DataSet header, DataSet master) throws Exception {
this.header = header;
this.master = master;
if (header == null || master == null) throw new Exception("Dataset is empty");
long t = System.currentTimeMillis();
InputStream inp = new FileInputStream(source);
Workbook oldBook = WorkbookFactory.create(inp);
Sheet oldSheet = oldBook.getSheetAt(0);
Workbook newBook = new HSSFWorkbook();
Sheet newSheet = newBook.createSheet(oldSheet.getSheetName());
init(newBook);
process(oldSheet, newSheet);
File target = File.createTempFile("libra", ".xls");
target.deleteOnExit();
FileOutputStream fileOut = new FileOutputStream(target);
newBook.write(fileOut);
fileOut.close();
oldBook.close();
inp.close();
Desktop.getDesktop().open(target);
System.out.println(System.currentTimeMillis() - t);
}
/**
* write excel sheet, specified value to specified cell.
*
* @param sheetName excel sheet name
* @param row row index which to be changed
* @param col column index which to be changed
* @param value value to be put into cell
* @throws RuntimeException
*/
public void setExcelValue(String sheetName, int row, int col, String value) {
Workbook workBook = null;
try {
if (new File(fileName).exists()) {
workBook = getWorkBook(new FileInputStream(fileName), false);
} else {
workBook = getWorkBook(null, false);
}
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
xlSheet = workBook.createSheet(sheetName);
}
xlRow = xlSheet.getRow(row - 1);
if (xlRow == null) {
xlRow = xlSheet.createRow((short) row - 1);
}
xlCell = xlRow.getCell(col - 1);
if (xlCell == null) {
xlCell = xlRow.createCell(col - 1);
}
xlCell.setCellType(1); // set cell type as string
xlCell.setCellValue(value);
FileOutputStream fileOut = new FileOutputStream(fileName);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("set excel value failed:" + e.getMessage());
}
}
/*
* Don't hardcode US Locale for datetimes in Excel. Experiment a bit.
*/
public static void issue26() throws InvalidFormatException, IOException {
System.out.println("Testing issue26 =================================");
Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("Sheet1");
Row row = sheet.createRow(0);
// first cell
Cell cell0 = row.createCell(0);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
cell0.setCellStyle(cellStyle);
// second cell using another format with French locale
CellStyle cs2 = wb.createCellStyle();
String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
System.out.println(
"Converted pattern in FRENCH locale is: "
+ DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));
DataFormat df = wb.createDataFormat();
cs2.setDataFormat(df.getFormat(excelFormatPattern));
Cell cell1 = row.createCell(1);
cell1.setCellValue(new Date(1385903303326L));
cell1.setCellStyle(cs2);
FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
wb.write(out);
out.close();
System.out.println("Wrote /tmp/issue26_out.xlsx");
}
/**
* Called to update the embedded Excel workbook. As the format and structire of the workbook are
* known in advance, all this code attempts to do is write a new value into the first cell on the
* first row of the first worksheet. Prior to executing this method, that cell will contain the
* value 1.
*
* @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException Rather than use the specific
* classes (HSSF/XSSF) to handle the embedded workbook this method uses those defeined in the
* SS stream. As a result, it might be the case that a SpreadsheetML file is opened for
* processing, throwing this exception if that file is invalid.
* @throws java.io.IOException Thrown if a problem occurs in the underlying file system.
*/
public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
PackagePart pPart = null;
Iterator<PackagePart> pIter = null;
List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
pIter = embeddedDocs.iterator();
while (pIter.hasNext()) {
pPart = pIter.next();
if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
|| pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
// Get an InputStream from the pacage part and pass that
// to the create method of the WorkbookFactory class. Update
// the resulting Workbook and then stream that out again
// using an OutputStream obtained from the same PackagePart.
workbook = WorkbookFactory.create(pPart.getInputStream());
sheet = workbook.getSheetAt(SHEET_NUM);
row = sheet.getRow(ROW_NUM);
cell = row.getCell(CELL_NUM);
cell.setCellValue(NEW_VALUE);
workbook.write(pPart.getOutputStream());
}
}
// Finally, write the newly modified Word document out to file.
this.doc.write(new FileOutputStream(this.docFile));
}
}
public void writeData(Data data, boolean onlyNumbers) {
try {
File file = new File(path);
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream fOutputStream = new FileOutputStream(file);
org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook();
org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");
@SuppressWarnings("rawtypes")
ArrayList<ArrayList> dataList = data.getData();
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < dataList.get(i).size(); j++) {
row.createCell(j);
if (onlyNumbers) {
row.getCell(j).setCellValue(Double.parseDouble((String) dataList.get(i).get(j)));
} else {
row.getCell(j).setCellValue((String) dataList.get(i).get(j));
}
}
}
workbook.write(fOutputStream);
workbook.close();
fOutputStream.close();
workbook = null;
file = null;
sheet = null;
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
}
}
@RequestMapping(value = "exportExcel")
public String exportExcel(HttpServletRequest request, HttpServletResponse response)
throws Exception {
String operator = request.getParameter("filter_LIKES_operator");
String time = request.getParameter("filter_LIKES_time");
StringBuilder builder = new StringBuilder("from OptLog where 1=1");
if (StringUtils.isNotBlank(operator)) {
builder.append(" and operator like '%").append(operator).append("%'");
request.setAttribute("filter_LIKES_operator", operator);
}
if (StringUtils.isNotBlank(time)) {
builder.append(" and time like '%").append(time).append("%'");
request.setAttribute("filter_LIKES_time", time);
}
builder.append(" order by id desc");
List<OptLog> data = optLogManager.findOptLogOrderby(builder.toString());
// 生成Excel文件.
Workbook wb = new ExcelExporter().export("操作日志", data);
// 输出Excel文件.
// HttpServletResponse response = Struts2Utils.getResponse();
response.setContentType(ServletUtils.EXCEL_TYPE);
ServletUtils.setFileDownloadHeader(response, "操作日志.xls");
wb.write(response.getOutputStream());
response.getOutputStream().flush();
return null;
}
@RequestMapping(value = "/excel", method = RequestMethod.GET)
public void excel(HttpServletResponse response) throws Exception {
Workbook wb = new HSSFWorkbook();
// CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
org.apache.poi.ss.usermodel.Row row = sheet.createRow((short) 0);
row = sheet.createRow((short) 1);
row.createCell(0).setCellValue("Mã");
row.createCell(1).setCellValue("Tên");
row.createCell(2).setCellValue("Mã Path");
row.createCell(3).setCellValue("Tên path");
row.createCell(4).setCellValue("Cấp danh mục");
int i = 1;
List<Category> listAll = categoryService.listAll();
for (Category print : listAll) {
if (print.getName().length() > 19) {
i++;
row = sheet.createRow((short) i);
row.createCell(0).setCellValue(String.valueOf(print.getId()));
row.createCell(1).setCellValue(String.valueOf(print.getName()));
row.createCell(2).setCellValue(String.valueOf(print.getPath()));
String path = "";
for (String categoryId : print.getPath()) {
Category get = categoryService.get(categoryId);
path += get.getName() + " >>";
}
row.createCell(3).setCellValue(String.valueOf(path));
row.createCell(4).setCellValue(String.valueOf(print.getLevel()));
}
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=Danh-sach-danh-muc.xls");
wb.write(response.getOutputStream());
}
private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheet1");
CellStyle headerStyle = getHeaderStyle(wb);
CellStyle firstCellStyle = getFirsetCellStyle(wb);
CellStyle commonCellStyle = getCommonCellStyle(wb);
CellStyle amtCellStyle = getAmtCellStyle(wb);
for (int i = 0; i < LENGTH_9; i++) {
sheet1.setColumnWidth(i, STR_15 * STR_256);
}
// 表头
Row row = sheet1.createRow(0);
row.setHeightInPoints(STR_20);
Cell cell = headInfo(headerStyle, row);
if (detailList.size() == 0) {
row = sheet1.createRow(1);
cell = row.createCell(0);
cell.setCellValue(NO_RECORD);
} else {
fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
wb.write(outputStream);
InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
outputStream.close();
return inputStream;
}
/**
* 基类基于子类提供的相关参数数据, 生成JXLS报表
*
* @see #exportXlsForGrid(List, Sort, GroupPropertyFilter) 此方法中基于参数组装好相关的data数据后，调用此方法生成Excel响应
* @param dataMap
*/
protected void exportExcel(
String templateFileName, String exportFileName, Map<String, Object> dataMap) {
// 日期格式定义
dataMap.put("dateFormatter", new SimpleDateFormat(DateUtils.DEFAULT_DATE_FORMAT));
dataMap.put("timeFormatter", new SimpleDateFormat(DateUtils.DEFAULT_TIME_FORMAT));
HttpServletResponse response = ServletActionContext.getResponse();
InputStream fis = null;
OutputStream fos = null;
try {
Resource resource = new ClassPathResource("/template/xls/" + templateFileName);
logger.debug("Open template file inputstream: {}", resource.getURL());
fis = resource.getInputStream();
XLSTransformer transformer = new XLSTransformer();
// generate the excel workbook according to the template and
// parameters
Workbook workbook = transformer.transformXLS(fis, dataMap);
String filename = exportFileName;
filename = new String(filename.getBytes("GBK"), "ISO-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
fos = response.getOutputStream();
// output the generated excel file
workbook.write(fos);
} catch (Exception e) {
throw new WebException(e.getMessage(), e);
} finally {
IOUtils.closeQuietly(fis);
IOUtils.closeQuietly(fos);
}
}
public void inserirCliente(Cliente cliente) {
int count = 0;
boolean achou = false;
int ultimaLinha = wb.getSheetAt(0).getLastRowNum();
// verifica se existe um espaço em null entre as linhas da planilha
while (count <= ultimaLinha && !achou) {
if (wb.getSheetAt(0).getRow(count) == null) {
Row selectedRow = wb.getSheetAt(0).createRow(count);
this.createRow(selectedRow, cliente);
achou = true;
} else if (count == ultimaLinha) {
Row selectedRow = wb.getSheetAt(0).createRow(wb.getSheetAt(0).getLastRowNum() + 1);
this.createRow(selectedRow, cliente);
achou = true;
}
count++;
}
try {
this.fos = new FileOutputStream("RepositorioCliente.xls");
wb.write(fos);
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void removerCliente(String cpf) {
int count = 1;
boolean achou = false;
int ultimaLinha = wb.getSheetAt(0).getLastRowNum();
while (count < ultimaLinha && !achou) {
if (wb.getSheetAt(0).getRow(count).getCell(1).equals(cpf)) {
Row row = wb.getSheetAt(0).getRow(count);
wb.getSheetAt(0).removeRow(row);
achou = true;
} else {
count++;
}
}
try {
this.fos = new FileOutputStream("RepositorioCliente.xls");
wb.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* write the excel that have been made into a OutputStream
*
* @param os destination OutputStream
* @return ExcelMaker itself
*/
public ExcelMaker writeTo(OutputStream os)
throws NullPointerException, IllegalArgumentException, IOException {
checkNotNull(workbook, "workbook isn't created yet");
if (workbook.getNumberOfSheets() == 0) {
this.make();
}
workbook.write(os);
return this;
}
@Override
public void close() throws IOException {
try {
workbook.write(os);
} catch (IOException e) {
throw new MolgenisDataException("Exception writing to excel file", e);
}
os.close();
}
@Override
protected void closeFile() {
try {
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
private void saveWorkbook(Workbook resultWorkbook, String fileName) throws IOException {
String saveResultsProp = System.getProperty("saveResults");
if ("true".equalsIgnoreCase(saveResultsProp)) {
OutputStream os = new BufferedOutputStream(new FileOutputStream(fileName));
resultWorkbook.write(os);
os.flush();
os.close();
}
}
private void saveWorkbook(Workbook resultWorkbook, String fileName) throws IOException {
if (log.isInfoEnabled()) {
log.info("Saving " + fileName);
}
OutputStream os = new BufferedOutputStream(new FileOutputStream(fileName));
resultWorkbook.write(os);
os.flush();
os.close();
log.info("Output Excel saved to " + fileName);
}
private void writeFullDataToSpreadsheet(
OutputStream fos, Map<String, List<Map<String, Object>>> data) throws IOException {
LOG.debug("writeFullDataToSpreadsheet started" + data);
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();
// Sheet sheet = workbook.getSheetAt(0);
// LOG.debug("workbook.getActiveSheetIndex()" + workbook.getActiveSheetIndex());
// sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
int rowNum = sheet.getLastRowNum();
LOG.debug("sheet.getLastRowNum()" + sheet.getLastRowNum());
for (String methodName : data.keySet()) {
boolean isHeaderWritten = false;
Map<String, Integer> parameterIndexMap = new LinkedHashMap<String, Integer>();
for (Map<String, Object> methodData : data.get(methodName)) {
// rowNum increment by one to proceed with next record of the method.
LOG.debug("methodData.keySet().size" + methodData.keySet().size());
LOG.debug("methodData" + methodData);
if (!isHeaderWritten) {
int columnIndex = 0;
// Write the method name and parameter names in header.
writeDataToCell(sheet, rowNum, columnIndex++, methodName);
for (String parameterName : methodData.keySet()) {
writeDataToCell(sheet, rowNum, columnIndex, parameterName);
// capturing column index so that corresponding values will be placed at same column
parameterIndexMap.put(parameterName, columnIndex);
columnIndex++;
}
// incrementing row after writing header
rowNum++;
isHeaderWritten = true;
}
// Write the actual result and test status values.
if (isHeaderWritten) {
int columnIndex = 0;
// we need to put empty cell in first column as per easytest xls structure.
writeDataToCell(sheet, rowNum, columnIndex++, null);
for (String parameter : methodData.keySet()) {
writeDataToCell(
sheet, rowNum, parameterIndexMap.get(parameter), methodData.get(parameter));
}
rowNum++;
}
}
}
// Write the output to a file
workbook.write(fos);
LOG.debug("writeFullDataToSpreadsheet finished");
}
public static File writerFile(String[] title, List<String[]> content, String filePath)
throws IOException {
checkDir(filePath);
File f = new File(filePath);
if (!f.exists()) {
f.createNewFile();
}
FileOutputStream out = new FileOutputStream(f);
wb = new SXSSFWorkbook();
setStyle(wb);
Sheet sheet = wb.createSheet("sheet1");
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
int tCount = title.length;
for (int i = 0; i < tCount; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(title[i]);
sheet.setColumnWidth(i, 5000);
}
int rnum = 1;
for (String[] c : content) {
Row r = sheet.createRow(rnum);
for (int i = 0; i < c.length; i++) {
Cell cell = r.createCell(i);
cell.setCellStyle(contentStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
sheet.setColumnWidth(i, 5000);
String v = c[i];
if (v == null) {
v = "";
}
cell.setCellValue(v);
}
rnum++;
}
wb.write(out);
out.flush();
wb.close();
out.close();
return f;
}
/** @throws Exception */
public void makeXlsxFile() throws Exception {
String fileName = "test.xlsx";
FileOutputStream fileOut = new FileOutputStream(fileName);
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("test"); // シート作成
Row row = sheet.createRow((short) 0);
Cell cell = row.createCell(0);
cell.setCellValue("日本語は通る？");
wb.write(fileOut);
fileOut.close();
}
@RequestMapping("export")
public void exportData(HttpServletResponse response) {
try (OutputStream out = response.getOutputStream();
Workbook wb = bo.doExport(); ) {
response.setContentType("application/x-msexcel");
response.setHeader("Content-Disposition", "attachment; filename=record.xls");
wb.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* put dataList to excel sheets.
*
* @param sheetName excel sheet name.
* @param dataList data list to be parsed and put into excel sheets.
* @param rowNum row count of the sheet to be modified.
* @param ignoreRows rows to skip when put value.
* @param ignoreColumns columns to skip when put value.
* @throws RuntimeException
* @throws IllegalArgumentException
*/
public void putListToExcelWithFullIgnore(
String sheetName, List<String> dataList, int rowNum, int ignoreRows, int ignoreColumns) {
if (dataList.size() % rowNum != 0) {
LOG.error("dataList has wrong element count for excel!");
throw new IllegalArgumentException("dataList has wrong element count for excel!");
}
String value = null;
int index = 0;
final int colCount = dataList.size() / rowNum;
Workbook workBook = null;
try {
if (new File(fileName).exists()) {
workBook = getWorkBook(new FileInputStream(fileName), false);
} else {
workBook = getWorkBook(null, false);
}
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
xlSheet = workBook.createSheet(sheetName);
}
for (int j = ignoreRows; j < ignoreRows + rowNum; j++) {
xlRow = xlSheet.getRow(j);
if (xlRow == null) {
xlRow = xlSheet.createRow(j);
}
for (int i = ignoreColumns; i < ignoreColumns + colCount; i++) {
value = dataList.get(index);
xlCell = xlRow.getCell(i);
if (xlCell == null) {
xlCell = xlRow.createCell(i);
}
xlCell.setCellType(1);
if (value != null) {
xlCell.setCellValue(value);
}
index++;
}
}
FileOutputStream fileOut = new FileOutputStream(fileName);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("set excel value failed:" + e.getMessage());
}
}
/**
* 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于流
*
* @param os 输出流
* @param objs 对象列表
* @param clz 对象类型
* @param isXssf 是否是2007版本
*/
public void exportExcelByPath(
OutputStream os, List objs, Class clz, boolean isXssf, String message) {
try {
Workbook wb = handleExcel(objs, clz, isXssf, message);
wb.write(os);
} catch (FileNotFoundException e) {
e.printStackTrace();
logger.error(e);
} catch (IOException e) {
e.printStackTrace();
logger.error(e);
}
}
public String setTestData(String sheetName, int rowNum, int colNum, String data)
throws EncryptedDocumentException, InvalidFormatException, IOException {
FileInputStream fis = new FileInputStream(filepath);
Workbook wb = WorkbookFactory.create(fis);
Sheet sh = wb.getSheet(sheetName);
Row row = sh.getRow(rowNum);
Cell cel = row.createCell(colNum);
cel.setCellType(Cell.CELL_TYPE_STRING);
cel.setCellValue(data);
FileOutputStream fos = new FileOutputStream(filepath);
cel.setCellValue(data);
wb.write(fos);
wb.close();
return data;
}
public static void main(String[] args) throws Exception {
// The path to the documents directory.
String dataDir = Utils.getDataDir(ApacheHideUnHideCells.class);
InputStream inStream = new FileInputStream(dataDir + "workbook.xls");
Workbook workbook = WorkbookFactory.create(inStream);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
row.setZeroHeight(true);
FileOutputStream fileOut = new FileOutputStream(dataDir + "ApacheHideUnhideCells.xls");
workbook.write(fileOut);
fileOut.close();
System.out.println("Process Completed.");
}
public Reporting() {
try {
FileInputStream file = new FileInputStream(new File("reportTest.xls"));
workbook = new HSSFWorkbook(file);
Workbook wb = new HSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("Testbook.xls");
wb.write(fileOut);
fileOut.close();
} catch (IOException ex) {
ex.printStackTrace();
JOptionPane.showMessageDialog(null, ex);
// log Error
FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
}
}
@Override
public OutputStream createXlsData(
String metricName, String rrdFilename, long startTime, long endTime)
throws IOException, MetricsGraphException {
LOGGER.trace("ENTERING: createXlsData");
Workbook wb = new HSSFWorkbook();
createSheet(wb, metricName, rrdFilename, startTime, endTime);
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wb.write(bos);
bos.close();
LOGGER.trace("EXITING: createXlsData");
return bos;
}
public String uploadTemplateDownload() throws IOException {
// 1. 调用 Service 方法, 获取 Excel 文档对应的 Workbook 对象
Workbook workbook = employeeService.buildUploadExcelWorkbook();
// 2. 先写到一个指定的位置
String fileName =
ServletActionContext.getServletContext()
.getRealPath("/files/" + System.currentTimeMillis() + ".xls");
workbook.write(new FileOutputStream(fileName));
// 3. 确定输入流
inputStream = new FileInputStream(fileName);
this.contentType = "application/vnd.ms-excel";
this.contentLength = inputStream.available();
this.contentDisposition = "attachment;filename=employees.xls";
return "download-success";
}
public void generateReport() throws IOException {
System.out.println("Test Report Gen");
Sheet sheet = workbook.getSheet("sheet1");
try {
String strSql = "Select * from participant;";
Sql db = new Sql();
ResultSet rs = db.executeQuery(strSql);
ResultSetMetaData rsMeta = rs.getMetaData();
Row row = sheet.createRow(0);
Cell cell;
// get table columns which will act as title columns
for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
String colName = rsMeta.getColumnName(i);
int dataType = rsMeta.getColumnType(i);
cell = row.createCell(i - 1);
cell.setCellValue(colName);
}
int i = 1;
while (rs.next()) {
// create row to write data to
row = sheet.createRow(i);
// create cells for writing data to
for (int c = 1; c <= rsMeta.getColumnCount(); c++) {
cell = row.createCell(c - 1);
String colName = rsMeta.getColumnName(c);
cell.setCellValue(rs.getString(colName));
}
i++;
}
JOptionPane.showMessageDialog(null, "Excel Report of All Participants Generated");
} catch (SQLException ex) {
ex.printStackTrace();
// log Error
FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
}
/// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("reportTest.xls");
workbook.write(fileOut);
fileOut.close();
}
@ResponseBody
@RequestMapping(value = "/admin/orders/export.html", method = RequestMethod.GET)
public ResponseEntity<byte[]> export() throws Exception {
Map<String, Object> model = new HashMap<String, Object>();
List<Order> orders = orderRepository.export();
model.put("orders", orders);
XLSTransformer transformer = new XLSTransformer();
Resource resource = new ClassPathResource("/template/OrderExport.xlsx");
Workbook workbook = transformer.transformXLS(resource.getInputStream(), model);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
final HttpHeaders headers = new HttpHeaders();
headers.setContentType(
MediaType.valueOf("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
headers.set("Content-Disposition", "attachment;Filename=OrderExport.xlsx");
headers.setCacheControl("no-cache");
return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.OK);
}
