java用poi实现将数据库里面的数据导入已经存在的excel模板中。最好有实例参考，谢谢。

数据库里面的每一条数据list.get(i)，可能会需要导入excel模板中的一行或者多行。那么循环添加的时候，开始的行号应该怎么确定呢？通过反射得到的对象属性的get(... 数据库里面的每一条数据list.get(i)，可能会需要导入excel模板中的一行或者多行。那么循环添加的时候，开始的行号应该怎么确定呢？通过反射得到的对象属性的get()方法，怎么与list,get(i)拼接后得到相应的值。展开

 我来答

3个回答

#热议# 为什么说不要把裤子提到肚脐眼？

酱了个油了个去
2014-06-10 · TA获得超过474个赞

知道小有建树答主

回答量：412

采纳率：100%

帮助的人：72.8万

我也去答题访问个人页

关注

展开全部

/**
     * 通过EXCEL模板导入团队信息
     */
    @ResponseBody
    @RequestMapping("importTemp")
    public Map<String,String> importTemp(HttpServletRequest request,HttpSession session,
              @RequestParam(value="excel", required=false) MultipartFile file, HttpServletResponse response ) {
        Yhb yhb=(Yhb)session.getAttribute(WebConstants.CURRENT_USER);
        String zjr = yhb.getYhid();
        List<DrTdb> tdxxList = new ArrayList<DrTdb>();
        Map<String,String> map = new HashMap<String,String>();
        String sfcg = "1";
        String bcghs = "";
        String bcgyy = "";
        HSSFWorkbook workbook = null;
        try {
            workbook = new HSSFWorkbook(file.getInputStream());
            HSSFSheet sheet = workbook.getSheetAt(0);  
            if (sheet != null)  
            { 
              int d= sheet.getPhysicalNumberOfRows();
               other: for (int i = 7; i < sheet.getPhysicalNumberOfRows(); i++)  
                {  
                    sfcg = "2";
                    HSSFRow row = sheet.getRow(i);  
                    DrTdb td = new DrTdb();
                    int rs = 1;
                    //td.setTdbm("T"+DateUtil.CurrentTime("MM-dd"));
                    td.setZjr(zjr);
                    for (int j = 0; j < 8; j++)  
                    {  
                        HSSFCell cell = row.getCell(j);  
                        if(j == 0){
                            if(cell != null){
                                String cellStr = cell.toString();  
                                if(isDigit(cellStr) != ""){
                                    String NDID = (String) tdglService.queryForObject("tdExcel.queryNd",isDigit(cellStr));
                                    td.setNd(NDID);
                                }else{
                                    sfcg = "0";
                                    bcghs = String.valueOf(i+1);
                                    bcgyy = "请填写正确的年度";
                                    break other;
                                }
                            }else{
                                sfcg = "0";
                                bcghs = String.valueOf(i+1);
                                bcgyy = "年度不可为空";
                                break other;
                            }
                        }else if(j == 1){
                            if(cell != null){
                                String cellStr = cell.toString();
                                String YXID = (String) tdglService.queryForObject("tdExcel.queryIdByYx",cellStr);
                                String YXDM = (String) tdglService.queryForObject("tdExcel.queryDmByYx",cellStr);
                                if(YXDM==null || YXDM==""){
                                    sfcg = "0";
                                    bcghs = String.valueOf(i+1);
                                    bcgyy = "院系不存在";
                                    break other;
                                }
                                td.setSsyx(YXID);
                                //设置团队编码
                                String currentYear = DateUtil.CurrentTime("yyyy");//当前年份    
                                String  tdbm="";
                                String str=null;
                                str="T"+currentYear+YXDM;
                                String maxID = (String) tdglService.queryForObject("tdgl.queryMaxID", str);//当前团队最大ID
                                String str_q=   maxID.substring(1);
                                int  MAXID=Integer.parseInt(str_q); //将返回的字符串去掉T
                                tdbm="T"+(MAXID+1);
                                td.setTdbm(tdbm);
                            }else{
                                sfcg = "0";
                                bcghs = String.valueOf(i+1);
                                bcgyy = "院系不存在";
                                break other;
                            }
                        }else if(j == 3){
                            if(cell != null){
                                String cellStr = cell.toString();
                                String[] zdjs = cellStr.split(",");
                                for(int in = 0; in < zdjs.length; in ++){
                                    zdjs[in] = zdjs[in].substring(1,zdjs[in].length()-1);
                                    //zdjs[in]=isDigit(zdjs[in]);
                                    zdjs[in] = (String) tdglService.queryForObject("tdExcel.queryIdByZgh",zdjs[in]);
                                    if("".equals(zdjs[in]) || zdjs[in] == null){
                                        sfcg = "0";
                                        bcghs = String.valueOf(i+1);
                                        bcgyy = "教师编号有误";
                                        break other;
                                    }
                                }
                                td.setZdjs(zdjs);
                            }else{
                                sfcg = "0";
                                bcghs = String.valueOf(i+1);
                                bcgyy = "指导教师不可为空";
                                break other;
                            }
                        }else if(j == 5){
                            if(cell != null){
                                String cellStr = cell.toString();
                                cellStr = cellStr.substring(1,cellStr.length()-1);
                                // BigDecimal bg=new BigDecimal(cellStr);
                                 //cellStr=bg.toPlainString();
                                cellStr = (String) tdglService.queryForObject("tdExcel.queryIdByXh",cellStr);
                                
                                if("".equals(cellStr) || cellStr == null){
                                    sfcg = "0";
                                    bcghs = String.valueOf(i+1);
                                    bcgyy = "团队组长学号有误";
                                    break other;
                                }
                                else{
                                    String Tdid = (String) tdglService.queryForObject("tdExcel.queryryid",cellStr);
                                    String XM = (String) tdglService.queryForObject("tdExcel.queryXmBy",cellStr);
                                    if( Tdid!= null){
                                        sfcg = "0";
                                        bcghs = String.valueOf(i+1);
                                        bcgyy = "团队组长"+XM+"已存在";
                                        break other;
                                    }
                                }
                                td.setTdzz(cellStr);
                            }else{
                                sfcg = "0";
                                bcghs = String.valueOf(i+1);
                                bcgyy = "团队组长不可为空";
                                break other;
                            }
                        }else if(j == 7){
                            if(cell != null){
                                String cellStr = cell.toString();  
                                String[] tdzy = cellStr.split(",");
                                for(int k = 0; k < tdzy.length; k++){
                                    tdzy[k] = tdzy[k].substring(1,tdzy[k].length()-1);
                                    tdzy[k] = (String) tdglService.queryForObject("tdExcel.queryIdByXh",tdzy[k]);
                                    if("".equals(tdzy[k]) || tdzy[k] == null){
                                        sfcg = "0";
                                        bcghs = String.valueOf(i+1);
                                        bcgyy = "团队组员学号有误";
                                        break other;
                                    }
                                    rs++;
                                }
                                td.setTdzy(tdzy);
                            }else{
                                sfcg = "0";
                                bcghs = String.valueOf(i+1);
                                bcgyy = "团队组员不可为空";
                                break other;
                            }
                        }
                    }  
                        td.setRs(String.valueOf(rs));
                        tdxxList.add(td);
                }  
            }  
        }catch(IOException e){
            e.printStackTrace();
        }
        if("2".equals(sfcg)){
            
            
            for(DrTdb tdxx : tdxxList){
                tdglService.insert("tdExcel.addTdxx", tdxx);
                tdglService.insert("tdExcel.addTdzz", tdxx);
                tdglService.insert("tdExcel.addTdzy", tdxx);
                tdglService.insert("tdExcel.addZdjs", tdxx);
            }
            
        }
        map.put("sfcg",sfcg );
        map.put("bcghs",bcghs );
        map.put("bcgyy",bcgyy );
        return map;
    }

看不懂留言

已赞过 已踩过<

评论收起

北京勤哲软件技术

广告2024-12-21

勤哲Excel服务器2024学习和下载。用Excel自动生成用excel做员工档案及手机app.软博会金奖产品，适合于各行各业的管理人员使用。

www.qinzhe.com

人世俯仰
推荐于2016-06-18 · TA获得超过1325个赞

知道小有建树答主

回答量：511

采纳率：66%

帮助的人：181万

我也去答题访问个人页

关注

展开全部

这是我之前写的用反射的将数据导入到excel中的类，具体实现，代码里有注释。不知道楼主持久层用的什么东东？如果是ibaits，建议使用ibatis的rowhandler，导出部分的实现，和下面这个类也很类似，楼主自己改改吧，这样性能会高一些，尤其是在你处理的要写入文件的数据，比较多的情况下。
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

import cn.emag.framework.Util.LogName;

/**
* 导出成excel文件工具类
*/
public class Export2ExcelUtil
{
   
    private static Logger log = Logger.getLogger(LogName.ERROR_LOG);
   
    private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
    /**
     * 生成excel文件存入服务器
     * @param importdata 待导入excle文件的内容
     * @param header  excel”表头“部分内容
     * @param attr 与”表头“顺序对应的importdata中的成员变量名，首字母大写
     * @param fileName 导入到目标文件中，完整路径
     */
    public static void export2exc(List<Object> importdata,String[] header,String[] attr, String fileName)
    {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
//        创建头文件
        writeHeader(header,sheet,wb);
//        创建文件内容
        int i=1;
        HSSFRow row;
        for(Object object:importdata)
        {
            row = sheet.createRow(i++);
            writeBody(object, attr, row);
        }
//        写入文件
        OutputStream os = null;
        try
        {
            os = new FileOutputStream(fileName);
            wb.write(os);
        }
        catch (Exception e)
        {
            log.error("写入文件失败",e);
        }
        finally
        {
            try
            {
                os.close();
            }
            catch (IOException e)
            {
                log.error("写入文件失败",e);
            }
        }
    }

/**
     * 创建文件头
     * @param header 头内容
     * @param sheet
     */
    public static void writeHeader(String[] header,HSSFSheet sheet,HSSFWorkbook wb)
    {

HSSFRow row = sheet.createRow(0);
        HSSFCellStyle  cellstype = wb.createCellStyle();
        cellstype = wb.createCellStyle();
        cellstype.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellstype.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellstype.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellstype.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellstype.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellstype.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellstype.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell;
        HSSFRichTextString str;
        int n = 0;
        for (String head : header)
        {
            sheet.setColumnWidth(n, 4000);
            cell = row.createCell(n++);
            cell.setCellStyle(cellstype);
            str = new HSSFRichTextString(head);
            cell.setCellValue(str);
        }
    }

/**
     * 写行内容
     * @param object
     * @param atrr
     * @param row
     * @throws Exception
     */
    public static  void writeBody(Object object,String[] attr,HSSFRow row)
    {
        int n = 0;
        HSSFCell cell;
        Method method;
        HSSFRichTextString str;
        String content=null;
        for(String atr : attr)
        {
            cell = row.createCell(n++);
             try
            {
                method = object.getClass().getMethod("get"+atr);
                Object o = method.invoke(object);
                if(null!=o)
                {
                    if(o instanceof Date)
                    {
                        content=format.format(o);
                    }else
                    {
                        content = String.valueOf( method.invoke(object)); 
                    }
                   
                }else
                 {
                     content=null;
                 }
            }
            catch (SecurityException e)
            {
                log.error("excel写入单元格内容失败",e);
            }
            catch (IllegalArgumentException e)
            {
                log.error("excel写入单元格内容失败",e);
            }
            catch (NoSuchMethodException e)
            {
                log.error("excel写入单元格内容失败",e);
            }
            catch (IllegalAccessException e)
            {
                log.error("excel写入单元格内容失败",e);
            }
            catch (InvocationTargetException e)
            {
                log.error("excel写入单元格内容失败",e);
            }
             str = new HSSFRichTextString(content);
             cell.setCellValue(str);
        }
       
    }

}


本回答被提问者和网友采纳






已赞过已踩过<

你对这个回答的评价是？
评论收起