在Java中使用Apache POI导入导出Excel(二)

news/2024/12/4 6:53:37/

本文将继续介绍POI的使用,上接在Java中使用Apache POI导入导出Excel(一)

使用Apache POI组件操作Excel(二)

14、读取和重写工作簿

java">try (InputStream inp = new FileInputStream("workbook.xls")) {
//InputStream inp = new FileInputStream("workbook.xlsx");Workbook wb = WorkbookFactory.create(inp);Sheet sheet = wb.getSheetAt(0);Row row = sheet.getRow(2);Cell cell = row.getCell(3);if (cell == null)cell = row.createCell(3);cell.setCellType(CellType.STRING);cell.setCellValue("a test");// Write the output to a filetry (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);}
}

15、在单元格中使用换行符

java">Workbook wb = new XSSFWorkbook(); 
Sheet sheet = wb.createSheet();Row row = sheet.createRow(2);Cell cell = row.createCell(2);
cell.setCellValue("Use \n with word wrap on to create a new line");//to enable newlines you need set a cell styles with wrap=true
CellStyle cs = wb.createCellStyle();
cs.setWrapText(true);
cell.setCellStyle(cs);//increase row height to accommodate two lines of text
row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));//adjust column width to fit the content
sheet.autoSizeColumn(2);try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) {wb.write(fileOut);
}
wb.close();

16、数据格式

java">Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");CellStyle style;
DataFormat format = wb.createDataFormat();Row row;
Cell cell;int rowNum = 0;
int colNum = 0;row = sheet.createRow(rowNum++);cell = row.createCell(colNum);
cell.setCellValue(11111.25);style = wb.createCellStyle();
style.setDataFormat(format.getFormat("0.0"));
cell.setCellStyle(style);row = sheet.createRow(rowNum++);cell = row.createCell(colNum);cell.setCellValue(11111.25);style = wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.0000"));
cell.setCellStyle(style);try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);
}
wb.close();

17、使工作表适合一页

java">Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");PrintSetup ps = sheet.getPrintSetup();sheet.setAutobreaks(true);ps.setFitHeight((short)1);
ps.setFitWidth((short)1);// Create various cells and rows for spreadsheet.
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);
}
wb.close();

18、设置打印区域

java">Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");//sets the print area for the first sheet
wb.setPrintArea(0, "$A$1:$C$2");//Alternatively:
wb.setPrintArea(0, //sheet index0, //start column1, //end column0, //start row0  //end row
);try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);
}
wb.close();

19、在页脚上设置页码

java">Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");Footer footer = sheet.getFooter();
footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() );// Create various cells and rows for spreadsheet.
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {wb.write(fileOut);
}
wb.close();

20、使用便捷函数

便利函数提供 实用程序功能,例如在合并周围设置边框 区域和更改样式属性而不明确 创建新样式。

java">Workbook wb = new XSSFWorkbook()
Sheet sheet1 = wb.createSheet( "new sheet" );// Create a merged region
Row row = sheet1.createRow( 1 );
Row row2 = sheet1.createRow( 2 );Cell cell = row.createCell( 1 );
cell.setCellValue( "This is a test of merging" );
CellRangeAddress region = CellRangeAddress.valueOf("B2:E5");sheet1.addMergedRegion( region );// Set the border and border colors.
RegionUtil.setBorderBottom( BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBorderTop(    BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBorderLeft(   BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBorderRight(  BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setTopBorderColor(   IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setLeftBorderColor(  IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setRightBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb);// Shows some usages of HSSFCellUtil
CellStyle style = wb.createCellStyle();
style.setIndention((short)4);CellUtil.createCell(row, 8, "This is the value of the cell", style);Cell cell2 = CellUtil.createCell( row2, 8, "This is the value of the cell");CellUtil.setAlignment(cell2, HorizontalAlignment.CENTER);// Write out the workbook
try (OutputStream fileOut = new FileOutputStream( "workbook.xls" )) {wb.write( fileOut );
}
wb.close();

21、在工作表上向上或向下移动行

java">Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("row sheet");// Create various cells and rows for spreadsheet.
// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)sheet.shiftRows(5, 10, -5);

22、将图纸设置为已选中

java">Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("row sheet");sheet.setSelected(true);

23、设置缩放放大倍数

java">Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");sheet1.setZoom(75);   // 75 percent magnification


http://www.ppmy.cn/news/1552203.html

相关文章

Flink随笔 20241203 Flink重点内容

Flink 是一个强大的流处理框架,它的设计理念是高吞吐量、低延迟的流式计算。你提到的这些重点是 Flink 的核心组成部分,下面我将详细解析每一个方面。 1. 窗口(Window) 窗口是 Flink 流处理中一个非常重要的概念,主要…

uni-app写的微信小程序每次换账号登录时出现缓存上一个账号数据的问题

uni-app写的微信小程序每次更换另外账号登录时出现缓存上一个账号数据的问题? 1, 清除缓存数据:在 onShow 钩子中,我们将 powerStations、list 和 responseRoles 的值重置为初始状态,以清除之前的缓存数据。 2&…

Linux C/C++编程之静态库

【图书推荐】《Linux C与C一线开发实践(第2版)》_linux c与c一线开发实践pdf-CSDN博客《Linux C与C一线开发实践(第2版)(Linux技术丛书)》(朱文伟,李建英)【摘要 书评 试读】- 京东图书 (jd.com…

肝硬化腹水中医怎么治疗

肝硬化腹水在中医中被称为“鼓胀”或“水鼓”,其治疗强调整体观念和辨证施治。以下是中医治疗肝硬化腹水的主要方法和原则: ### 1. 治疗原则 - **扶正祛邪**:中医认为肝硬化腹水的主要病机是正虚邪实,治疗上以扶正为主&#xff…

【大模型微调】pdf转markdown

目前市面上大部分都是pdf文档,要想转换成能训练的文本,调研了各种工具。 觉得MinerU确实不错。 参考此链接进行操作 MinerU/docs/README_Ubuntu_CUDA_Acceleration_en_US.md at master opendatalab/MinerU GitHub 需要注意的几个点: 1. 使用root账户安装的,配置文件在…

微信小程序分包流程

小程序目录结构 - app.js - app.json - app.wxss - pages // 主包所有页面- index // 页面内容,包含(index.js,index.wxml,index.json,index.wxss)- logs - packageA // 第一个分包- pages // 第一个分包的所有页面- index // 页面内容,包含(index.js,index.wxml,index.json…

南昌榉之乡托养机构解读:自闭症与看电视并无必然联系

在探讨自闭症的成因时,有人会问:自闭症是多看电视引起的吗?今天,就让我们来看看南昌榉之乡托养机构对此有何见解。 榉之乡大龄自闭症托养机构在江苏、广东、江西等地都有分校,一直致力于为大龄自闭症患者提供专业的支持…

Kylin Server V10 下 Kafka 集群部署

一、ZooKeeper 集群部署 1、主机规划 主机名 IP 地址 myid 10.8.3.35 1 10.8.3.36 2 10.8.3.37 3 2、拓扑结构 3、部署 (1) 下载Zookeeper [root@localhost ~]# cd /usr/local [root@localhost local]# wget https://www.apache.org/dyn/closer.lua/zookeeper/zookeeper-…