/**
* make the excel file based on headers and contents which should have been set
*
* @param sheetName specified sheet name
* @return ExcelMaker itself
* @throws IllegalArgumentException only support String, Integer, Long, Double, Date, null as cell
* data.
*/
public ExcelMaker make(String sheetName) throws IllegalArgumentException {
// check if headers and contents set
if (!contextReady()) {
throw new IllegalArgumentException("invalid headers or contents");
}
Sheet sheet = sheetName == null ? workbook.createSheet() : workbook.createSheet(sheetName);
// write the display headers as the 1st row in the sheet
int rowNum = 0;
Row headerRow = sheet.createRow(rowNum++);
if (displayHeaders != null) {
for (int cellNum = 0; cellNum < displayHeaders.size(); cellNum++) {
String header = displayHeaders.get(cellNum);
Cell cell = headerRow.createCell(cellNum);
cell.setCellValue(header);
}
} else { // if display headers not set, use headers as default
for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
String header = headers.get(cellNum);
Cell cell = headerRow.createCell(cellNum);
cell.setCellValue(header);
}
}
// parse each Object in content list and write as a row
for (Object content : contents) {
Map<String, Object> headerMap =
objectMapper.convertValue(content, new TypeReference<HashMap<String, Object>>() {});
Row row = sheet.createRow(rowNum++);
// only find properties that declared by headers
for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
String header = headers.get(cellNum);
Cell cell = row.createCell(cellNum);
Object obj = headerMap.get(header);
if (obj == null) {
obj = "";
}
if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Integer) {
cell.setCellValue((Integer) obj);
} else if (obj instanceof Long) {
cell.setCellValue((Long) obj);
} else if (obj instanceof Double) {
cell.setCellValue((Double) obj);
} else if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else {
throw new IllegalArgumentException("unsupported cell type");
}
}
}
return this;
}
private void write() {
sheet = workbook.createSheet("Inventory");
int col = header(1, true);
header(col, false);
data(1, filterByInputType(true));
data(col, filterByInputType(false));
}
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);
}
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;
}
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();
}
}
/*
* 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");
}
/**
* 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());
}
}
@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());
}
// ����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";
}
@Override
public ExcelSheetWriter createWritable(
String entityName,
Iterable<AttributeMetaData> attributes,
AttributeWriteMode attributeWriteMode) {
Sheet poiSheet = workbook.createSheet(entityName);
return new ExcelSheetWriter(poiSheet, attributes, attributeWriteMode, cellProcessors);
}
protected Sheet getCurrentSheet() {
synchronized (syncObj) {
if (sheet == null) {
Workbook wb = getCurrentWorkbook();
sheet = wb.createSheet();
}
return sheet;
}
}
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;
}
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");
}
/**
* @see
* net.sourceforge.squirrel_sql.fw.gui.action.exportData.AbstractDataExportFileWriter#beforeWorking()
*/
@Override
protected void beforeWorking(File file) throws IOException {
if (_exportFormat == ExportFormat.EXPORT_FORMAT_XLS) {
this.workbook = new HSSFWorkbook(); // See https://gist.github.com/madan712/3912272
} else {
this.workbook =
new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
}
this.file = file;
this.sheet = workbook.createSheet("Squirrel SQL Export");
}
/** @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();
}
@Override
public Result populate(Workbook workbook) {
Sheet savingsTransactionSheet = workbook.createSheet("SavingsTransaction");
setLayout(savingsTransactionSheet);
Result result = officeSheetPopulator.populate(workbook);
if (result.isSuccess()) result = clientSheetPopulator.populate(workbook);
if (result.isSuccess()) result = extrasSheetPopulator.populate(workbook);
if (result.isSuccess()) result = populateLoansTable(savingsTransactionSheet);
if (result.isSuccess()) result = setRules(savingsTransactionSheet);
setDefaults(savingsTransactionSheet);
return result;
}
/**
* 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());
}
}
public void testWriteAndRead() throws ParseException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(2);
Cell cell = row.createCell(3);
CellHelper test = new CellHelper();
// Numeric
test.setCellValue(cell, 42.2);
Double numericResult = test.getCellValue(cell, Double.class);
assertEquals(42.2, numericResult);
// Date
test.setCellValue(cell, DateHelper.parseDate("2013-08-30"));
Date dateResult = test.getCellValue(cell, Date.class);
assertEquals(DateHelper.parseDate("2013-08-30"), dateResult);
// Boolean
test.setCellValue(cell, true);
Boolean booleanResult = test.getCellValue(cell, Boolean.class);
assertEquals(true, (boolean) booleanResult);
// Text
test.setCellValue(cell, "apple");
String stringResult = test.getCellValue(cell, String.class);
assertEquals("apple", stringResult);
// Null
test.setCellValue(cell, null);
Object nullResult = test.getCellValue(cell, Object.class);
assertEquals(null, nullResult);
}
/**
* Creates a new excel-file and creates the title colons. Be careful: if file already exists it
* will be overridden!!
*
* @param filename
*/
public void newTrainingFile(String filename, String language) throws IOException {
// create new file
Workbook wb = new HSSFWorkbook();
ls = LanguageSupportFactory.getLanguageSupport("en");
// create info and training sheet
infoSheet = wb.createSheet(LanguageSupportFactory.getInfoSheetName());
trainingSheet = wb.createSheet(ls.NameTrainingSheet());
// add version info
// Create a row and put some cells in it. Rows are 0 based.
Row row = infoSheet.createRow(0);
// Create a cell and put a value in it.
row.createCell(0).setCellValue("Version:");
row.createCell(1).setCellValue(this.VERSION);
row = infoSheet.createRow(1);
row.createCell(0).setCellValue("Language:");
row.createCell(1).setCellValue(this.LANGUAGE);
// add training titles
row = trainingSheet.createRow(0);
row.createCell(COL_MODULE_NUMBER).setCellValue(ls.ModuleNumberTitle());
row.createCell(COL_MODULE).setCellValue(ls.ModuleTitle());
row.createCell(COL_TOPIC).setCellValue(ls.TopicTitle());
row.createCell(COL_ACTION).setCellValue(ls.ActionTitle());
row.createCell(COL_DURATION_ACTION).setCellValue(ls.DurationActionTitle());
row.createCell(COL_SELECTED).setCellValue(ls.SelectedTitle());
row.createCell(COL_DURATION_TOPIC).setCellValue(ls.DurationTopicTitle());
row.createCell(COL_DURATION_MOUDLE).setCellValue(ls.DurationModuleTitle());
row.createCell(COL_TARGET_GROUPS).setCellValue(ls.TargetGroupsTitle());
row.createCell(COL_DURATION_TRAINING).setCellValue(ls.DurationTrainingTitle());
save(filename, wb);
}
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.");
}
/**
* @param wb
* @param reviewerSheetName
*/
public static void create(Workbook wb, String sheetName) {
int sheetNum = wb.getSheetIndex(sheetName);
if (sheetNum >= 0) {
wb.removeSheetAt(sheetNum);
}
Sheet sheet = wb.createSheet(sheetName);
CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
Row row = sheet.createRow(0);
for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
sheet.setColumnWidth(i, COL_WIDTH * 256);
sheet.setDefaultColumnStyle(i, defaultStyle);
Cell cell = row.createCell(i);
cell.setCellStyle(headerStyle);
}
}
public static void create(Workbook wb, String sheetName) {
int sheetNum = wb.getSheetIndex(sheetName);
if (sheetNum >= 0) {
wb.removeSheetAt(sheetNum);
}
Sheet sheet = wb.createSheet(sheetName);
CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
Row row = sheet.createRow(0);
for (int i = 0; i < HEADER_TITLES.length; i++) {
sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
sheet.setDefaultColumnStyle(i, defaultStyle);
Cell cell = row.createCell(i);
cell.setCellStyle(headerStyle);
cell.setCellValue(HEADER_TITLES[i]);
}
}
public boolean CreateExcel(String path) {
try {
wb = new HSSFWorkbook(); // 创建新的Excel工作簿
sheet = wb.createSheet("民政婚姻登记");
Row row = sheet.createRow(3);
Cell cell = row.createCell(0);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(1);
// OutputStreamReader in = new OutputStreamReader(new FileOutputStream(path),"UTF-8");
fileOut = new FileOutputStream(path);
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
System.out.println("CreateExcel() ERRO\n");
return false;
}
return true;
}
public void printStatSpreadSheed() {
try {
// create workbook
workbook = new XSSFWorkbook();
FileOutputStream fileOut =
new FileOutputStream(new File(saveDirectory, outputName + ".xlsx"));
// create a worksheet
Sheet sheet0 = workbook.createSheet("Contamination stats");
sheet0.createFreezePane(0, 1, 0, 1);
// create row, one for each item plus header
// Row[] rows = new Row[]
} catch (Exception e) {
System.err.println("\nProblem printing the spreadsheet!");
e.printStackTrace();
System.exit(1);
}
}
public void appendToFile(Map<String, Double> content, String name) {
Sheet sheet = null;
sheet = workbook.createSheet();
Row head = sheet.createRow(0);
head.createCell(0).setCellValue(name);
head.createCell(1).setCellValue("Сумма");
int index = 1;
double sum = 0;
for (Map.Entry<String, Double> entry : content.entrySet()) {
Row row = sheet.createRow(index);
row.createCell(0).setCellValue(entry.getKey());
row.createCell(1).setCellValue(entry.getValue());
sum += entry.getValue();
index++;
}
Row sumRow = sheet.createRow(index);
sumRow.createCell(0).setCellValue("Итого");
sumRow.createCell(1).setCellValue(sum);
}
public void addSheet(
String sheetName, List<Object[]> bodyData, int[] entryTypes, String[] header) {
Sheet addedSheet = workbook.createSheet(sheetName);
createHeaderRow(addedSheet.createRow(0), header);
// goes through each entry in the list creating the rows
for (int index = 0; index < bodyData.size(); index++) {
createBodyRow(
addedSheet.createRow(index + 1),
bodyData.get(index),
entryTypes); // the 1 accounts for the header
}
// it now auto-sizes the columns
for (int column = 0; column <= header.length; column++) {
addedSheet.autoSizeColumn(column);
}
}
public static void prepareSurvey() {
Map<Integer, SentimentSentence> orderToSentence =
importOrderToSentence(
PreprocessingForDocumentVector.PYTHON_WORKSPACE + "order-to-sentence.txt");
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("survey");
int count = 0;
while (count <= orderToSentence.size()) {
Row row = sheet.createRow(count);
Cell cellReviewId = row.createCell(0);
Cell cellSentenceId = row.createCell(1);
Cell cellSentence = row.createCell(2);
Cell cellRating = row.createCell(3);
if (count == 0) {
cellReviewId.setCellValue("review id");
cellSentenceId.setCellValue("sentence id");
cellSentence.setCellValue("sentence");
cellRating.setCellValue("Rating (1, 2, 3 or 4 stars)");
} else {
SentimentSentence sentence = orderToSentence.get(count - 1);
cellReviewId.setCellValue(sentence.getReviewId());
cellSentenceId.setCellValue(sentence.getId());
cellSentence.setCellValue(sentence.getSentence());
}
++count;
}
Path outputPath =
Paths.get(PreprocessingForDocumentVector.PYTHON_WORKSPACE + "sentiment_survey.xlsx");
try {
Files.deleteIfExists(outputPath);
wb.write(Files.newOutputStream(outputPath, StandardOpenOption.CREATE));
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void overrideFlushedRows() throws IOException {
Workbook wb = new SXSSFWorkbook(3);
try {
Sheet sheet = wb.createSheet();
sheet.createRow(1);
sheet.createRow(2);
sheet.createRow(3);
sheet.createRow(4);
thrown.expect(Throwable.class);
thrown.expectMessage(
"Attempting to write a row[1] in the range [0,1] that is already written to disk.");
sheet.createRow(1);
} finally {
wb.close();
}
}
public ArquivoCliente() {
count = 1;
if (!new File("RepositorioCliente.xls").exists()) {
// cria planilha
wb = new HSSFWorkbook();
// planilha
sheetExcel = wb.createSheet();
Row row = sheetExcel.createRow(0);
String array[] = {
"Nome",
" CPF",
" Email ",
" RG ",
" Data de nascimento ",
" Endereco ",
"CNH",
"Placa do carro"
};
for (int i = 0; i < array.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(array[i]);
}
try {
this.fos = new FileOutputStream("RepositorioCliente.xls");
wb.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
this.fis = new FileInputStream("RepositorioCliente.xls");
wb = new HSSFWorkbook(fis);
fis.close();
} catch (IOException e) {
}
}
}
private Workbook createExcel(List<ConfirmationRecord> dataList) {
InputStream templateExcelConf = null;
Workbook wb = null;
int contentRow = 5;
try {
Date currentCycle = null;
templateExcelConf =
Thread.currentThread()
.getContextClassLoader()
.getResourceAsStream(TEMPLATE_EXCEL_CONF_REPORT_PATH);
wb = WorkbookFactory.create(templateExcelConf);
Sheet tempSheet = wb.getSheetAt(0);
Sheet newSheet = null;
// Excel Processing below here
for (ConfirmationRecord conf : dataList) {
if (maxCycleFrom == null || maxCycleFrom.compareTo(conf.getCycleFrom()) < 0)
maxCycleFrom = conf.getCycleFrom();
if (maxCycleTo == null || maxCycleTo.compareTo(conf.getCycleTo()) < 0)
maxCycleTo = conf.getCycleTo();
if (currentCycle == null || currentCycle.compareTo(conf.getCycleFrom()) != 0) {
currentCycle = conf.getCycleFrom();
newSheet = wb.createSheet(getSheetName(conf.getCycleFrom(), conf.getCycleTo()));
headerProcess(tempSheet, newSheet, conf.getCycleFrom(), conf.getCycleTo());
contentRow = 5;
}
contentProcess(tempSheet, newSheet, contentRow, conf);
contentRow++;
}
return wb;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
