import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class Test {
public static void main(String[] args) throws Exception {
List<List> head = new ArrayList();
//sheet名称
List<String> name = new ArrayList<String>(Arrays.asList(new String[]{"大区","分公司","门店"}));
//每个sheet头
List<String> head1 = new ArrayList<String>(Arrays.asList(new String[]{"大区头1","大区头2","大区头3","大区头4","大区头5"}));
List<String> head2 = new ArrayList<String>(Arrays.asList(new String[]{"分公司头1","分公司头2","分公司头3"}));
List<String> head3 = new ArrayList<String>(Arrays.asList(new String[]{"门店头1","门店头2"}));
head.add(head1);
head.add(head2);
head.add(head3);
//每个sheet数据
List<List<List>> data = new ArrayList<List<List>>();
List<List> data1 = new ArrayList<List>();
List<List> data2 = new ArrayList<List>();
List<List> data3 = new ArrayList<List>();
for (int i = 0; i < 10; i++) {
List tmpList = new ArrayList();
for (int j = 0; j < 5; j++) {
tmpList.add(j);
}
data1.add(tmpList);
}
for (int i = 0; i < 4; i++) {
List tmpList = new ArrayList();
for (int j = 0; j < 3; j++) {
tmpList.add(j);
}
data2.add(tmpList);
}
for (int i = 0; i < 6; i++) {
List tmpList = new ArrayList();
for (int j = 0; j < 2; j++) {
tmpList.add(j);
}
data3.add(tmpList);
}
data.add(data1);
data.add(data2);
data.add(data3);
//System.out.println(xx.get(0));
createExcelSheet(name,head,data,"d://1.xls");
}
@SuppressWarnings("unchecked")
private static void createExcelSheet(List<String> sheetName, List<List> sheetHeader,List<List<List>> sheetData, String fileRealPath)
throws Exception {
HSSFWorkbook workBook = new HSSFWorkbook();
int sheetnum = sheetName.size();
for(int m=0; m<sheetnum; m++){
HSSFSheet sheet = workBook.createSheet();
String msg = sheetName.get(m).toString();
String str = new String(msg.getBytes("UTF-8"),"UTF-8");
workBook.setSheetName(m,str);
HSSFHeader header = sheet.getHeader();
header.setCenter("sheet");
HSSFRow headerRow = sheet.createRow(0);
HSSFCellStyle headstyle = workBook.createCellStyle();
HSSFFont headfont = workBook.createFont();
headfont.setColor(HSSFColor.BLACK.index);
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headstyle.setFont(headfont);
//List<String> lst = new ArrayList<String>();
//List lst = Arrays.asList(new String[]{"1","2","3","4","5","6","7","8","9","10"});
for (int i = 0; i < sheetHeader.get(m).size(); i++) {
HSSFCell headerCell = headerRow.createCell(i);
headerCell.setCellStyle(headstyle);
// 设置cell的值
headerCell.setCellValue(sheetHeader.get(m).get(i).toString());
headerCell.setCellStyle(headstyle);
}
int rowIndex = 1;
for (int i = 0; i < sheetData.get(m).size(); i++) {
System.out.println("总数:"+sheetData.get(m).size()+"--"+i);
List<String> list2 = sheetData.get(m).get(i);
HSSFRow row = sheet.createRow(rowIndex);
for (int q = 0; q < list2.size(); q++) {
// 创建第i个单元格
HSSFCell cell = row.createCell(q);
System.out.println(String.valueOf(list2.get(q)));
cell.setCellValue(String.valueOf(list2.get(q)).replace("未知", ""));
sheet.setColumnWidth(q, (80 * 50));
}
rowIndex++;
}
//FileOutputStream fos = new FileOutputStream(fileRealPath);
}
FileOutputStream fos = new FileOutputStream(fileRealPath);
// sheet.setGridsPrinted(true);
// HSSFFooter footer = sheet.getFooter();
// footer.setRight("Page " + HSSFFooter.page() + " of "
// + HSSFFooter.numPages());
workBook.write(fos);
fos.close();
}
}
上面代码的输出本来在for循环内部,这样在写入本地文件的时候没有问题,当使用servlet强制输出到response中就会出现只能显示第一个sheet的情况,所以需要把强制输出调整到for循环之后。