- 新建Sheet
在Workbook里新建Sheet时,使用「HSSFWorkbook」类的「createSheet」的方法。
HSSFWorkbook workbook = new HSSFWorkbook();//创建个空白的workbook
HSSFSheet sheet = workbook.createSheet();//创建个空白的sheet
- 读取现有的Sheet
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.getSheet("sheet1")//读取名称为sheet1的sheet
不用Sheet名而用Sheet的序列号来取得Sheet的话,可以用「HSSFWorkbook」类的「getSheetAt」方法。使用方法如下:
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.getSheetAt(1);//读取序号为1的sheet(第二张sheet)
package linkin;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Linkin
{
public static void main(String[] args)
{
HSSFWorkbook workbook = new HSSFWorkbook();
workbook.createSheet();//创建workbook的sheet
workbook.createSheet();
workbook.createSheet("test");
FileOutputStream out = null;
try
{
out = new FileOutputStream("sample.xls");
workbook.write(out);
System.out.println("OK");
}
catch (IOException e)
{
System.out.println(e.toString());
}
finally
{
try
{
out.close();
}
catch (IOException e)
{
System.out.println(e.toString());
}
}
}
}
从生成的excel可以看出,如果没有设定Sheet名,会从0开始默认设定为Sheet0,Sheet1等,设定为test的话,则可以创建test这样的Sheet。
- Workbook复制Sheet
package linkin;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Linkin
{
public static void main(String[] args)
{
FileInputStream in = null;
HSSFWorkbook workbook = null;
try
{
in = new FileInputStream("sample.xls");
POIFSFileSystem fs = new POIFSFileSystem(in);
workbook = new HSSFWorkbook(fs);
}
catch (IOException e)
{
System.out.println(e.toString());
}
finally
{
try
{
in.close();
}
catch (IOException e)
{
System.out.println(e.toString());
}
}
workbook.cloneSheet(0);
workbook.cloneSheet(1);
FileOutputStream out = null;
try
{
out = new FileOutputStream("sample1.xls");
workbook.write(out);
System.out.println("OK");
}
catch (IOException e)
{
System.out.println(e.toString());
}
finally
{
try
{
out.close();
}
catch (IOException e)
{
System.out.println(e.toString());
}
}
}
}
执行后打开「sample1.xls」文件,可以看出,两个Sheet被成功复制。被复制的Sheet名格式是「原Sheet名(序列号)」。顺便一提的是,如果你手动在Excel里进行复制的话,被复制的Sheet名的格式是「原Sheet名 (序列号)」。也就是原Sheet名和(序列号)之间有一个半角空格。
- Workbook删除Sheet
如果不知道序列号的话,也可以通过「HSSFWorkbook」类的方法「getSheetIndex」,设定Sheet名来取得序列号。
如下代码生成一个Workbook,再在其中创建三个Sheet,最后再把名为「Sheet1」的Sheet删除。
package linkin;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Linkin
{
public static void main(String[] args)
{
HSSFWorkbook workbook = new HSSFWorkbook();
workbook.createSheet();
workbook.createSheet();
workbook.createSheet();
workbook.removeSheetAt(workbook.getSheetIndex("Sheet1"));
System.out.println("Sheet0 = " + workbook.getSheetIndex("Sheet0"));
System.out.println("Sheet2 = " + workbook.getSheetIndex("Sheet2"));
FileOutputStream out = null;
try
{
out = new FileOutputStream("sample.xls");
workbook.write(out);
}
catch (IOException e)
{
System.out.println(e.toString());
}
finally
{
try
{
out.close();
}
catch (IOException e)
{
System.out.println(e.toString());
}
}
}
}
我们发现中间的Sheet被删除后,后面的Sheet序列号会往前移一个,总之会维持从0开始的整数顺序。
- 改变sheet的名称
package linkin;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Linkin
{
public static void main(String[] args)
{
HSSFWorkbook workbook = new HSSFWorkbook();
workbook.createSheet();
workbook.createSheet();
workbook.setSheetName(0, "test");//更改sheet0的名字为test
workbook.setSheetName(1, "测试");//更改sheet1的名字为“测试”设置为utf-16
FileOutputStream out = null;
try
{
out = new FileOutputStream("sample.xls");
workbook.write(out);
System.out.println("OK");
}
catch (IOException e)
{
System.out.println(e.toString());
}
finally
{
try
{
out.close();
}
catch (IOException e)
{
System.out.println(e.toString());
}
}
}
}
- 设定列宽度
package linkin;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Linkin
{
public static void main(String[] args)
{
FileInputStream in = null;
HSSFWorkbook workbook = null;
try
{
in = new FileInputStream("sample.xls");
POIFSFileSystem fs = new POIFSFileSystem(in);
workbook = new HSSFWorkbook(fs);
}
catch (IOException e)
{
System.out.println(e.toString());
}
finally
{
try
{
in.close();
}
catch (IOException e)
{
System.out.println(e.toString());
}
}
HSSFSheet sheet = workbook.getSheetAt(0);
short[] width = new short[3];
for (int i = 0; i < 3; i++)
{
width[i] = sheet.getColumnWidth((short) i);
System.out.println(i + "列宽度:" + width[i]);
}
//循环获取0,1,2的宽度,将第二列的宽度赋给第0,1列
sheet.setColumnWidth((short) 0, width[2]);
sheet.setColumnWidth((short) 1, width[2]);
FileOutputStream out = null;
try
{
out = new FileOutputStream("sample2.xls");
workbook.write(out);
}
catch (IOException e)
{
System.out.println(e.toString());
}
finally
{
try
{
out.close();
}
catch (IOException e)
{
System.out.println(e.toString());
}
}
}
}
- 指定默认的列宽度
指定默认的列宽度用「HSSFSheet」类的「setDefaultColumnWidth」方法。
要取得原来的默认列宽度,使用「getDefaultColumnWidth」方法。
package linkin;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Linkin
{
public static void main(String[] args)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
sheet.setDefaultColumnWidth((short)5);//将默认的列宽设为5个文字大小
FileOutputStream out = null;
try{
out = new FileOutputStream("sample2.xls");
workbook.write(out);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try {
out.close();
}catch(IOException e){
System.out.println(e.toString());
}
}
}
}
- 调整工作表Sheet的显示比例
(int numerator, int denominator)参数,这样就可以指定显示比例了,指定方法是用”numerator”÷”denominator”,比方说,「setZoom(2, 1)」就是设定为200%的比例,「setZoom(3, 4)」就是设定为75%的比例。
package linkin;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Linkin
{
public static void main(String[] args)
{
FileInputStream in = null;
HSSFWorkbook workbook = null;
try{
in = new FileInputStream("sample.xls");
POIFSFileSystem fs = new POIFSFileSystem(in);
workbook = new HSSFWorkbook(fs);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try{
in.close();
}catch (IOException e){
System.out.println(e.toString());
}
}
HSSFSheet sheet = workbook.getSheetAt(0);//取得序号为0的sheet
sheet.setZoom(2, 1);//改变sheet的显示比例为200%
FileOutputStream out = null;
try{
out = new FileOutputStream("sample2.xls");
workbook.write(out);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try {
out.close();
}catch(IOException e){
System.out.println(e.toString());
}
}
}
}
- 合并单元格
public int addMergedRegion(Region region)--过时
public int addMergedRegion(CellRangeAddress cellRangeAddress)--没过时
参数(Region region),合并范围必须使用「Region」类来指定,关于「Region」类的介绍如下。
extends java.lang.Object implements java.lang.Comparable
「Region」类的构造方法
public Region(int rowFrom, short colFrom, int rowTo, short colTo)// rowFrom起始行号, colFrom起始列号,rowTo结束行号,colTo结束列号。指定范围时,从左上的单元格到右下的单元格指定,比方像下面这样:Region(1, (short)1, 2, (short)3)
package linkin;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Linkin
{
public static void main(String[] args)
{
FileInputStream in = null;
HSSFWorkbook workbook = null;
try
{
in = new FileInputStream("sample.xls");
POIFSFileSystem fs = new POIFSFileSystem(in);
workbook = new HSSFWorkbook(fs);
}
catch (IOException e)
{
System.out.println(e.toString());
}
finally
{
try
{
in.close();
}
catch (IOException e)
{
System.out.println(e.toString());
}
}
HSSFSheet sheet = workbook.getSheetAt(0);
sheet.addMergedRegion(new Region(1, (short) 1, 2, (short) 3));//从第2行第2列开始并到第3行,第4列
FileOutputStream out = null;
try
{
out = new FileOutputStream("sample2.xls");
workbook.write(out);
}
catch (IOException e)
{
System.out.println(e.toString());
}
finally
{
try
{
out.close();
}
catch (IOException e)
{
System.out.println(e.toString());
}
}
}
}