java用POI导入导出Excel数据导数据库

最新推荐文章于 2025-05-09 19:54:14 发布

java开发小白

最新推荐文章于 2025-05-09 19:54:14 发布

阅读量426

点赞数

CC 4.0 BY-SA版权

分类专栏： Spring

本文链接：https://blog.csdn.net/qq_32914323/article/details/87685368

Spring 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了如何使用Java的Apache POI库来实现Excel数据的导入和导出。首先展示了HTML页面上的文件上传表单，然后提供了一个JavaScript函数用于验证上传文件的类型。接着，给出了Spring MVC后台的处理方法，读取上传的Excel文件并进行数据处理。此外，还提到了导出Excel文件的方法，包括前端的Ajax调用和后端的Java代码，用于生成并下载Excel文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Excel导入导出：

http://www.cnblogs.com/Damon-Luo/p/5919656.html

导入Excel数据：

页面

</div>

//用于验证文件扩展名的正则表达式

function checkSuffix() {

var name = document.getElementById("file").value;

var strRegex = "(.xls|.xlsx|.xlsb|.xlsm|.xlst)$";

var re = new RegExp(strRegex);

if (re.test(name.toLowerCase())) {

alert("上传成功");

document.fileupload.submit();

} else {

alert("文件名不合法");

}

</form>配置

</bean>

后台接收

@RequestMapping(value="/importExcel")

public ModelAndView importExcel(HttpServletRequest request, HttpServletResponse response)

{

MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

// 得到上传的文件

MultipartFile deviceExcel = multipartRequest.getFile("file");

ModelAndView mv=new ModelAndView();

mv.setViewName("success");

try {

Workbook wookbook = new XSSFWorkbook(deviceExcel.getInputStream());//得到工作簿

Sheet sheet = wookbook.getSheetAt(0); // 从工作区中取得页（Sheet）

//获得表头

Row rowHead = sheet.getRow(0);

//判断表头是否正确

if(rowHead.getPhysicalNumberOfCells() != 2)

{

System.out.println("表头的数量不对!");

}

//获得数据的总行数

int totalRowNum = sheet.getLastRowNum();

//要获得属性

String name = "";

Double latitude = null;

//获得所有数据

for(int i = 1 ; i <= totalRowNum ; i++)

{

//获得第i行对象

Row row = sheet.getRow(i);

//获得获得第i行第0列的 String类型对象

Cell cell = row.getCell((short)0);

// name = cell.getStringCellValue().toString();

name = (String) getRightTypeCell(cell);

//获得一个数字类型的数据

cell = row.getCell((short)1);

//latitude = (int) cell.getNumericCellValue();

latitude = (Double) getRightTypeCell(cell);

System.out.println("名字："+name+",年龄："+latitude);

}

} catch (Exception e) {

e.printStackTrace();

}

return mv;

}

/**

* @param cell 一个单元格的对象

* @return 返回该单元格相应的类型的值

public static Object getRightTypeCell(Cell cell){

Object object = null;

switch(cell.getCellType())

{

case Cell.CELL_TYPE_STRING :

{

object=cell.getStringCellValue();

break;

}

case Cell.CELL_TYPE_NUMERIC :

{

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

object=cell.getNumericCellValue();

break;

}

case Cell.CELL_TYPE_FORMULA :

{

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

object=cell.getNumericCellValue();

break;

}

case Cell.CELL_TYPE_BLANK :

{

cell.setCellType(Cell.CELL_TYPE_BLANK);

object=cell.getStringCellValue();

break;

}

return object;

}

导入Excel数据：

js代码

function exportEngExcel(){

$.ajaxFileUpload({

url : Utils.getRootPath()+'/engMonthlyDetail/exportEngExcel?engCode='+engCode,

secureuri : false,

type:"post",

dataType:'json',

success : function(data) {

error:function(d){

}

});

}

需要引入下面的js：

jQuery.extend({

createUploadIframe: function(id, uri)

{

//create frame

var frameId = 'jUploadFrame' + id;

var iframeHtml = '<iframe id="' + frameId + '" name="' + frameId + '" style="position:absolute; top:-9999px; left:-9999px"';

if(window.ActiveXObject)

{

if(typeof uri== 'boolean'){

iframeHtml += ' src="' + 'javascript:false' + '"';

}

else if(typeof uri== 'string'){

iframeHtml += ' src="' + uri + '"';

}

iframeHtml += ' />';

jQuery(iframeHtml).appendTo(document.body);

return jQuery('#' + frameId).get(0);

createUploadForm: function(id,fileElementId,data,fileElement)

{

//create form

var formId = 'jUploadForm' + id;

var fileId = 'jUploadFile' + id;

var form = jQuery('<form action="" method="POST" name="' + formId + '" id="' + formId + '" enctype="multipart/form-data"></form>');

if(data)

{

for(var i in data)

{

jQuery('<input type="hidden" name="' + i + '" value="' + data[i] + '" />').appendTo(form);

}

// var oldElement;

// if(fileElement == null)

// oldElement = jQuery('#' + fileElementId);

// else

// oldElement = fileElement;

// var newElement = jQuery(oldElement).clone();

// jQuery(oldElement).attr('id', fileId);

// jQuery(oldElement).before(newElement);

// jQuery(oldElement).appendTo(form);

if(typeof(fileElementId) == 'string'){

fileElementId = [fileElementId];

}

for(var i in fileElementId){

var oldElement = jQuery('#' + fileElementId[i]);

var newElement = jQuery(oldElement).clone();

jQuery(oldElement).attr('id', fileId);

jQuery(oldElement).before(newElement);

jQuery(oldElement).appendTo(form);

}

//set attributes

jQuery(form).css('position', 'absolute');

jQuery(form).css('top', '-1200px');

jQuery(form).css('left', '-1200px');

jQuery(form).appendTo('body');

return form;

ajaxFileUpload: function(s) {

// TODO introduce global settings, allowing the client to modify them for all requests, not only timeout

s = jQuery.extend({}, jQuery.ajaxSettings, s);

var id = new Date().getTime()

var form = jQuery.createUploadForm(id, s.fileElementId, (typeof(s.data)=='undefined'?false:s.data),s.fileElement);

var io = jQuery.createUploadIframe(id, s.secureuri);

var frameId = 'jUploadFrame' + id;

var formId = 'jUploadForm' + id;

// Watch for a new set of requests

if ( s.global && ! jQuery.active++ )

{

jQuery.event.trigger( "ajaxStart" );

}

var requestDone = false;

// Create the request object

var xml = {}

if ( s.global )

jQuery.event.trigger("ajaxSend", [xml, s]);

// Wait for a response to come back

var uploadCallback = function(isTimeout)

{

var io = document.getElementById(frameId);

try

{

if(io.contentWindow)

{

xml.responseText = io.contentWindow.document.body?io.contentWindow.document.body.innerHTML:null;

xml.responseXML = io.contentWindow.document.XMLDocument?io.contentWindow.document.XMLDocument:io.contentWindow.document;

}else if(io.contentDocument)

{

xml.responseText = io.contentDocument.document.body?io.contentDocument.document.body.innerHTML:null;

xml.responseXML = io.contentDocument.document.XMLDocument?io.contentDocument.document.XMLDocument:io.contentDocument.document;

}

}catch(e)

{

jQuery.handleError(s, xml, null, e);

}

if ( xml || isTimeout == "timeout")

{

requestDone = true;

var status;

try {

status = isTimeout != "timeout" ? "success" : "error";

// Make sure that the request was successful or notmodified

if ( status != "error" )

{

// process the data (runs the xml through httpData regardless of callback)

var data = jQuery.uploadHttpData( xml, s.dataType );

// If a local callback was specified, fire it and pass it the data

if ( s.success )

s.success( data, status );

// Fire the global callback

if( s.global )

jQuery.event.trigger( "ajaxSuccess", [xml, s] );

} else

jQuery.handleError(s, xml, status);

} catch(e)

{

status = "error";

jQuery.handleError(s, xml, status, e);

}

// The request was completed

if( s.global )

jQuery.event.trigger( "ajaxComplete", [xml, s] );

// Handle the global AJAX counter

if ( s.global && ! --jQuery.active )

jQuery.event.trigger( "ajaxStop" );

// Process result

if ( s.complete )

s.complete(xml, status);

jQuery(io).unbind();

setTimeout(function()

{ try

{

jQuery(io).remove();

jQuery(form).remove();

} catch(e)

{

jQuery.handleError(s, xml, null, e);

}

}, 100);

xml = null;

}

// Timeout checker

if ( s.timeout > 0 )

{

setTimeout(function(){

// Check to see if the request is still happening

if( !requestDone ) uploadCallback( "timeout" );

}, s.timeout);

}

try

{

var form = jQuery('#' + formId);

jQuery(form).attr('action', s.url);

jQuery(form).attr('method', 'POST');

jQuery(form).attr('target', frameId);

if(form.encoding)

{

jQuery(form).attr('encoding', 'multipart/form-data');

}

else

{

jQuery(form).attr('enctype', 'multipart/form-data');

}

jQuery(form).submit();

} catch(e)

{

jQuery.handleError(s, xml, null, e);

}

jQuery('#' + frameId).load(uploadCallback);

return {abort: function(){

try

{

jQuery('#' + frameId).remove();

jQuery(form).remove();

}

catch(e){}

}};

uploadHttpData: function( r, type ) {

var data = !type;

data = type == "xml" || data ? r.responseXML : r.responseText;

// If the type is "script", eval it in global context

if ( type == "script" )

jQuery.globalEval( data );

// Get the JavaScript object, if JSON is used.

if ( type == "json" )

以下为新增代码///

data = r.responseText;

var start = data.indexOf(">");

if(start != -1) {

var end = data.indexOf("<", start + 1);

if(end != -1) {

data = data.substring(start + 1, end);

}

///以上为新增代码///

eval( "data = " + data );

// evaluate scripts within html

if ( type == "html" )

jQuery("<div>").html(data).evalScripts();

return data;

handleError: function( s, xml, status, e ) {

// If a local callback was specified, fire it

if ( s.error )

s.error( xml, status, e );

// Fire the global callback

if ( s.global )

jQuery.event.trigger( "ajaxError", [xml, s, e] );

}

});

Java后台的代码

/**

* 导出工程项目

* @param request

* @param response

* @return

@RequestMapping(value = "exportEngExcel")

public void exportEngExcel(HttpServletRequest request,HttpServletResponse response,String engCode) {

UserInfo user = getSessionUser(request);

List<RoleInfo> roleList = user.getRoleInfoList();

List<QueryCondition> param = new ArrayList<QueryCondition>();

List<Map<String, Object>> englist = new ArrayList<Map<String,Object>>();

param.add(new QueryCondition("engCode", engCode, ConditionType.EQ, FieldType.STRING, "ENG"));

boolean isSuperAdmin = false;

if(!BaseUtil.isListEmpty(roleList)){

for (RoleInfo roleInfo : roleList) {

if(UserTypeCode.超级管理员.getValue().equals(roleInfo.getRoleCode())){

isSuperAdmin = true;

break;

}

if(isSuperAdmin){

englist= engMonthlyDetailService.getAllMonthlyDetailList(param);

}else{

String entrpCode = user.getEntrpCode();

param.add(new QueryCondition("entrpCode", entrpCode, ConditionType.IN, FieldType.STRING, "ENG"));

englist= engMonthlyDetailService.getAllMonthlyDetailList(param);

}

try {

HSSFWorkbook wb = getCommonMonthExcelObj(englist);

response.addHeader("content-type","application/x-msdownload");

response.addHeader("Content-Disposition", "attachment;filename=" + new String(("工程类完成情况导出模板.xls").getBytes("GB2312"),"ISO-8859-1"));

ServletOutputStream out = response.getOutputStream();

response.setContentType("application/vnd.ms-excel;charset=gb2312");

wb.write(out);

out.flush();

out.close();

} catch (Exception e) {

e.printStackTrace();

}

/**

* 根据数据生成excel表格

* @param englist

* @return

public HSSFWorkbook getCommonMonthExcelObj(List<Map<String, Object>> englist){

String[] title={"项目名称","项目编码","报送期","本期完成合同额","海外工程结汇美元数量","海外工程结汇人民币数量","结汇平均汇率","被拖欠工程款","水泥购买量","钢材购买量"};

// 第一步，创建一个webbook，对应一个Excel文件

HSSFWorkbook wb = new HSSFWorkbook();

// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet

HSSFSheet sheet = wb.createSheet("工程类项目表");

// 设置单元格大小

//sheet.setDefaultRowHeight((short) (2 * 300));

sheet.setDefaultRowHeightInPoints(20);

sheet.setDefaultColumnWidth(28);

sheet.protectSheet("123");

第一行的标题/

/*Map<String, Object> cellMap2 = englist.get(0);

HSSFRow rowTitle2 = sheet.createRow((int) 0);

HSSFCellStyle styleTitle0 = wb.createCellStyle();

styleTitle0.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));

HSSFFont fTitleRow0 = wb.createFont();

fTitleRow0.setFontHeightInPoints((short) 15);// 字号

fTitleRow0.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗

styleTitle0.setFont(fTitleRow0);

styleTitle0.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

styleTitle0.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

styleTitle0.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

styleTitle0.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

HSSFCell cell0 = rowTitle2.createCell(0);

cell0.setCellValue(BaseUtil.retStr(cellMap2.get("ENGNAME")+"项目的完成情况"));

cell0.setCellStyle(styleTitle0);*/

//第一行的标题//

// 添加标题,第0行

HSSFRow rowTitle = sheet.createRow((int) 0);

// 第三步，创建单元格，并设置值表头设置表头居中

HSSFCellStyle style = wb.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

// 表头信息标题字号设置

HSSFFont rowStyle = wb.createFont();

rowStyle.setFontHeightInPoints((short) 12);// 字号

rowStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗

style.setFont(rowStyle);

style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

for(int i=0;i<title.length;i++){

HSSFCell cellTitle0 = rowTitle.createCell((short) i);

cellTitle0.setCellValue(title[i]);

cellTitle0.setCellStyle(style);

}

//单元格样式

HSSFCellStyle styleLock = wb.createCellStyle();

styleLock.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

// 数据信息标题字号设置

HSSFFont dataStyle = wb.createFont();

dataStyle.setFontHeightInPoints((short) 10);// 字号

styleLock.setFont(dataStyle);

styleLock.setLocked(true);

styleLock.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());

