POI Java Manual

Java 代码里设置formular

String sumStr = "SUMIF("+"C"+(_rowTempFirst.getRowNum()+1)+":"+"C"+(_line-2+1)+",\"<>---\","+exlColName.get(_sumCol-1)+(_rowTempFirst.getRowNum()+1)+":"+exlColName.get(_sumCol-1)+(_line-2+1)+")";

_newNow.getCell(_sumCol-1).setCellFormula(sumStr);

读取Merged Region

所有的MergedRegion
List<CellRangeAddress> _mergerdList = exlBook.getSheetAt(0).getMergedRegions();

MergedRegion的数量
int _mergerdNum = exlBook.getSheetAt(0).getNumMergedRegions();

其中一个MergedRegion
CellRangeAddress _cellRange = exlBook.getSheetAt(0).getMergedRegion(i);

寫Cell時精度問題

Double.valueOf(obj.toString())
可以保持原始精度
Float.parseFloat(obj.toString())
不能保持原始精度

	public static Cell writeCeall(Cell cell,Object obj) {
	
		if(obj instanceof Number) {
			cell.setCellValue(Double.valueOf(obj.toString()));
			return cell;
		}
		if(obj instanceof Float) {
			cell.setCellValue(Float.parseFloat(obj.toString()));
			return cell;
		}		
cell.setCellValue(String.valueOf(obj));

循環

for

 Workbook wb = WorkbookFactory.create(inStream);
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet)
{
  for (Cell cell : row)
  {
    System.out.println("Iteration.");
  }
}

for

Range range = worksheet.getCells().getMaxDisplayRange();
int tcols = range.getColumnCount();
int trows = range.getRowCount();
RowCollection rows = cells.getRows();
for (int i = 0 ; i < rows.getCount() ; i++)
{
	for (int j = 0 ; j < tcols ; j++)
	{
		System.out.print(cells.get(i,j).getName() + " - " + cells.get(i,j).getValue() + "\t");
	}
	System.out.println("");
}

Iterator

Iterator<Cell> cellIterator = row.iterator();
while(cellIterator.hasNext()) {

使用Iterator迭代少了最後一頁

 ExcelHelp.copyRow(_rowTemplate, row);
 Iterator<Cell> cellIterator = row.iterator();
 while(cellIterator.hasNext()) {
	 Cell cell = cellIterator.next();
	 String cellVal = ExcelHelp.readCeall(cell);
	 if(cellVal.equals("")) continue;
	 if(cellVal.equals("Item")) {
		 ExcelHelp.writeCeall(cell,(String)map.get("Item"));
		 continue;
	 }

4.1.0 版本 shiftRows not works

 sheet.shiftRows(rowNum+1, sheet.getLastRowNum(), -1, false, false);

<!--	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>4.1.0</version>
	</dependency>
	   <dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml</artifactId>
	    <version>4.1.0</version>
	</dependency>-->
	
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>5.2.5</version>
	</dependency>
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml</artifactId>
	    <version>5.2.5</version>
	</dependency>

两种方式读取workbook

1. 在原有文件上处理数据
2. 把原有文件当做模版，形成新的文件

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class ReadExcelTest {
	public static void main(String[] args) {
		try{
			 XSSFWorkbook workbook;
			 XSSFSheet sheet;	
	    	String filePath = "D:\\test.xlsx";
	    	 workbook = new XSSFWorkbook(new File(filePath));	    	
	    	InputStream in = new FileInputStream(filePath);
	    	workbook = new XSSFWorkbook(in);	    	
		    sheet = workbook.getSheetAt(0);	
		    for(Row row:sheet) {
		    	for(Cell cell:row) {
		    		System.out.print(cell.toString());
		    	}
		    	System.out.println("");
		    }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

shiftRows

不是下面或者上面的内容 向上移动，或者向下移动，而是覆盖。

 sheet.shiftRows(rowNum, rowNum, -1);
rowNum删除后会覆盖上面的内容，也就是为空

shiftRows 往下移动一行会覆盖下面的内容

shiftRows 其实rows 必须是全部行数，一行或者数行的应用很好

sheet.shiftRows(rowNum+1, sheet.getLastRowNum(),1, true, false);