注释比较详细,直接贴代码,不多比比,要用到的jar请自行下载,或者联系我获取
package creativity.guoke;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* @author peter
* @Date 2017年11月27日
* @Description 导出多sheet的excel
*/
public class ApachePoi
{
public static void main(String[] args) throws IOException
{
// TODO Auto-generated method stub
exportExcel();
}
@SuppressWarnings("resource")
public static String exportExcel() throws IOException
{
String fileName = "C:\\Users\\YUY\\Desktop\\"+System.currentTimeMillis()+".xls";
String[] header = new String[]{"经销商", "年卡号", "激活码"};
List<String> listAgent = new ArrayList<String>();
listAgent.add("steven");
listAgent.add("peter");
listAgent.add("vactor");
// 导出到多个sheet中--------------------------------------------------------------------------------开始
// 创建一个EXCEL
HSSFWorkbook wb = new HSSFWorkbook();
// 循环经销商,每个经销商一个sheet
for (int i = 0; i < listAgent.size(); i++)
{
// 第 i 个sheet,以经销商命名
HSSFSheet sheet = wb.createSheet(listAgent.get(i).toString());
//第一行前三个单元格合并(参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号)
CellRangeAddress region1 = new CellRangeAddress(0,0,(short)0,(short)2);
sheet.addMergedRegion(region1);
HSSFRow row1 = sheet.createRow(0);
HSSFCell cell1 = row1.createCell(0);
HSSFCell cell2 = row1.createCell(1);
HSSFCell cell3 = row1.createCell(2);
cell1.setCellValue("第一行设置标题");
//单元格 设置样式 ----
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
cellStyle.setBorderBottom(BorderStyle.DOUBLE); //下边框
cellStyle.setFillForegroundColor((short) 13);// 设置背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置字体
HSSFFont font = wb.createFont();
font.setFontName("仿宋_GB2312");
font.setBold(true);//粗体显示
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);//字体设置应用到设置的格式上
//使 设置的样式生效(设置的格式应用到单元格上)
cell1.setCellStyle(cellStyle);
cell2.setCellStyle(cellStyle);
/** ^
*
*
*/
cell3.setCellStyle(cellStyle);
// 第i个sheet第二行为列名
HSSFRow rowFirst = sheet.createRow(1);
// 写标题
for (int j = 0; j < header.length; j++)
{
// 获取第一行的每一个单元格
HSSFCell cell = rowFirst.createCell(j);
// 往单元格里面写入值
cell.setCellValue(header[j]);
}
// 循环该经销商下的卡(为每一个sheet准备数据)
// List<StudyCard> lst = study_cardService.list(paramMap, null);
List<Map<String, Object>> lst = new ArrayList<Map<String, Object>>();
// 循环添加测试数据
for (int j = 0; j < 10; j++)
{
Map<String, Object> data = new HashMap<String, Object>(3);
data.put("agent", listAgent.get(i).toString() + j);
data.put("card_no", "card" + j);
data.put("password", "password" + j);
lst.add(data);
}
for (int j = 0; j < lst.size(); j++)
{
Map<?, ?> map = (Map<?, ?>) lst.get(j);
// 创建数据行,从第三行开始
HSSFRow row = sheet.createRow(j + 2);
// 设置对应单元格的值
row.createCell(0).setCellValue(map.get("agent").toString());
row.createCell(1).setCellValue(map.get("card_no").toString());
row.createCell(2).setCellValue(map.get("password").toString());
}
}
// 写出文件(path为文件路径含文件名)
OutputStream os = null;
try
{
os = new FileOutputStream(new File(fileName));
wb.write(os);
System.out.println("文件导出成功,请前往查看.....\n" + fileName);
}
catch (Exception e)
{
System.out.println("我擦,导出文件居然出错了.....\n" + e.getMessage());
}
finally
{
os.flush();
os.close();
}
// 导出到多个sheet中---------------------------------------------------------------------------------结束
return null;
}
}
运行结果:
EXCEL效果
多个sheet中都有数据不在一 一截图