一.利用poi生成excel,介绍一下基本用法
1.HSSFWorkbook workbook = new HSSFWorkbook();创建工作薄
2.HSSFSheet sheet = workbook.createSheet("工资表");一个sheet最多有65535行
3.HSSFRow row = sheet.createRow(0);创建行(最顶端,头部);
4.HSSFCell cell = row.createCell((short) 0);创建单元格;
5.FileOutputStream fOut = new FileOutputStream(outputFile);创建流输出文件;
workbook.write(fOut);
fOut.flush();
操作结束,关闭文件
fOut.close();
需要下载POI的jar文件,引入jar文件拷贝,代码就可以运行。
二.简单例子
Main.java
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelTest {
public static int rowNum = 1;
public static int rowTotalNum = 1;
private static HSSFRow row1;
public static void main(String[] args) {
String fileName = "ExcelTest";
String outPath ="D:\\";
List list = new ArrayList();
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet();
FileOutputStream fout;
String excelOutPath = outPath+"\\"+ fileName+".xls";
try {
fout = new FileOutputStream(excelOutPath);
crateExcel(hwb, sheet, fout);//创建excel
insertData(fout, excelOutPath, hwb, sheet, list);//向excel插入数据
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void crateExcel(HSSFWorkbook hwb,HSSFSheet sheet,FileOutputStream fout) throws IOException{
//头部
row1 = sheet.createRow(0);
row1.createCell(0).setCellValue(new HSSFRichTextString("序号"));
row1.createCell(1).setCellValue(new HSSFRichTextString("地址"));
row1.createCell(2).setCellValue(new HSSFRichTextString("X"));
row1.createCell(3).setCellValue(new HSSFRichTextString("Y"));
row1.createCell(4).setCellValue(new HSSFRichTextString("相似度"));
hwb.write(fout);
}
public static void insertData(FileOutputStream fout,String excelOutPath,HSSFWorkbook hwb,HSSFSheet sheet,List list) throws IOException{
fout = new FileOutputStream(excelOutPath);
int len = list.size();
for(int i =0;i<100 ;i++){
String likeNum = "33";
String addr = "北京";
String x_point = "119.3456";
String y_point = "38.3456";
HSSFRow row2 = sheet.createRow((rowNum % 50000)+1);// 创建表格的行
row2.createCell(0).setCellValue(new HSSFRichTextString(Integer.toString(rowNum)));
row2.createCell(1).setCellValue(new HSSFRichTextString(addr));
row2.createCell(2).setCellValue(new HSSFRichTextString(x_point));
row2.createCell(3).setCellValue(new HSSFRichTextString(y_point));
row2.createCell(4).setCellValue(new HSSFRichTextString(likeNum));
rowNum++;
//一个sheet最多可容65535行
if(rowNum%(65535*rowTotalNum)==0){
rowTotalNum++;
//创建新的sheet
sheet = hwb.createSheet();
row1.createCell(0).setCellValue(new HSSFRichTextString("序号"));
row1.createCell(1).setCellValue(new HSSFRichTextString("地址"));
row1.createCell(2).setCellValue(new HSSFRichTextString("X"));
row1.createCell(3).setCellValue(new HSSFRichTextString("Y"));
row1.createCell(4).setCellValue(new HSSFRichTextString("相似度"));
}
}
hwb.write(fout);
fout.close();
}
}