前后端代码及实现效果:
## ssm框架+servlet实现
导入:(poi-3.7的版本不支持2007版及以上的excel文件,所以这里使用3.9版本)
poi-3.9-20121203.jar
poi-ooxml-3.9.jar
poi-ooxml-3.9-sources.jar
poi-ooxml-schemas-3.9-20121203.jar
dom4j-1.6.1.jar
xmlbeans-2.3.0.jar(如果不导入这个会报错!!!)
代码:
- ExcelServlet导出模板
public class ExcelServlet extends HttpServlet {
/**
*导出模板
*/
@Autowired
private static IGeneralDao generalDao;
static {
generalDao = (IGeneralDao) Dispatcher.getBean("generalDao");
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String parameter = req.getParameter("arg");
if ("chfpi".equals(parameter)) {
HSSFWorkbook workbook = null;
String fileName = "chfp报考管理模板"+ ".xls"; // Excel文件名
OutputStream os = null;
workbook = new DownloadClientTemplate().buildExcelDocument();
resp.reset();// 清空输出流
// web浏览通过MIME类型判断文件是excel类型
resp.setHeader("Content-type",
"application/vnd.ms-excel;charset=UTF-8");
// 对文件名进行处理。防止文件名乱码
// Content-disposition属性设置成以附件方式进行下载
resp.addHeader("Content-Disposition", "attachment;filename="
+ new String(fileName.getBytes("gb2312"), "ISO8859-1"));
os = resp.getOutputStream();// 取得输出流
workbook.write(os);
}
}
- DownloadClientTemplate excel的工具类(具体模板内容)
public class DownloadClientTemplate {
public HSSFWorkbook buildExcelDocument() {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 12); // 设置字体的大小
font.setFontName("宋体"); // 设置字体的样式，如：宋体、微软雅黑等
font.setItalic(false); // 斜体true为斜体
HSSFCellStyle style = workbook.createCellStyle();
Sheet sheetHome = workbook.createSheet("首页");
// 案例展示2行
String[][] exampleStr = new String[][] {
{ "eg:", "姓名", "手机号码", "考试批", "考试次", "金额" },
{ "", "张三", "185xxxxxxxx", "18年6月", "补考一批", "200" },
{ "", "李四", "185xxxxxxxx", "18年6月", "补考一批", "200" } };
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
for (int i = 0; i < exampleStr.length; i++) {
Row r = sheetHome.createRow(i);
String[] strings = exampleStr[i];
for (int j = 0; j < strings.length; j++) {
Cell c = r.createCell(j, Cell.CELL_TYPE_STRING);
c.setCellStyle(style);
c.setCellValue(strings[j]);
}
}
// 提示信息
HSSFFont fontRemind = workbook.createFont();
fontRemind.setFontHeightInPoints((short) 12); // 设置字体的大小
fontRemind.setFontName("宋体"); // 设置字体的样式，如：宋体、微软雅黑等
fontRemind.setItalic(false); // 斜体true为斜体
HSSFCellStyle styleRemind = workbook.createCellStyle();
fontRemind.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 对文中进行加粗
fontRemind.setColor(HSSFColor.RED.index); // 设置字体的颜色
styleRemind.setFont(fontRemind);
Row rowHead = sheetHome.createRow(3);
Cell cellHead = rowHead.createCell(0, Cell.CELL_TYPE_STRING);
cellHead.setCellStyle(styleRemind);
cellHead.setCellValue("姓名、手机号、考试批、考试次、金额为必填项，不能为空！\r\n考试批格式为xx年xx月");
rowHead = sheetHome.createRow(4);
cellHead = rowHead.createCell(0, Cell.CELL_TYPE_STRING);
cellHead.setCellStyle(styleRemind);
cellHead.setCellValue("本页为案列展示页，请跳转至第二页进行操作！");
/*********************************** Sheet第二页 ********************************************/
Sheet sheetInput = workbook.createSheet("客户信息录入");
// HSSFDataValidation setGenderValid = ExcelUtils.setGenderValid();
Row rowInput = sheetInput.createRow(0);
String[] title = new String[] { "姓名", "手机号码", "考试批", "考试次", "金额" };
for (int i = 0; i < title.length; i++) {
Cell c = rowInput.createCell(i, Cell.CELL_TYPE_STRING);
c.setCellStyle(style);
c.setCellValue(title[i]);
}
// sheetInput.addValidationData(setGenderValid);
return workbook;
}
}
- servlet导入数据 (可以用form的submit方式提交,form表单属性中加上enctype=“multipart/form-data”,这样有个bug,submit会跳转页面,所以作者试了用ajax实现,当控制器接收时,可能是框架的问题,@RequestParam(“file”) MultipartFile file一直是空的,所以选择了用servlet)
public class ChfpClassServlet extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = -2483096664722616128L;
@Autowired
private ChfpClassService chfpClassService;
private Logger log = LoggerFactory.getLogger(ChfpClassServlet.class);
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doPost(req, resp);
}
public void init() throws ServletException {
chfpClassService = Dispatcher.getBean("chfpClassService");
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String arg = req.getParameter("arg");
// 使用Apache Common组件中的fileupload进行文件上传
if (arg.equals("import")) {
log.info("importExcel");
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
List<ChfpClass> temp = new ArrayList<ChfpClass>();
InputStream fileIn = null;
FileItem fileItem = null;
Workbook wb0 = null;
String func = req.getParameter("callback");
String ret = null;
// String name=req.getParameter("name");
boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
try {
List items = upload.parseRequest(req);
if (items.size() > 0) {
fileItem = (FileItem) items.get(0);
fileIn = fileItem.getInputStream();
String name = fileItem.getName();
if (isExcel2007(name)) {
isExcel2003 = false;
}
// 根据指定的文件输入流导入Excel从而产生Workbook对象
if (isExcel2003) {
wb0 = new HSSFWorkbook(fileIn);
} else {
wb0 = new XSSFWorkbook(fileIn);
}
SimpleDateFormat sdf = new SimpleDateFormat("yy年MM月");
Sheet sht0 = wb0.getSheetAt(1);//使用导入模板跳过第一张示例表
for (Row r : sht0) {
// 如果当前行的行号（从0开始）未达到2（第三行）则从新循环,就是跳过表头的意思
if (r.getRowNum() < 1) {
continue;
}
Cell numCell = r.getCell(1);
if (numCell != null) {
numCell.setCellType(Cell.CELL_TYPE_STRING);
}
String cellValue = numCell.getStringCellValue();
String patch=sdf.format(r.getCell(2).getDateCellValue());
String batch=r.getCell(3).getStringCellValue();
cellValue.replace("\\D", "");
if (cellValue.length() == 11) {
int result = chfpClassService.findMobile(cellValue,batch,patch);
if (result == 0) {
ChfpClass chfpClass = new ChfpClass();
chfpClass.setCname(r.getCell(0)
.getStringCellValue());
chfpClass.setMobile(cellValue);
chfpClass.setBatch(batch);
chfpClass.setPatch(patch);
chfpClass.setAmount(r.getCell(4)
.getNumericCellValue());
temp.add(chfpClass);
} else {
temp.clear();
ret = "{\"result\":1,\"msg\":\"导入失败!请检查"+patch+batch+"中电话号码"
+ cellValue + "是否已存在!\"}";
}
} else {
ret = "{\"result\":1,\"msg\":\"导入失败!请检查电话号码"
+ cellValue + "的位数\"}";
}
}
if (temp.size() > 0) {
chfpClassService.insertUser(temp);
ret = "{\"result\":0,\"msg\":\"导入成功!请刷新页面\"}";
}
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
ret = "{\"result\":1,\"msg\":\"导入失败!file文件创建异常!\"}";
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
ret = "{\"result\":1,\"msg\":\"导入失败!IO流写入异常!\"}";
} catch (Exception e) {
e.printStackTrace();
ret = "{\"result\":1,\"msg\":\"导入失败!系统异常!\"}";
} finally {
try {
fileIn.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (ret != null) {
try {
PrintWriter writer = resp.getWriter();
writer.write(ret.toCharArray());
writer.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
// @描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}
- 前端html代码:(multiple="multiple"必须有!)
<tr>
<td><a href="../servlet/ExcelServlet?arg=chfpi"
id="exportExcel2" style="display:none"> </a> <input id="export1"
type="button" onclick="exportExcel2()" value="下载模板"/>
<from>
<input type="file" multiple="multiple" id="limgfile"
name="upload" style="width: 200px; display: inline;" /> <input
id="saveZipButton" type="button" value="上传考试数据"
onclick="uploadFile()" /> </from>
</td>
</tr>
- js
function exportExcel2() {
var baseUrl = '../servlet/ExcelServlet?arg=chfpi';
$("#exportExcel1").attr("href", baseUrl);
$("#exportExcel1")[0].click();
}
function uploadFile() {
// alert($('#limgfile').val())
$.ajaxFileUpload({
url : '/WEALTH/chfpClass?arg=import&callback=chfpclass', //需要链接到服务器地址
secureuri : false,
dataType : 'text',
fileElementId : 'limgfile', //文件选择框的id属性
success : function(data) {
data.replace(/\>(.*)\</g, function(g0, g1) {
data = g1;
});
data = eval('(' + data + ')');
if (data.result == 0) {
alert(data.msg);
refresh();
} else {
alert(data.msg);
}
}
});
}
- mabatis.xml里批量插入:
<insert id="insertUser" parameterType="List">
insert into
activitydb.fin2_act3_chfp_class(cname,mobile,batch,patch,amount,estatus,pstatus)
values
<foreach collection="list" separator="," index="index" item="item">
(
#{item.cname},#{item.mobile},#{item.batch},#{item.patch},#{item.amount},'无报名资料','未付费'
)
</foreach>
</insert>
- web.xml里需要配置的内容:
<servlet>
<servlet-name>ExcelServlet</servlet-name>
<servlet-class>com.wealth.control.servlet.ExcelServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>ChfpClassServlet</servlet-name>
<servlet-class>com.wealth.control.servlet.ChfpClassServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ChfpClassServlet</servlet-name>
<url-pattern>/chfpClass</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>ExcelServlet</servlet-name>
<url-pattern>/servlet/ExcelServlet</url-pattern>
</servlet-mapping>
- 实现效果!
具体代码都在这里了,因为有导出模板的功能,所以代码实现的导入数据设置是导入第二张表的数据,如果不需要,可以把代码中Sheet sht0 = wb0.getSheetAt(1);改成需要的表
扫一扫
2398
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
