package com.test;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* 写入Excel
*/
public class TestWorkbook {
public static void main(String[] args) {
writeInExcel();
readOutExcel();
}
private static void readOutExcel() {
InputStream input = null;
try {
input = new FileInputStream("F:" + File.separator
+ "test.xls");
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
HSSFWorkbook workBook = new HSSFWorkbook(input);
HSSFSheet sheet = workBook.getSheetAt(0);
int rowNum = sheet.getLastRowNum();
for(int i = 0; i < rowNum; i++)
{
HSSFRow row = sheet.getRow(i);
System.out.println(row.getCell(0) + "," + row.getCell(1));
}
} catch (IOException e) {
e.printStackTrace();
} finally{
if(null != input)
{
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private static void writeInExcel() {
// 创建工作薄
HSSFWorkbook workBook = new HSSFWorkbook();
// 文件本地存储地址
OutputStream outputStream = null;
try {
outputStream = new FileOutputStream("F:" + File.separator
+ "test.xls");
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}
// 创建工作表,并取名
HSSFSheet sheet = workBook.createSheet("Test");
// 设置每列宽度
setFileColumnWidth(sheet);
// 设置表头
setTableHeader(workBook, sheet);
// 设置内容
setContent(workBook, sheet);
// 写入数据到客户端
try {
workBook.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != outputStream) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/*
* 设置内容
*/
private static void setContent(HSSFWorkbook workBook, HSSFSheet sheet) {
// 设置
HSSFFont font = setFont(workBook, false);
HSSFCellStyle cellStyle = setCellStyle(workBook, font, false);
List<String[]> sList = new ArrayList<String[]>();
String[] arr1 = { "1", "小红" };
String[] arr2 = { "2", "小翠" };
sList.add(arr1);
sList.add(arr2);
for (int i = 0; i < sList.size(); i++) {
HSSFRow row = sheet.createRow(i + 1);
HSSFCell cell1 = row.createCell(0);
cell1.setCellStyle(cellStyle);
cell1.setCellValue(sList.get(i)[0]);
HSSFCell cell2 = row.createCell(1);
cell2.setCellStyle(cellStyle);
cell2.setCellValue(sList.get(i)[1]);
}
}
/*
* 设置表头
*/
private static void setTableHeader(HSSFWorkbook workBook, HSSFSheet sheet) {
// 设置表头字体
HSSFFont font = setFont(workBook, true);
String[] tableHeder = { "Id", "姓名" };
// 创建第一行。
HSSFRow row = sheet.createRow(0);
HSSFCellStyle cellStyle = setCellStyle(workBook, font, true);
for (int i = 0; i < tableHeder.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellValue(tableHeder[i]);
}
}
/*
* 设置样式
*/
private static HSSFCellStyle setCellStyle(HSSFWorkbook workBook,
HSSFFont font, boolean isHeader) {
HSSFCellStyle cellStyle = workBook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setFont(font);
setBorderAndVertical(cellStyle);
return cellStyle;
}
/*
* 设置字体
*/
private static HSSFFont setFont(HSSFWorkbook workBook, boolean isHeader) {
HSSFFont font = workBook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) (10));
if (isHeader) {
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
} else {
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
}
return font;
}
/*
* 设置每列宽度
*/
private static void setFileColumnWidth(HSSFSheet sheet) {
sheet.setColumnWidth(0, 2300);
sheet.setColumnWidth(1, 2000);
}
private static void setBorderAndVertical(HSSFCellStyle cellStyle) {
// 垂直对齐方式 居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
}
}