如何将数据导入到一个已经做好的excel模板中 本人对poi还不熟,请来几段代码,带解释哦,有poi祥细文档可发[email protected].谢大神们了!excel文档poijava 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 自己来jsp<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%><%@page import="java.text.SimpleDateFormat"%><%@page import="org.apache.poi.ss.usermodel.CellStyle"%><%@page import="com.yinjun.common.InitApp"%><%@page import="com.yinjun.service.ITSBalanceService"%><%@page import="com.yinjun.entity.TSBalance"%><%@page import="java.util.*"%><%@page import="org.apache.poi.ss.usermodel.Cell"%><%@page import="org.apache.poi.ss.usermodel.Row"%><%@page import="org.apache.poi.ss.usermodel.Sheet"%><%@page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%><%@page import="org.apache.poi.ss.usermodel.Workbook"%><%@page import="java.io.IOException"%><%@page import="java.io.FileInputStream"%><%@page import="java.io.InputStream"%><%@ page import="java.io.File"%><%@ page import="com.yinjun.util.*"%><% request.setCharacterEncoding("utf-8"); String msg = ""; String fileName = request.getParameter("fileName"); int index = fileName.lastIndexOf("\\"); if (index > 0) { fileName = fileName.substring(index + 1); } String title = new SimpleDateFormat("yyyyMMdd").format(new Date()) + fileName; response.reset(); response.setCharacterEncoding("utf-8"); response.setContentType("application/download"); response.setHeader("Content-Disposition", "attachment; filename=" + new String(title.getBytes("utf-8"), "iso8859-1"));//设置文件名显示中文 String startDate = request.getParameter("startDate"); String endDate = request.getParameter("endDate"); String meterIds = request.getParameter("meterIds"); String pageSize = request.getParameter("pageSize"); String currentPage = request.getParameter("currentPage"); String statistics = request.getParameter("statistics"); String exportWay = request.getParameter("exportWay"); Map<String, String> map = new HashMap<String, String>(); map.put("startDate", startDate); map.put("endDate", endDate); map.put("meterIds", meterIds); map.put("currentPage", currentPage); map.put("pageSize", pageSize); if ("all".equals(exportWay)) { map.put("queryAll", "queryAll");//选择查询全部 } map.put("statistics", statistics); ITSBalanceService tsbalanceService = (ITSBalanceService) InitApp.context .getBean("tsbalanceService"); Map<String, Object> data = tsbalanceService.showTSBalance(map); List<TSBalance> projectNodeList = (List<TSBalance>) data .get("tsbalanceList"); String path = request.getRealPath("/excelTemplate").replace("\\", "/"); String filePath = path + "/" + fileName; File file = new File(filePath); InputStream is = null; try { is = new FileInputStream(file); Workbook wb = new HSSFWorkbook(is); int sheetsNum = wb.getNumberOfSheets(); //找到对应的sheet for (int i = 0; i < sheetsNum; i++) { String sheetName = wb.getSheetName(i); int lastRowNum = 0; //如果是平衡报表 if (CommonUtil.SHEET_BALANCE.equals(sheetName)) { Sheet sheet = wb.getSheet(sheetName); lastRowNum = sheet.getLastRowNum(); //循环row,获得需要的cell for (int r = 4; r <= lastRowNum; r++) { Row row = sheet.getRow(r); //电表地址所在的单元格 Cell meterCell = row.getCell(4); //正向有功起码所在的单元格,获得cell的样式 Cell PP1Cell = row.getCell(7); CellStyle PP1CellStyle = PP1Cell.getCellStyle(); //正向有功止码所在的单元格,获得cell的样式 Cell PP2Cell = row.getCell(8); CellStyle PP2CellStyle = PP2Cell.getCellStyle(); //反向有功起码所在的单元格,获得cell的样式 Cell PP4Cell = row.getCell(11); CellStyle PP4CellStyle = PP4Cell.getCellStyle(); //反向有功止码所在的单元格,获得cell的样式 Cell PP5Cell = row.getCell(12); CellStyle PP5CellStyle = PP5Cell.getCellStyle(); //从电表地址单元格中取出值 String meterValue = ""; //数值单元格取值 if (meterCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int cellValue = (int) meterCell .getNumericCellValue(); meterValue = Integer.toString(cellValue); } //文本单元格取值 else if (meterCell.getCellType() == Cell.CELL_TYPE_STRING) { meterValue = meterCell.getStringCellValue(); } //从页面传的list中找到对应的电表地址,并将起码、止码写入 for (TSBalance balance : projectNodeList) { //如果模板中的电表地址和list中的电表地址相等,将起码、止码写入对应cell中; String meterAddress = balance.getAddress()==null?"":balance.getAddress(); String pp1 = balance.getPP1()==null?"":balance.getPP1(); String pp2 = balance.getPP2()==null?"":balance.getPP2(); String pp4 = balance.getPP4()==null?"":balance.getPP4(); String pp5 = balance.getPP5()==null?"":balance.getPP5(); if (!"".equals(meterAddress)&& meterValue.equals(meterAddress)) { //判断是否为-1,不为-1就修改单元格的值 if (!CommonUtil.judge(pp1)) { PP1Cell.setCellValue(pp1); PP1Cell.setCellStyle(PP1CellStyle); } if (!CommonUtil.judge(pp2)) { PP2Cell.setCellValue(pp2); PP2Cell.setCellStyle(PP2CellStyle); } if (!CommonUtil.judge(pp4)) { PP4Cell.setCellValue(pp4); PP4Cell.setCellStyle(PP4CellStyle); } if (!CommonUtil.judge(pp5)) { PP5Cell.setCellValue(pp5); PP5Cell.setCellStyle(PP5CellStyle); } } } } } //如果是10kv线损表 else if (CommonUtil.SHEET_LINE_LOSS.equals(sheetName)) { Sheet sheet = wb.getSheet(sheetName); lastRowNum = sheet.getLastRowNum(); for (int r = 2; r < lastRowNum; r++) { Row row = sheet.getRow(r); //电表地址所在的单元格 Cell meterCell = row.getCell(4); //电量类的标识的单元格 Cell typeCell = row.getCell(3); //本月止码所在的单元格 Cell checkCodeCell = row.getCell(6); CellStyle chcekCodeStyle = checkCodeCell.getCellStyle(); //本月起码所在的单元格 Cell startCodeCell = row.getCell(5); CellStyle startCodeStyle = startCodeCell.getCellStyle(); //从电表地址单元格中取出值 String meterValue = ""; //数值单元格取值 if (meterCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int cellValue = (int) meterCell.getNumericCellValue(); meterValue = Integer.toString(cellValue); } //文本单元格取值 else if (meterCell.getCellType() == Cell.CELL_TYPE_STRING) { meterValue = meterCell.getStringCellValue(); } //取电量标识值 int typeValue = 0; //数值单元格取值 if (typeCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { typeValue = (int) typeCell.getNumericCellValue(); } //文本单元格取值 else if (typeCell.getCellType() == Cell.CELL_TYPE_STRING) { typeValue = Integer.parseInt(typeCell.getStringCellValue()); } //取本月止碼 String checkCodeValue = ""; //数值单元格取值 if (checkCodeCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { checkCodeValue = Double.toString(checkCodeCell.getNumericCellValue()); } //文本单元格取值 else if (checkCodeCell.getCellType() == Cell.CELL_TYPE_STRING) { checkCodeValue = checkCodeCell.getStringCellValue(); } //将本月止码移动到本月起码 startCodeCell.setCellType(Cell.CELL_TYPE_STRING); startCodeCell.setCellValue(checkCodeValue); startCodeCell.setCellStyle(startCodeStyle); for (TSBalance balance : projectNodeList) { String meterAddress = balance.getAddress()==null?"":balance.getAddress(); String pp2 = balance.getPP2()==null?"":balance.getPP2(); String pp5 = balance.getPP5()==null?"":balance.getPP5(); String pp8 = balance.getPP8()==null?"":balance.getPP8(); String pp10 = balance.getPP10()==null?"":balance.getPP10(); if (!"".equals(meterAddress)&& meterValue.equals(meterAddress)) { if(CommonUtil.judge(pp2)){ checkCodeCell.setCellValue(""); }else{ //正向有功 if (typeValue == CommonUtil.TYPE_PP2) { checkCodeCell.setCellType(Cell.CELL_TYPE_STRING); checkCodeCell.setCellValue(pp2); checkCodeCell.setCellStyle(chcekCodeStyle); } } if(CommonUtil.judge(pp5)){ checkCodeCell.setCellValue(""); }else { //反向有功 if (typeValue == CommonUtil.TYPE_PP5) { checkCodeCell.setCellType(Cell.CELL_TYPE_STRING); checkCodeCell.setCellValue(pp5); checkCodeCell.setCellStyle(chcekCodeStyle); } } if(CommonUtil.judge(pp8)){ checkCodeCell.setCellValue(""); } else { //正向无功 if (typeValue == CommonUtil.TYPE_PP8) { checkCodeCell.setCellType(Cell.CELL_TYPE_STRING); checkCodeCell.setCellValue(pp8); checkCodeCell.setCellStyle(chcekCodeStyle); } } if(CommonUtil.judge(pp10)){ checkCodeCell.setCellValue(""); }else { //反向无功 if (typeValue == CommonUtil.TYPE_PP10) { checkCodeCell.setCellType(Cell.CELL_TYPE_STRING); checkCodeCell.setCellValue(pp10); checkCodeCell.setCellStyle(chcekCodeStyle); } } } } } } else { msg = "你所提交的模板不可用"; break; } } wb.write(response.getOutputStream()); } catch (Exception e) { e.printStackTrace(); } finally { try { if (is != null) is.close(); } catch (IOException e) { e.printStackTrace(); } } out.clear(); out = pageContext.pushBody();%> public static HSSFWorkbook createExcel() throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet; HSSFRow row; HSSFCell cell; sheet = workbook.createSheet(); row = sheet.createRow(0); cell = row.createCell(0); cell.setCellValue("第一行第一列"); cell = row.createCell(1); cell.setCellValue("第一行第二列"); cell = row.createCell(2); cell.setCellValue("第一行第三列"); cell = row.createCell(3); cell.setCellValue("第一行第四列"); cell = row.createCell(4); cell.setCellValue("第一行第五列"); cell = row.createCell(5); cell.setCellValue("第一行第六列"); for (int i = 0; i < 30000; i++) { row = sheet.createRow(i + 1); cell = row.createCell(0); cell.setCellValue("123"); cell = row.createCell(1); cell.setCellValue("123"); cell = row.createCell(2); cell.setCellValue("123"); cell = row.createCell(3); cell.setCellValue(""); cell = row.createCell(4); cell.setCellValue(""); cell = row.createCell(5); cell.setCellValue("123"); } return workbook; } 反编译后的代码问题 HibernateCallBack ssh 自己做的分页标签 struts2 提交验证问题 给点建议 一段关于BASE64编解码的代码,请高手指教 我下了FCKEditor for java版的,在Tomcat上安装怎么老是有问题,总是提示找不到包,请帮看看? 怎样在数据库上实现struts资源文件? 请问各位在JB7里面怎么指定CMP2.0所使用的DataSource的JNDI阿? 业务数据和账号数据软删除时的wen'ti jsp C#和Java区别
jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@page import="java.text.SimpleDateFormat"%><%@page import="org.apache.poi.ss.usermodel.CellStyle"%>
<%@page import="com.yinjun.common.InitApp"%>
<%@page import="com.yinjun.service.ITSBalanceService"%>
<%@page import="com.yinjun.entity.TSBalance"%>
<%@page import="java.util.*"%>
<%@page import="org.apache.poi.ss.usermodel.Cell"%>
<%@page import="org.apache.poi.ss.usermodel.Row"%>
<%@page import="org.apache.poi.ss.usermodel.Sheet"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@page import="org.apache.poi.ss.usermodel.Workbook"%>
<%@page import="java.io.IOException"%>
<%@page import="java.io.FileInputStream"%><%@page import="java.io.InputStream"%>
<%@ page import="java.io.File"%>
<%@ page import="com.yinjun.util.*"%>
<%
request.setCharacterEncoding("utf-8");
String msg = "";
String fileName = request.getParameter("fileName"); int index = fileName.lastIndexOf("\\");
if (index > 0) {
fileName = fileName.substring(index + 1);
} String title = new SimpleDateFormat("yyyyMMdd").format(new Date())
+ fileName;
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/download");
response.setHeader("Content-Disposition", "attachment; filename="
+ new String(title.getBytes("utf-8"), "iso8859-1"));//设置文件名显示中文
String startDate = request.getParameter("startDate");
String endDate = request.getParameter("endDate");
String meterIds = request.getParameter("meterIds");
String pageSize = request.getParameter("pageSize");
String currentPage = request.getParameter("currentPage");
String statistics = request.getParameter("statistics");
String exportWay = request.getParameter("exportWay"); Map<String, String> map = new HashMap<String, String>();
map.put("startDate", startDate);
map.put("endDate", endDate);
map.put("meterIds", meterIds);
map.put("currentPage", currentPage);
map.put("pageSize", pageSize);
if ("all".equals(exportWay)) {
map.put("queryAll", "queryAll");//选择查询全部
}
map.put("statistics", statistics);
ITSBalanceService tsbalanceService = (ITSBalanceService) InitApp.context
.getBean("tsbalanceService");
Map<String, Object> data = tsbalanceService.showTSBalance(map);
List<TSBalance> projectNodeList = (List<TSBalance>) data
.get("tsbalanceList"); String path = request.getRealPath("/excelTemplate").replace("\\",
"/");
String filePath = path + "/" + fileName;
File file = new File(filePath); InputStream is = null;
try {
is = new FileInputStream(file);
Workbook wb = new HSSFWorkbook(is);
int sheetsNum = wb.getNumberOfSheets();
//找到对应的sheet
for (int i = 0; i < sheetsNum; i++) {
String sheetName = wb.getSheetName(i);
int lastRowNum = 0;
//如果是平衡报表
if (CommonUtil.SHEET_BALANCE.equals(sheetName)) {
Sheet sheet = wb.getSheet(sheetName);
lastRowNum = sheet.getLastRowNum();
//循环row,获得需要的cell
for (int r = 4; r <= lastRowNum; r++) {
Row row = sheet.getRow(r);
//电表地址所在的单元格
Cell meterCell = row.getCell(4);
//正向有功起码所在的单元格,获得cell的样式
Cell PP1Cell = row.getCell(7);
CellStyle PP1CellStyle = PP1Cell.getCellStyle();
//正向有功止码所在的单元格,获得cell的样式
Cell PP2Cell = row.getCell(8);
CellStyle PP2CellStyle = PP2Cell.getCellStyle();
//反向有功起码所在的单元格,获得cell的样式
Cell PP4Cell = row.getCell(11);
CellStyle PP4CellStyle = PP4Cell.getCellStyle();
//反向有功止码所在的单元格,获得cell的样式
Cell PP5Cell = row.getCell(12);
CellStyle PP5CellStyle = PP5Cell.getCellStyle();
//从电表地址单元格中取出值
String meterValue = "";
//数值单元格取值
if (meterCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int cellValue = (int) meterCell
.getNumericCellValue();
meterValue = Integer.toString(cellValue);
}
//文本单元格取值
else if (meterCell.getCellType() == Cell.CELL_TYPE_STRING) {
meterValue = meterCell.getStringCellValue(); }
//从页面传的list中找到对应的电表地址,并将起码、止码写入
for (TSBalance balance : projectNodeList) {
//如果模板中的电表地址和list中的电表地址相等,将起码、止码写入对应cell中;
String meterAddress = balance.getAddress()==null?"":balance.getAddress();
String pp1 = balance.getPP1()==null?"":balance.getPP1();
String pp2 = balance.getPP2()==null?"":balance.getPP2();
String pp4 = balance.getPP4()==null?"":balance.getPP4();
String pp5 = balance.getPP5()==null?"":balance.getPP5();
if (!"".equals(meterAddress)&& meterValue.equals(meterAddress)) {
//判断是否为-1,不为-1就修改单元格的值
if (!CommonUtil.judge(pp1)) {
PP1Cell.setCellValue(pp1);
PP1Cell.setCellStyle(PP1CellStyle);
}
if (!CommonUtil.judge(pp2)) {
PP2Cell.setCellValue(pp2);
PP2Cell.setCellStyle(PP2CellStyle);
} if (!CommonUtil.judge(pp4)) {
PP4Cell.setCellValue(pp4);
PP4Cell.setCellStyle(PP4CellStyle);
} if (!CommonUtil.judge(pp5)) {
PP5Cell.setCellValue(pp5);
PP5Cell.setCellStyle(PP5CellStyle);
} }
} }
}
//如果是10kv线损表
else if (CommonUtil.SHEET_LINE_LOSS.equals(sheetName)) {
Sheet sheet = wb.getSheet(sheetName);
lastRowNum = sheet.getLastRowNum();
for (int r = 2; r < lastRowNum; r++) {
Row row = sheet.getRow(r);
//电表地址所在的单元格
Cell meterCell = row.getCell(4);
//电量类的标识的单元格
Cell typeCell = row.getCell(3);
//本月止码所在的单元格
Cell checkCodeCell = row.getCell(6);
CellStyle chcekCodeStyle = checkCodeCell.getCellStyle();
//本月起码所在的单元格
Cell startCodeCell = row.getCell(5);
CellStyle startCodeStyle = startCodeCell.getCellStyle();
//从电表地址单元格中取出值
String meterValue = "";
//数值单元格取值
if (meterCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int cellValue = (int) meterCell.getNumericCellValue();
meterValue = Integer.toString(cellValue);
}
//文本单元格取值
else if (meterCell.getCellType() == Cell.CELL_TYPE_STRING) {
meterValue = meterCell.getStringCellValue();
} //取电量标识值
int typeValue = 0;
//数值单元格取值
if (typeCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
typeValue = (int) typeCell.getNumericCellValue();
}
//文本单元格取值
else if (typeCell.getCellType() == Cell.CELL_TYPE_STRING) {
typeValue = Integer.parseInt(typeCell.getStringCellValue());
} //取本月止碼
String checkCodeValue = "";
//数值单元格取值
if (checkCodeCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
checkCodeValue = Double.toString(checkCodeCell.getNumericCellValue());
}
//文本单元格取值
else if (checkCodeCell.getCellType() == Cell.CELL_TYPE_STRING) {
checkCodeValue = checkCodeCell.getStringCellValue(); }
//将本月止码移动到本月起码
startCodeCell.setCellType(Cell.CELL_TYPE_STRING);
startCodeCell.setCellValue(checkCodeValue);
startCodeCell.setCellStyle(startCodeStyle);
for (TSBalance balance : projectNodeList) {
String meterAddress = balance.getAddress()==null?"":balance.getAddress();
String pp2 = balance.getPP2()==null?"":balance.getPP2();
String pp5 = balance.getPP5()==null?"":balance.getPP5();
String pp8 = balance.getPP8()==null?"":balance.getPP8();
String pp10 = balance.getPP10()==null?"":balance.getPP10();
if (!"".equals(meterAddress)&& meterValue.equals(meterAddress)) {
if(CommonUtil.judge(pp2)){
checkCodeCell.setCellValue("");
}else{
//正向有功
if (typeValue == CommonUtil.TYPE_PP2) {
checkCodeCell.setCellType(Cell.CELL_TYPE_STRING);
checkCodeCell.setCellValue(pp2);
checkCodeCell.setCellStyle(chcekCodeStyle);
}
}
if(CommonUtil.judge(pp5)){
checkCodeCell.setCellValue("");
}else {
//反向有功
if (typeValue == CommonUtil.TYPE_PP5) {
checkCodeCell.setCellType(Cell.CELL_TYPE_STRING);
checkCodeCell.setCellValue(pp5);
checkCodeCell.setCellStyle(chcekCodeStyle);
}
}
if(CommonUtil.judge(pp8)){
checkCodeCell.setCellValue("");
} else {
//正向无功
if (typeValue == CommonUtil.TYPE_PP8) {
checkCodeCell.setCellType(Cell.CELL_TYPE_STRING);
checkCodeCell.setCellValue(pp8);
checkCodeCell.setCellStyle(chcekCodeStyle);
}
}
if(CommonUtil.judge(pp10)){
checkCodeCell.setCellValue("");
}else {
//反向无功
if (typeValue == CommonUtil.TYPE_PP10) {
checkCodeCell.setCellType(Cell.CELL_TYPE_STRING);
checkCodeCell.setCellValue(pp10);
checkCodeCell.setCellStyle(chcekCodeStyle);
}
}
}
}
}
} else {
msg = "你所提交的模板不可用";
break;
}
}
wb.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (is != null)
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
out.clear();
out = pageContext.pushBody();
%>
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet;
HSSFRow row;
HSSFCell cell;
sheet = workbook.createSheet();
row = sheet.createRow(0);
cell = row.createCell(0);
cell.setCellValue("第一行第一列");
cell = row.createCell(1);
cell.setCellValue("第一行第二列");
cell = row.createCell(2);
cell.setCellValue("第一行第三列");
cell = row.createCell(3);
cell.setCellValue("第一行第四列");
cell = row.createCell(4);
cell.setCellValue("第一行第五列");
cell = row.createCell(5);
cell.setCellValue("第一行第六列");
for (int i = 0; i < 30000; i++) {
row = sheet.createRow(i + 1);
cell = row.createCell(0);
cell.setCellValue("123");
cell = row.createCell(1);
cell.setCellValue("123");
cell = row.createCell(2);
cell.setCellValue("123");
cell = row.createCell(3);
cell.setCellValue("");
cell = row.createCell(4);
cell.setCellValue("");
cell = row.createCell(5);
cell.setCellValue("123");
}
return workbook;
}