protected void importMergedRegions(Sheet poiSheet, SSheet sheet) {
// merged cells
// reference RangeImpl.getMergeAreas()
int nMerged = poiSheet.getNumMergedRegions();
final SheetImpl sheetImpl = (SheetImpl) sheet;
for (int i = nMerged - 1; i >= 0; --i) {
final CellRangeAddress mergedRegion = poiSheet.getMergedRegion(i);
// ZSS-1114: any new merged region that overlapped with previous merged region is thrown away
final CellRegion r =
new CellRegion(
mergedRegion.getFirstRow(),
mergedRegion.getFirstColumn(),
mergedRegion.getLastRow(),
mergedRegion.getLastColumn());
final CellRegion overlapped = sheetImpl.checkMergedRegion(r);
if (overlapped != null) {
_logger.warning(
"Drop the region "
+ r
+ " which is overlapped with existing merged area "
+ overlapped
+ ".");
continue;
}
sheetImpl.addDirectlyMergedRegion(r);
}
}
public void testDeleteRows() throws SmartsheetException, IOException {
testAddRows();
smartsheet
.sheetResources()
.rowResources()
.deleteRows(sheet.getId(), new HashSet(Arrays.asList(newRows.get(0).getId())), true);
// clean up
deleteSheet(sheet.getId());
deleteSheet(copyToSheet.getId());
}
// ZSS-952
protected void importSheetDefaultColumnWidth(Sheet poiSheet, SSheet sheet) {
// reference XUtils.getDefaultColumnWidthInPx()
int defaultWidth =
UnitUtil.defaultColumnWidthToPx(
poiSheet.getDefaultColumnWidth(), ((AbstractBookAdv) book).getCharWidth()); // ZSS-1132
sheet.setDefaultColumnWidth(defaultWidth);
}
public void testGetRow() throws SmartsheetException, IOException {
smartsheet
.sheetResources()
.rowResources()
.getRow(sheet.getId(), newRows.get(0).getId(), null, null);
row =
smartsheet
.sheetResources()
.rowResources()
.getRow(
sheet.getId(),
newRows.get(0).getId(),
EnumSet.of(RowInclusion.COLUMNS, RowInclusion.COLUMN_TYPE),
EnumSet.of(ObjectExclusion.NONEXISTENT_CELLS));
assertNotNull(row);
}
/**
* POI AutoFilter.getFilterColumn(i) sometimes returns null. A POI FilterColumn object only exists
* when we have set a criteria on that column. For example, if we enable auto filter on 2 columns,
* but we only set criteria on 2nd column. Thus, the size of filter column is 1. There is only one
* FilterColumn object and its column id is 1. Only getFilterColumn(1) will return a FilterColumn,
* other get null.
*
* @param poiSheet source POI sheet
* @param sheet destination sheet
*/
protected void importAutoFilter(Sheet poiSheet, SSheet sheet) {
AutoFilter poiAutoFilter = poiSheet.getAutoFilter();
if (poiAutoFilter != null) {
CellRangeAddress filteringRange = poiAutoFilter.getRangeAddress();
SAutoFilter autoFilter =
sheet.createAutoFilter(new CellRegion(filteringRange.formatAsString()));
int numberOfColumn = filteringRange.getLastColumn() - filteringRange.getFirstColumn() + 1;
importAutoFilterColumns(poiAutoFilter, autoFilter, numberOfColumn); // ZSS-1019
}
}
public List<Object> readExcel(Workbook wb, Class clz, int readLine, int tailLine) {
Sheet sheet = wb.getSheetAt(0); // 取第一张表
List<Object> objs = null;
try {
Row row = sheet.getRow(readLine); // 开始行，主题栏
objs = new ArrayList<Object>();
Map<Integer, String> maps = getHeaderMap(row, clz); // 设定对应的字段顺序与方法名
if (maps == null || maps.size() <= 0)
throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行"); // 与order顺序不符
for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) { // 取数据
row = sheet.getRow(i);
Object obj = clz.newInstance(); // 调用无参结构
for (Cell c : row) {
int ci = c.getColumnIndex();
String mn = maps.get(ci).substring(3); // 消除get
mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
Map<String, Object> params = new HashMap<String, Object>();
if (!"enterDate".equals(mn)) c.setCellType(Cell.CELL_TYPE_STRING); // 设置单元格格式
else c.setCellType(Cell.CELL_TYPE_NUMERIC);
if (this.getCellValue(c).trim().equals("是")) {
BeanUtils.copyProperty(obj, mn, 1);
} else if (this.getCellValue(c).trim().equals("否")) {
BeanUtils.copyProperty(obj, mn, 0);
} else BeanUtils.copyProperty(obj, mn, this.getCellValue(c));
}
objs.add(obj);
}
} catch (InstantiationException e) {
e.printStackTrace();
logger.error(e);
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NumberFormatException e) {
e.printStackTrace();
logger.error(e);
}
return objs;
}
public void testAddRows() throws SmartsheetException, IOException {
// create sheet
sheet = smartsheet.sheetResources().createSheet(createSheetObject());
// get column
PaginationParameters parameters =
new PaginationParameters.PaginationParametersBuilder().setIncludeAll(true).build();
PagedResult<Column> wrapper =
smartsheet
.sheetResources()
.columnResources()
.listColumns(sheet.getId(), EnumSet.allOf(ColumnInclusion.class), parameters);
Column addedColumn1 = wrapper.getData().get(0);
Column addedColumn2 = wrapper.getData().get(1);
// Specify cell values for first row.
List<Cell> cellsA =
new Cell.AddRowCellsBuilder()
.addCell(addedColumn1.getId(), true)
.addCell(addedColumn2.getId(), "New status")
.build();
// Specify contents of first row.
row = new Row.AddRowBuilder().setCells(cellsA).setToBottom(true).build();
// Specify cell values for second row.
List<Cell> cellsB =
new Cell.AddRowCellsBuilder()
.addCell(addedColumn1.getId(), true)
.addCell(addedColumn2.getId(), "New status")
.build();
// Specify contents of first row.
Row rowA = new Row.AddRowBuilder().setCells(cellsB).setToBottom(true).build();
newRows =
smartsheet.sheetResources().rowResources().addRows(sheet.getId(), Arrays.asList(row, rowA));
List<Column> columns = wrapper.getData();
addedColumn = columns.get(1);
}
@Test
public void testUpdateRows() throws SmartsheetException, IOException {
// create sheet
Sheet sheet = smartsheet.sheetResources().createSheet(createSheetObject());
PaginationParameters parameters =
new PaginationParameters.PaginationParametersBuilder().setIncludeAll(true).build();
PagedResult<Column> wrapper =
smartsheet
.sheetResources()
.columnResources()
.listColumns(sheet.getId(), EnumSet.allOf(ColumnInclusion.class), parameters);
Column addedColumn1 = wrapper.getData().get(0);
Column addedColumn2 = wrapper.getData().get(1);
// Specify cell values for first row.
List<Cell> cellsA =
new Cell.AddRowCellsBuilder()
.addCell(addedColumn1.getId(), true)
.addCell(addedColumn2.getId(), "New status")
.build();
// Specify contents of first row.
Row row = new Row.AddRowBuilder().setCells(cellsA).setToBottom(true).build();
List<Row> newRows =
smartsheet.sheetResources().rowResources().addRows(sheet.getId(), Arrays.asList(row));
// Updated cells //correct
List<Cell> cellsB =
new Cell.UpdateRowCellsBuilder()
.addCell(addedColumn1.getId(), true)
.addCell(addedColumn2.getId(), "Updtaed status")
.build();
Row rowB = new Row.UpdateRowBuilder().setCells(cellsB).setRowId(newRows.get(0).getId()).build();
List<Row> updatedRows =
smartsheet.sheetResources().rowResources().updateRows(sheet.getId(), Arrays.asList(rowB));
assertNotNull(updatedRows);
deleteSheet(sheet.getId());
}
public void testCopyRow() throws SmartsheetException, IOException {
// Create new sheet to copy to
copyToSheet = smartsheet.sheetResources().createSheet(createSheetObject());
CopyOrMoveRowDestination destination =
new CopyOrMoveRowDestination.InsertCopyOrMoveRowDestinationBuilder()
.setSheetId(copyToSheet.getId())
.build();
CopyOrMoveRowDirective copyOrMoveRowDirective =
new CopyOrMoveRowDirective.InsertCopyOrMoveRowDirectiveBuilder()
.setRowIds(Arrays.asList(newRows.get(0).getId()))
.setTo(destination)
.build();
smartsheet
.sheetResources()
.rowResources()
.copyRows(sheet.getId(), null, null, copyOrMoveRowDirective);
smartsheet
.sheetResources()
.rowResources()
.copyRows(
sheet.getId(), EnumSet.of(RowCopyInclusion.CHILDREN), false, copyOrMoveRowDirective);
}
public void testMoveRow() throws SmartsheetException, IOException {
List<Long> rowIds = new ArrayList<Long>();
rowIds.add(newRows.get(0).getId());
CopyOrMoveRowDestination destination =
new CopyOrMoveRowDestination.InsertCopyOrMoveRowDestinationBuilder()
.setSheetId(copyToSheet.getId())
.build();
CopyOrMoveRowDirective directive =
new CopyOrMoveRowDirective.InsertCopyOrMoveRowDirectiveBuilder()
.setRowIds(rowIds)
.setTo(destination)
.build();
// smartsheet.sheetResources().rowResources().moveRows(sheet.getId(), null, null, directive);
smartsheet
.sheetResources()
.rowResources()
.moveRows(
sheet.getId(),
EnumSet.of(RowMoveInclusion.ATTACHMENTS, RowMoveInclusion.DISCUSSIONS),
false,
directive);
}
public void testSendRows() throws SmartsheetException, IOException {
// Specify individual recipient.
RecipientEmail recipientEmail =
new RecipientEmail.AddRecipientEmailBuilder().setEmail("*****@*****.**").build();
List<Recipient> recipients = new ArrayList<Recipient>();
recipients.add(recipientEmail);
MultiRowEmail multiRowEmail =
new MultiRowEmail.AddMultiRowEmailBuilder()
.setSendTo(recipients)
.setSubject("some subject")
.setMessage("some message")
.setCcMe(false)
.setRowIds(Arrays.asList(newRows.get(0).getId()))
.setColumnIds(Arrays.asList(addedColumn.getId()))
.setIncludeAttachments(false)
.setIncludeDiscussions(false)
.build();
smartsheet.sheetResources().rowResources().sendRows(sheet.getId(), multiRowEmail);
}
/**
* Convenience method to create new sheet with only one empty set, named {@link #PROPERTIES}.
* Display name and hint are settable via the appropriate bundle.
*
* @return a new sheet with default property set
*/
public static Sheet createDefault() {
Sheet newSheet = new Sheet();
// create default property set
newSheet.put(createPropertiesSet());
return newSheet;
}
/*
* import sheet scope content from POI Sheet.
*/
protected SSheet importSheet(Sheet poiSheet, int poiSheetIndex) {
SSheet sheet = book.createSheet(poiSheet.getSheetName());
sheet.setDefaultRowHeight(UnitUtil.twipToPx(poiSheet.getDefaultRowHeight()));
// ZSS-952
importSheetDefaultColumnWidth(poiSheet, sheet);
// reference FreezeInfoLoaderImpl.getRowFreeze()
sheet.getViewInfo().setNumOfRowFreeze(BookHelper.getRowFreeze(poiSheet));
sheet.getViewInfo().setNumOfColumnFreeze(BookHelper.getColumnFreeze(poiSheet));
sheet
.getViewInfo()
.setDisplayGridlines(
poiSheet
.isDisplayGridlines()); // Note isDisplayGridlines() and isPrintGridlines() are
// different
sheet.getViewInfo().setColumnBreaks(poiSheet.getColumnBreaks());
sheet.getViewInfo().setRowBreaks(poiSheet.getRowBreaks());
SPrintSetup sps = sheet.getPrintSetup();
SHeader header = sheet.getViewInfo().getHeader();
if (header != null) {
header.setCenterText(poiSheet.getHeader().getCenter());
header.setLeftText(poiSheet.getHeader().getLeft());
header.setRightText(poiSheet.getHeader().getRight());
sps.setHeader(header);
}
SFooter footer = sheet.getViewInfo().getFooter();
if (footer != null) {
footer.setCenterText(poiSheet.getFooter().getCenter());
footer.setLeftText(poiSheet.getFooter().getLeft());
footer.setRightText(poiSheet.getFooter().getRight());
sps.setFooter(footer);
}
if (poiSheet.isDiffOddEven()) {
Header poiEvenHeader = poiSheet.getEvenHeader();
if (poiEvenHeader != null) {
SHeader evenHeader = new HeaderFooterImpl();
evenHeader.setCenterText(poiEvenHeader.getCenter());
evenHeader.setLeftText(poiEvenHeader.getLeft());
evenHeader.setRightText(poiEvenHeader.getRight());
sps.setEvenHeader(evenHeader);
}
Footer poiEvenFooter = poiSheet.getEvenFooter();
if (poiEvenFooter != null) {
SFooter evenFooter = new HeaderFooterImpl();
evenFooter.setCenterText(poiEvenFooter.getCenter());
evenFooter.setLeftText(poiEvenFooter.getLeft());
evenFooter.setRightText(poiEvenFooter.getRight());
sps.setEvenFooter(evenFooter);
}
}
if (poiSheet.isDiffFirst()) {
Header poiFirstHeader = poiSheet.getFirstHeader();
if (poiFirstHeader != null) {
SHeader firstHeader = new HeaderFooterImpl();
firstHeader.setCenterText(poiFirstHeader.getCenter());
firstHeader.setLeftText(poiFirstHeader.getLeft());
firstHeader.setRightText(poiFirstHeader.getRight());
sps.setFirstHeader(firstHeader);
}
Footer poiFirstFooter = poiSheet.getFirstFooter();
if (poiFirstFooter != null) {
SFooter firstFooter = new HeaderFooterImpl();
firstFooter.setCenterText(poiFirstFooter.getCenter());
firstFooter.setLeftText(poiFirstFooter.getLeft());
firstFooter.setRightText(poiFirstFooter.getRight());
sps.setFirstFooter(firstFooter);
}
}
PrintSetup poips = poiSheet.getPrintSetup();
sps.setBottomMargin(poiSheet.getMargin(Sheet.BottomMargin));
sps.setTopMargin(poiSheet.getMargin(Sheet.TopMargin));
sps.setLeftMargin(poiSheet.getMargin(Sheet.LeftMargin));
sps.setRightMargin(poiSheet.getMargin(Sheet.RightMargin));
sps.setHeaderMargin(poiSheet.getMargin(Sheet.HeaderMargin));
sps.setFooterMargin(poiSheet.getMargin(Sheet.FooterMargin));
sps.setAlignWithMargins(poiSheet.isAlignMargins());
sps.setErrorPrintMode(poips.getErrorsMode());
sps.setFitHeight(poips.getFitHeight());
sps.setFitWidth(poips.getFitWidth());
sps.setHCenter(poiSheet.getHorizontallyCenter());
sps.setLandscape(poips.getLandscape());
sps.setLeftToRight(poips.getLeftToRight());
sps.setPageStart(poips.getUsePage() ? poips.getPageStart() : 0);
sps.setPaperSize(PoiEnumConversion.toPaperSize(poips.getPaperSize()));
sps.setCommentsMode(poips.getCommentsMode());
sps.setPrintGridlines(poiSheet.isPrintGridlines());
sps.setPrintHeadings(poiSheet.isPrintHeadings());
sps.setScale(poips.getScale());
sps.setScaleWithDoc(poiSheet.isScaleWithDoc());
sps.setDifferentOddEvenPage(poiSheet.isDiffOddEven());
sps.setDifferentFirstPage(poiSheet.isDiffFirst());
sps.setVCenter(poiSheet.getVerticallyCenter());
Workbook poiBook = poiSheet.getWorkbook();
String area = poiBook.getPrintArea(poiSheetIndex);
if (area != null) {
sps.setPrintArea(area);
}
CellRangeAddress rowrng = poiSheet.getRepeatingRows();
if (rowrng != null) {
sps.setRepeatingRowsTitle(rowrng.getFirstRow(), rowrng.getLastRow());
}
CellRangeAddress colrng = poiSheet.getRepeatingColumns();
if (colrng != null) {
sps.setRepeatingColumnsTitle(colrng.getFirstColumn(), colrng.getLastColumn());
}
sheet.setPassword(poiSheet.getProtect() ? "" : null);
// import hashed password directly
importPassword(poiSheet, sheet);
// ZSS-832
// import sheet visible
if (poiBook.isSheetHidden(poiSheetIndex)) {
sheet.setSheetVisible(SheetVisible.HIDDEN);
} else if (poiBook.isSheetVeryHidden(poiSheetIndex)) {
sheet.setSheetVisible(SheetVisible.VERY_HIDDEN);
} else {
sheet.setSheetVisible(SheetVisible.VISIBLE);
}
// ZSS-1130
// import conditionalFormatting
importConditionalFormatting(sheet, poiSheet);
return sheet;
}
public static void main(String args[]) {
try {
// 构建Workbook对象, 只读Workbook对象
// 直接从本地文件创建Workbook
// 从输入流创建Workbook
System.out.println("start load file-------------------------");
InputStream is = new FileInputStream("E:/account.xls"); // 创建输入
jxl.Workbook rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0); // 读取第一个sheet
int colNum = rs.getColumns(); // 列数
int rowNum = rs.getRows(); // 行数
// System.out.println(colNum+" "+rowNum);
// 创建account bean
ApplicationContext ctx =
new ClassPathXmlApplicationContext(
new String[] {
"spring/applicationContext-model-account.xml", "spring/applicationContext.xml"
});
AccountService g = (AccountService) ctx.getBean("accountService");
for (int i = 1; i < rowNum; i++) {
String no = rs.getCell(0, i).getContents();
String pass = rs.getCell(1, i).getContents();
String name = rs.getCell(2, i).getContents();
String maj = rs.getCell(3, i).getContents();
String grade = rs.getCell(4, i).getContents();
String cla = rs.getCell(5, i).getContents();
String idn = rs.getCell(6, i).getContents();
String email = rs.getCell(7, i).getContents();
String phone = rs.getCell(8, i).getContents();
String status = rs.getCell(9, i).getContents();
String role = rs.getCell(10, i).getContents();
ModelAccount ma = new ModelAccount();
ma.setAccountNo(no);
ma.setAccountPassword(pass);
ma.setAccountRealName(name);
ma.setAccountMajority(maj);
ma.setAccountGrade(grade);
ma.setAccountClass(cla);
ma.setAccountIdentification(idn);
ma.setAccountEmail(email);
ma.setAccountMobilePhone(phone);
ma.setAccountStatus(0);
ModelRoles ac = new ModelRoles();
ac.setRole_id(role);
ma.setAccountRole(ac);
g.saveOrUpdate(ma);
// ma.setAccount_role(account_role)
// System.out.println(no);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void readProcessSpreadSheet() {
processSpreadSheet = System.getProperty("processSpreadSheet");
System.out.println("processSpreadSheet: " + processSpreadSheet);
if (processCodesList.size() == 0) {
if (processSpreadSheet != null && !processSpreadSheet.equals("")) {
Workbook wb1 = null;
try {
wb1 = new XSSFWorkbook(processSpreadSheet);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Sheet sheet = wb1.getSheetAt(0);
Row row;
Cell cell;
int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();
int cols = 0; // No of columns
int tmp = 0;
// This trick ensures that we get the data properly even if it
// doesn't start from first few rows
for (int i = 0; i < 10 || i < rows; i++) {
row = sheet.getRow(i);
if (row != null) {
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
// out.println("tmp value"+tmp);
if (tmp > cols) {
cols = tmp;
}
}
}
ProcessDefinition tempProcessCode;
for (int r1 = 0; r1 < rows; r1++) {
tempProcessCode = new ProcessDefinition();
row = sheet.getRow(r1);
if (row != null) {
if (row.getCell(0) != null) {
for (int counter = 0; counter < cols; counter++) {
cell = row.getCell((short) counter);
// cell = row.getCell(1);
if (counter == 0) {
if (cell != null) {
tempProcessCode.setProcessName(cell.getStringCellValue());
} else {
tempProcessCode.setProcessName("");
}
} else if (counter == 1) {
if (cell != null) {
tempProcessCode.setProcessCode(cell.getStringCellValue());
} else {
tempProcessCode.setProcessCode("");
}
} else if (counter == 2) {
if (cell != null) {
tempProcessCode.setIaeaCode(cell.getStringCellValue());
} else {
tempProcessCode.setIaeaCode("");
}
} else if (counter == 3) {
if (cell != null) {
tempProcessCode.setProcessDescription(cell.getStringCellValue());
// System.out.println(tempProcessCode.getProcessDescription());
} else {
tempProcessCode.setProcessDescription("");
// System.out.println("Process Description EMpty");
}
}
}
}
} else {
rows++;
}
processCodesList.add(tempProcessCode);
}
}
}
System.out.println(processCodesList.size());
}
private void saveExcelPoject(File file) throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("timeplan");
// Заголовок в 0 строке
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Филиал");
cell = row.createCell(1);
cell.setCellValue("Город");
Calendar cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy");
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
cell = row.createCell(i + 2);
cell.setCellValue(sdf.format(cal.getTime()));
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
// sheet.setColumnWidth(0, 256);
// Цвета ячеек
CellStyle[] styles = new CellStyle[6];
styles[0] = wb.createCellStyle();
styles[0].setFillForegroundColor(HSSFColor.RED.index);
styles[0].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[1] = wb.createCellStyle();
styles[1].setFillForegroundColor(HSSFColor.GREEN.index);
styles[1].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[2] = wb.createCellStyle();
styles[2].setFillForegroundColor(HSSFColor.BLUE.index);
styles[2].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[3] = wb.createCellStyle();
styles[3].setFillForegroundColor(HSSFColor.ROSE.index);
styles[3].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[4] = wb.createCellStyle();
styles[4].setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
styles[4].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[5] = wb.createCellStyle();
styles[5].setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
styles[5].setFillPattern(FillPatternType.SOLID_FOREGROUND);
short rowIdx = 0;
for (Region region : this.regions) {
row = sheet.createRow(++rowIdx);
cell = row.createCell(0);
cell.setCellValue(region.filial);
cell = row.createCell(1);
cell.setCellValue(region.name);
cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
short color = region.getDateColorIndex(cal.getTime());
if (color >= 0) {
cell = row.createCell(i + 2);
cell.setCellStyle(styles[color]);
}
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
}
try (FileOutputStream fileOut = new FileOutputStream(file)) {
wb.write(fileOut);
}
}
/** Count the number of columns, using the number of non-empty cells in the first row. */
private int countNonEmptyColumns(final Sheet sheet) {
Row firstRow = sheet.getRow(0);
return firstEmptyCellPosition(firstRow);
}