styleLock.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

styleLock.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

styleLock.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

styleLock.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

styleLock.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

//单元格样式

HSSFCellStyle stylecell = wb.createCellStyle();

stylecell.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

// 数据信息标题字号设置

HSSFFont datacell = wb.createFont();

datacell.setFontHeightInPoints((short) 10);// 字号

stylecell.setFont(datacell);

stylecell.setLocked(false);

stylecell.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

stylecell.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

stylecell.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

stylecell.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

for(int i=0;i<englist.size();i++){

HSSFRow row = sheet.createRow((int) 1+i);

//row.setRowStyle(styleLock);

Map<String, Object> cellMap = englist.get(i);

HSSFCell cellData = row.createCell((short) 0);

cellData.setCellValue(BaseUtil.retStr(cellMap.get("ENGNAME")));

cellData.setCellStyle(styleLock);

cellData = row.createCell((short) 1);

cellData.setCellValue(BaseUtil.retStr(cellMap.get("ENGCODE")));

cellData.setCellStyle(styleLock);

cellData = row.createCell((short) 2);

cellData.setCellValue(BaseUtil.retStr(cellMap.get("CREATETIME")));

cellData.setCellStyle(styleLock);

cellData = row.createCell((short) 3);

cellData.setCellValue(BaseUtil.retStr(cellMap.get("FINISHAMOUNT")));