java 读写excel

package com.edu.zzu.Servlet;

import com.edu.zzu.DataBaseConnection.DataBaseConnection;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;

/**
 * Created by Administrator on 2016/9/7.
 */
public class ReadExcel {
    private static POIFSFileSystem fs;
    private static  HSSFWorkbook wb;
    private static HSSFSheet sheet;
    private static Connection conn;
    private static HSSFRow row;

    public void Read(InputStream in){
        int rowNum = sheet.getLastRowNum();
        int grade;
        String num, department, major, gender, name, id;
        String sql1, sql2, sql3, sql4, sql5;
        int id1, id2;
        sql1 = "select id from department where name=?";
        sql2 = "insert into department(id, name) values (NULL , ?)";
        sql3 = "select id from major where name=?";
        sql4 = "insert into major(id, name, department) values(NULL , ?, ?)";
        sql5 = "insert into student(num, name, gender, id, grade, major) values (?, ?, ?, ?, ?, ?)";
        PreparedStatement prep = null;
        ResultSet rs;
        System.out.println("Total: "+rowNum);
        for (int i = 40100; i <= rowNum; i++){
            row = sheet.getRow(i);
            grade = Integer.parseInt(getCellValue(row.getCell(0)));
            num = getCellValue(row.getCell(1));
            name =getCellValue( row.getCell(2));
            id = getCellValue(row.getCell(3));
            gender = getCellValue(row.getCell(5));
            department = getCellValue(row.getCell(6));
            major = getCellValue(row.getCell(7));
            try {
                prep = conn.prepareStatement(sql1);//insert department
                prep.setString(1, department);
                rs = prep.executeQuery();
                if (!rs.next()) {
                    prep = conn.prepareStatement(sql2);
                    prep.setString(1, department);
                    prep.executeUpdate();
                    prep = conn.prepareStatement(sql1);
                    prep.setString(1, department);
                    rs = prep.executeQuery();
                    rs.next();
                    id1 = rs.getInt("id");
                }else{
                    id1 = rs.getInt("id");
                }
                prep = conn.prepareStatement(sql3); //insert major
                prep.setString(1, major);
                rs = prep.executeQuery();
                if (!rs.next()){
                    prep = conn.prepareStatement(sql4);
                    prep.setString(1, major);
                    prep.setInt(2, id1);
                    prep.executeUpdate();
                    prep = conn.prepareStatement(sql3);
                    prep.setString(1, major);
                    rs = prep.executeQuery();
                    rs.next();
                    id2 = rs.getInt("id");
                }else{
                    id2 = rs.getInt("id");
                }
                prep = conn.prepareStatement(sql5); //insert student
                prep.setString(1, num);
                prep.setString(2, name);
                prep.setString(3, gender);
                prep.setString(4, id);
                prep.setInt(5, grade);
                prep.setInt(6, id2);
                prep.executeUpdate();
            } catch (SQLException e) {
                System.out.println(prep);
                e.printStackTrace();
                System.out.println(i+":  Error!");
//                break;
            }
            if (i%100==0){
                System.out.println(i);
            }
        }
        System.out.println("Success");
    }

    public static  void ReadTeacher(){
        int rowNum = sheet.getLastRowNum();
        Integer  id1, grade;
        String department, name, gender;
        String sql1 = "select id from department where name=?";
        String sql2 = "insert into teacher (name, gender, grade, department) values (?, ?, ?, ?)";
        PreparedStatement prep = null;
        ResultSet rs = null;
        for (int i = 1; i <= rowNum; i++){
            row = sheet.getRow(i);
            department = getCellValue(row.getCell(0));
            name = getCellValue(row.getCell(1));
            gender = getCellValue(row.getCell(2));
            if (getCellValue(row.getCell(3)).equals("")){
                grade = 0;
            }else{
                grade = Integer.parseInt(getCellValue(row.getCell(3)));
            }
            try {
                prep = conn.prepareStatement(sql1);
                prep.setString(1, department);
                rs = prep.executeQuery();
                if (rs.next()){
                    id1 = rs.getInt("id");
                }else{
                    System.out.println("Error at line "+i);
                    break;
                }
                prep = conn.prepareStatement(sql2);
                prep.setString(1, name);
                prep.setString(2, gender);
                prep.setInt(3, grade);
                prep.setInt(4, id1);
                prep.executeUpdate();
            } catch (SQLException e) {
                System.out.println(prep);
                System.out.println("Line :"+i);
                e.printStackTrace();
                break;
            }
        }
        System.out.println("Success!");
    }
    private static String getCellValue(HSSFCell cell){
        String value="";
        if (cell != null){
            switch (cell.getCellType()){
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = Integer.toString((int)cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
            }
        }
        return value;
    }
    public static void main(String[] args) {
        InputStream in = null;
        try {
            in = new FileInputStream("C:\\Users\\Administrator\\Desktop\\teacher.xls");
            conn = DataBaseConnection.getConnection();
            if (conn == null){
                return;
            }
            try {
                fs = new POIFSFileSystem(in);
                wb = new HSSFWorkbook(fs);
                sheet = wb.getSheet("Sheet1");
            } catch (IOException e) {
                e.printStackTrace();
            }
            System.out.println("Success Open File!");
            ReadTeacher();
//            new ReadExcel().Read(in);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }
}

package com.zzu.Servlet;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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 com.zzu.DAO.IAdminDao;
import com.zzu.DAO.IBookDAO;
import com.zzu.DAO.IBook_author_relaDAO;
import com.zzu.DAO.IBook_classDAO;
import com.zzu.DAO.IDepartmentDAO;
import com.zzu.DAO.IMajorDAO;
import com.zzu.DAO.IPermissionDAO;
import com.zzu.DAO.IReaderDAO;
import com.zzu.DAO.IRecordDAO;
import com.zzu.DAO.ISchoolDAO;
import com.zzu.Factory.AdminDAOFactory;
import com.zzu.Factory.BookDAOFactory;
import com.zzu.Factory.Book_author_relaDAOFactory;
import com.zzu.Factory.Book_classDAOFactory;
import com.zzu.Factory.DepartmentDAOFactory;
import com.zzu.Factory.MajorDAOFactory;
import com.zzu.Factory.PermissionDAOFactory;
import com.zzu.Factory.ReaderDAOFactory;
import com.zzu.Factory.RecordDAOImplFactory;
import com.zzu.Factory.SchoolDAOFactory;
import com.zzu.Model.Admin;
import com.zzu.Model.Author;
import com.zzu.Model.Book;
import com.zzu.Model.Book_class;
import com.zzu.Model.Department;
import com.zzu.Model.Major;
import com.zzu.Model.Permission;
import com.zzu.Model.Reader;
import com.zzu.Model.Record;
import com.zzu.Model.School;

@WebServlet(name = "ExportExcelServelt", urlPatterns = "/ExportExcelServelt")
public class ExportExcel extends HttpServlet {

    /**
     * Constructor of the object.
     */
    public ExportExcel() {
        super();
    }

    /**
     * Destruction of the servlet. <br>
     */
    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        // Put your code here 
    }

    /**
     * The doGet method of the servlet. <br>
     * 
     * This method is called when a form has its tag value method equals to get.
     * 
     * @param request
     *            the request send by the client to the server
     * @param response
     *            the response send by the server to the client
     * @throws ServletException
     *             if an error occurred
     * @throws IOException
     *             if an error occurred
     */
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        doPost(request, response);
    }

    /**
     * The doPost method of the servlet. <br>
     * 
     * This method is called when a form has its tag value method equals to
     * post.
     * 
     * @param request
     *            the request send by the client to the server
     * @param response
     *            the response send by the server to the client
     * @throws ServletException
     *             if an error occurred
     * @throws IOException
     *             if an error occurred
     */
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String type = request.getParameter("type");
        OutputStream out = response.getOutputStream();

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        // sheet.setDefaultColumnWidth((short)15);

        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);

        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style2.setFont(font2);

        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
        if (type.equals("reader")) {
            String[] headers = { "读者号", "姓名", "学校", "院系", "专业", "性别", "联系方式",
                    "电子邮箱", "注册日期", "欠款", "身份", "在借数量", "是否删除" };
            for (int i = 0; i < headers.length; i++) {
                createCell(row, i, style, headers[i]);
            }
            IReaderDAO readerDAO = ReaderDAOFactory.createReaderDAOImpl();
            List<Reader> readers = readerDAO.getAllReaders(1);
            Reader reader;
            ISchoolDAO schoolDAO = SchoolDAOFactory.createSchoolDAOImpl();
            IDepartmentDAO departmentDAO = DepartmentDAOFactory
                    .createDepartmentDAOImpl();
            IMajorDAO majorDAO = MajorDAOFactory.createMajorDAOImpl();
            School school;
            Department department;
            Major major;
            for (int i = 0; i < readers.size(); i++) {
                row = sheet.createRow(1 + i);
                reader = readers.get(i);
                major = majorDAO.getMajorByMajor_id(reader.getMajor_id());
                department = departmentDAO.getDepartmentByDepartment_id(major
                        .getDepartment_id());
                school = schoolDAO.getSchoolBySchool_id(department
                        .getSchool_id());
                createCell(row, 0, style2, reader.getReader_id());
                createCell(row, 1, style2, reader.getReader_name());
                createCell(row, 2, style2, school.getSchool_name());
                createCell(row, 3, style2, department.getDepartment_name());
                createCell(row, 4, style2, major.getMajor_name());
                createCell(row, 5, style2, reader.getGender() == 0 ? "女" : "男");
                createCell(row, 6, style2, reader.getContact());
                createCell(row, 7, style2, reader.getEmail());
                createCell(row, 8, style2, reader.getReg_date().toString());
                createCell(row, 9, style2, Float.toString(reader.getFine()));
                createCell(row, 10, style2,
                        Integer.toString(reader.getStatue()));
                createCell(row, 11, style2,
                        Integer.toString(reader.getBorrow_num()));
                createCell(row, 12, style2,
                        reader.getDelete_flag() == 0 ? "未删除" : "已删除");
                // cell = row.createCell(6);
                // cell.setCellStyle(style2);
                // if (reader.getPhoto() != null){
                // row.setHeightInPoints(60);
                // sheet.setColumnWidth(6, (short)(35.7*80));
                // byte[] value = new byte[reader.getPhoto().available()];
                // reader.getPhoto().read(value);
                // HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023,
                // 255, (short)6, 6, (short)6, 6);
                // // anchor.setAnchorType();
                // patriarch.createPicture(anchor, workbook.addPicture(value,
                // HSSFWorkbook.PICTURE_TYPE_JPEG));
                // }
            }
            readerDAO.close();
            schoolDAO.close();
            departmentDAO.close();
            majorDAO.close();
        } else if (type.equals("book")) {
            String[] headers = { "索书号", "书名", "作者", "译者", "分类", "版本", "位置",
                    "价钱", "出版日期", "出版社", "入库日期", "摘要", "是否借阅", "ISBN", "是否删除" };
            for (int i = 0; i < headers.length; i++) {
                createCell(row, i, style, headers[i]);
            }
            IBookDAO bookDAO = BookDAOFactory.createBookDAOImpl();
            IBook_classDAO book_classDAO = Book_classDAOFactory
                    .createBook_classDAOImpl();
            IBook_author_relaDAO book_author_relaDAO = Book_author_relaDAOFactory
                    .createBook_author_relaDAOImpl();
            List<Book> books = bookDAO.getAllBooks(1);
            Book book;
            Book_class book_class;
            List<Author> authors, translators;
            for (int i = 0; i < books.size(); i++) {
                book = books.get(i);
                row = sheet.createRow(1 + i);
                book_class = book_classDAO.getBook_classByClassifyno(book
                        .getClassifyno());
                authors = book_author_relaDAO.getAuthorsByISBN(book.getIsbn(),
                        0);
                translators = book_author_relaDAO.getAuthorsByISBN(
                        book.getIsbn(), 1);
                createCell(row, 0, style2, book.getCall_no());
                createCell(row, 1, style2, book.getBook_name());
                StringBuffer author = new StringBuffer("");
                for (Author a : authors) {
                    author.append(a.getAuthor_name() + "   ");
                }
                createCell(row, 2, style2, author.toString());
                StringBuffer translator = new StringBuffer("");
                for (Author a : translators) {
                    translator.append(a.getAuthor_name() + "   ");
                }
                createCell(row, 3, style2, translator.toString());
                createCell(row, 4, style2, book_class.getClassifyname());
                createCell(row, 5, style2, Integer.toString(book.getEdition()));
                createCell(row, 6, style2, book.getLocation());
                createCell(row, 7, style2, Float.toString(book.getPrice()));
                createCell(row, 8, style2, book.getPublish().toString());
                createCell(row, 9, style2, book.getPress());
                createCell(row, 10, style2, book.getImport_date().toString());
                createCell(row, 11, style2, book.getAbstr());
                createCell(row, 12, style2, book.getBorrowed() == 0 ? "未借出"
                        : "已借出");
                createCell(row, 13, style2, book.getIsbn());
                createCell(row, 14, style2, book.getDelete_flag() == 0 ? "未删除"
                        : "已删除");
            }
            book_author_relaDAO.close();
            book_classDAO.close();
            bookDAO.close();
        } else if (type.equals("admin")) {
            String[] headers = { "工号", "姓名", "性别", "联系方式", "注册日期", "类型", "是否删除" };
            for (int i = 0; i < headers.length; i++) {
                createCell(row, i, style, headers[i]);
            }
            IAdminDao adminDao = AdminDAOFactory.createAdminDAOImpl();
            List<Admin> admins = adminDao.getAllAdmins();
            Admin admin;
            for (int i = 0; i < admins.size(); i++) {
                admin = admins.get(i);
                row = sheet.createRow(i + 1);
                createCell(row, 0, style2, admin.getEmpno());
                createCell(row, 1, style2, admin.getAdmin_name());
                createCell(row, 2, style2, admin.getGender() == 0 ? "女" : "男");
                createCell(row, 3, style2, admin.getContact());
                createCell(row, 4, style2, admin.getReg_date().toString());
                createCell(row, 5, style2, admin.getType() == 0 ? "借阅管理员"
                        : "超级管理员");
                createCell(row, 6, style2, admin.getDelete_flag() == 0 ? "未删除"
                        : "已删除");
            }
            adminDao.close();
        } else if (type.equals("permission")) {
            String[] headers = { "身份", "最大借阅数量", "最长借阅时间", "最大续借次数", "最长续借时间",
                    "欠费费率", "最大欠费额度" };
            for (int i = 0; i < headers.length; i++) {
                createCell(row, i, style, headers[i]);
            }
            IPermissionDAO permissionDAO = PermissionDAOFactory
                    .createPermissionDAOImpl();
            List<Permission> permissions = permissionDAO.getAllPermissions();
            Permission permission;
            for (int i = 0; i < permissions.size(); i++) {
                permission = permissions.get(i);
                row = sheet.createRow(i + 1);
                createCell(row, 0, style2,
                        Integer.toString(permission.getStatue()));
                createCell(row, 1, style2,
                        Integer.toString(permission.getCount()));
                createCell(row, 2, style2,
                        Integer.toString(permission.getBorrow()));
                createCell(row, 3, style2,
                        Integer.toString(permission.getRenew()));
                createCell(row, 4, style2,
                        Integer.toString(permission.getRenew_limit()));
                createCell(row, 5, style2,
                        Float.toString(permission.getFine_rate()));
                createCell(row, 6, style2,
                        Integer.toString(permission.getFine_limit()));
            }
            permissionDAO.close();
        } else if (type.equals("record")) {
            String[] headers = { "编号", "操作人", "操作时间", "操作类型", "备注" };
            for (int i = 0; i < headers.length; i++) {
                createCell(row, i, style, headers[i]);
            }
            IRecordDAO recordDAO = RecordDAOImplFactory.createRecordDAOImpl();
            List<Record> records = recordDAO.getAllRecords();
            Record record;
            for (int i = 0; i < records.size(); i++) {
                record = records.get(i);
                row = sheet.createRow(i + 1);
                createCell(row, 0, style2, record.getRecord_id());
                createCell(row, 1, style2, record.getEmpno());
                createCell(row, 2, style2, record.getTime().toString());
                if (record.getOp_type() == 1) {
                    createCell(row, 3, style2, "增加");
                } else if (record.getOp_type() == 2) {
                    createCell(row, 3, style2, "删除");
                } else {
                    createCell(row, 3, style2, "修改");
                }
                createCell(row, 4, style2, record.getOp_content());
            }
        }
        response.addHeader("Content-Disposition", "attachment;filename="
                + (new SimpleDateFormat("yyyyMMddHHmmssSS")).format(new Date())
                + ".xls");
        response.setContentType("application/vnd.ms-excel;charset=gb2312");
        workbook.write(out);
        workbook.close();
        out.flush();
        out.close();

    }

    private void createCell(HSSFRow row, int index, HSSFCellStyle style,
            String content) {
        HSSFCell cell = row.createCell(index);
        cell.setCellStyle(style);
        cell.setCellValue(content);
    }

    /**
     * Initialization of the servlet. <br>
     * 
     * @throws ServletException
     *             if an error occurs
     */
    public void init() throws ServletException {
        // Put your code here
    }

}