Commons-POI - Java Excel operations

2010-04-19  来源:本站原创  分类:Java  人气:490 

★ , POI, many components are not able to spend, according to need to select their own needs, and I used here is the SS (HSSF + XSSF), and were for xls and xlsx, generally should be ok to use HSSF to, or it more people with office2003

Commons-POI - Java Excel operations

★ , most of the path with the package under the org.apache.poi.ss.usermodel

★ , poi-examples-3.6-20091214.jar file which has examples of this can be a reference

Commons-POI - Java Excel operations

★ , if not handled documents may also be xlsx xls, how do?
Method 1: Before judging, and then were treated with different methods to deal with Method 2: Use the interface operation, the output document, according to the type preservation

//save workbook
    String file = "picture.xls";
    if(wb instanceof XSSFWorkbook) file += "x";
    FileOutputStream fileOut = new FileOutputStream(file);
    wb.write(fileOut);
    fileOut.close();

★ , reference URL:
http://poi.apache.org/
http://poi.apache.org/spreadsheet/quick-guide.html
http://developers.sun.com.cn/blog/functionalca/entry/java read and write excel Introduction

★ , write a demo, ExcelUtil.java

package core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;

import core.dao.DaoImpl;

/**
 * Description: <br>
 * 2010-4-16
 *
 * @author huxiao [email protected]
 */
public class ExcelUtil {

        private static final String filepath = "F:/job/workspace_huxiao_new/_testpoi/src/test/file/workbook.xls";

        /**
         * description:  Put a List generated as  Excel<br>
         *
         * @param linkedList
         * @param file
         *            2010-4-16
         * @author huxiao [email protected]
         */
        public static void list2excel(List<Map<String, String>> list, List<String> keyList, File file) {
                List<LinkedHashMap<String, String>> linkedList = new ArrayList<LinkedHashMap<String, String>>();
                for (Map<String, String> map : list) {
                        LinkedHashMap<String, String> tmpMap = new LinkedHashMap<String, String>();
                        for (String key : keyList) {
                                tmpMap.put(key, map.get(key));
                        }
                        linkedList.add(tmpMap);
                }

                if (!file.isDirectory()) {
                        file.getParentFile().mkdirs();
                }
                Workbook wb = new HSSFWorkbook();
                Sheet sheet = wb.createSheet("Sheet1");

                for (int i = 0; i < linkedList.size(); i++) {
                        Row row = sheet.createRow(i);
                        Map<String, String> map = linkedList.get(i);
                        int column = 1;
                        for (String key : map.keySet()) {
                                Cell cell = row.createCell(column++);
                                cell.setCellValue(map.get(key));
                        }
                }
                try {
                        FileOutputStream fileOut = new FileOutputStream(file);
                        wb.write(fileOut);
                        fileOut.close();
                } catch (Exception e) {
                        System.out.println(" An error occurred generating the file  ");
                        e.printStackTrace();
                }
        }

        /**
         * description:  Read an Excel file and returns  List<br>
         *
         * @param keyList
         * @param file
         * @return 2010-4-19
         * @author huxiao [email protected]
         */
        public static List<Map<String, String>> excel2List(List<String> keyList, File file) {
                if (!file.isFile()) {
                        throw new RuntimeException(file + "  Does not exist  ");
                }
                POIFSFileSystem fs = null;
                HSSFWorkbook wb = null;
                HSSFSheet sheet = null;
                try {
                        fs = new POIFSFileSystem(new FileInputStream(file));
                        wb = new HSSFWorkbook(fs);
                        sheet = wb.getSheetAt(0);
                } catch (IOException e) {
                        System.out.println(file + " Read errors  ");
                        e.printStackTrace();
                }
                List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
                for (Row row : sheet) {
                        Map<String, String> tmpMap = new HashMap<String, String>();
                        for (Cell cell : row) {
                                int columnIndex = cell.getColumnIndex();
                                tmpMap.put(keyList.get(columnIndex - 1), cell.getStringCellValue());
                        }
                        resultList.add(tmpMap);
                }
                return resultList;
        }

        /**
         * description:  Test generation  Excel<br>
         * 2010-4-19
         *
         * @author huxiao [email protected]
         */
        @Test
        public void testList2Excel() {
                List<Map<String, String>> list = new DaoImpl().queryForList("select * from user");

                //  Because the hashmap is out of order  , So here the order of the custom key  , According to this keyList put  hashMap Go into LinkedHashMap recycling
                //  When demand for fixed, you can directly write to the configuration file  , Don't have a handwriting
                List<String> keyList = new ArrayList<String>();
                keyList.add("ID");
                keyList.add("USERNAME");
                keyList.add("PASSWORD");

                list2excel(list, keyList, new File(filepath));
        }

        /**
         * description:  Test  excel2list<br>
         *
         * @param args
         *            2010-4-19
         * @author huxiao [email protected]
         */
        @Test
        public void testExcel2List() {
                //  And when you do not have to order it, but you want to develop  key What, otherwise how storage to  map Inside?  ?
                //  Don't worry, the actual operation of the time this thing is available from the database or configuration file  , You won't have to re-enter it every time a handwritten
                List<String> keyList = new ArrayList<String>();
                keyList.add("ID");
                keyList.add("USERNAME");
                keyList.add("PASSWORD");

                List<Map<String, String>> list = excel2List(keyList, new File(filepath));
                System.out.println(list);
        }

}

相关文章
  • Commons-POI - Java Excel operations 2010-04-19

    ★ , POI, many components are not able to spend, according to need to select their own needs, and I used here is the SS (HSSF + XSSF), and were for xls and xlsx, generally should be ok to use HSSF to, or it more people with office2003 ★ , most of the

  • example of java Excel operations 2009-07-08

    1. package excel.jxl; 2. 3. import java.io.File; 4. import java.io.FileOutputStream; 5. import java.io.OutputStream; 6. import java.util.ArrayList; 7. import java.util.Date; 8. 9. import jxl.Cell; 10. import jxl.CellType; 11. import jxl.Sheet; 12. im

  • Apache POI Excel operations 2010-09-06

    1. Apache POI Description (http://poi.apache.org/) Read and write Java programs using Microsoft Office, which provides the following types: HSSF-provided read and write Microsoft Excel XLS format file functions. XSSF-provided read and write Microsoft

  • java使用poi读取excel内容方法实例 2014-09-21

    本文介绍java使用poi读取excel内容的实例,大家参考使用吧 import java.io.BufferedInputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.nio.channels.FileChannel; import java.text.DecimalForm

  • java poi读取excel操作示例(2个代码) 2015-03-31

    这篇文章主要介绍了使用POI读取EXCEL文件的方法,代码大家可以参考使用 项目中要求读取excel文件内容,并将其转化为xml格式.常见读取excel文档一般使用POI和JExcelAPI这两个工具.这里我们介绍使用POI实现读取excel文档. /* * 使用POI读取EXCEL文件 */ import java.io.File; import java.io.FileInputStream; import java.util.ArrayList; import org.apache.poi

  • java使用poi读取ppt文件和poi读取excel.word示例 2015-04-04

    这篇文章主要介绍了java使用poi读取ppt文件和poi读取excel.word示例,需要的朋友可以参考下 Apache的POI项目可以用来处理MS Office文档,codeplex上还有一个它的.net版本.POI项目可创建和维护操作各种基于OOXML和OLE2文件格式的Java API.大多数MS Office都是OLE2格式的.POI通HSMF子项目来支持Outlook,通过HDGF子项目来支持Visio,通过HPBF子项目来支持Publisher. 使用POI抽取Word简单示例:

  • java POI实现excel实现表格导出 2014-04-15

    1.首先下载到 poi-3.6-20091214.jar的包 2.新建一个java类:Student.java: import java.util.Date; public class Student { private int id; private String name; private int age; private Date birth; public Student() { } public Student(int id, String name, int age, Date bi

  • Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 2014-04-17

    在web开发中,有一个经典的功能,就是数据的导入导出.特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作.而数据导出的格式一般是EXCEL或者PDF,我这里就用两篇文章分别给大家介绍下.(注意,我们这里说的数据导出可不是数据库中的数据导出!么误会啦^_^) 呵呵,首先我们来导出EXCEL格式的文件吧.现在主流的操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI.这里我们用Apache POI!我们先去Apach

  • java excel table operation 2010-07-05

    About Java Excel JAVA EXCEL API is an open source project through which Java developers can read Excel document, create a new Excel file, update Existing Excel file. Non-Windows operating system using the API can also be pure Java application to hand

  • POI Export EXCEL Classical 2010-08-22

    web development, there is a classic feature is the import and export data. In particular, data export, production management or financial systems using the very common, because these systems often do the work of a number of statements printed. The da

  • 使用Apache POI读取Excel文件 2013-05-13

    网上关于介绍Apache POI操作Excel的文章已经很多了,但都讲得比较复杂.poi的API 与实际使用中的Excel很类似,可以说是POI把Excel中的workbook.sheet.cell等对象化了,在实际使用中极易理解.但由于Apache POI在存在已有不短时间,至少在excel2007之前就已经出现,造成同样一套Api并不能同时读取(写入)xls和xlsx两种类型的Excel文件.但poi对excel有一个很好的抽象(ss包下的Workbook.Sheet.Cell等类),可以一

  • Java file operations enhancement tools 2010-03-29

    Java file operations enhancement tools -------------------------------------------------- ------------------------------ 2008-12-15 18:30:43 Tags: File IO commons [pushed to the technical ring] Copyright Statement: original works, and if reproduced,

  • java excel operation 2010-04-18

    Using the Windows operating system, a friend of the Excel (spreadsheet) will definitely be familiar, but to use the Java language to manipulate the Excel file is not an easy task. Increasingly popular in Web applications today, to manipulate Excel fi

  • Use POI with Excel documents 2011-08-10

    Use POI with Excel documents JAVA DOC: http://jakarta.apache.org/poi/apidocs/index.html POI is a good Java open source project dedicated to parsing and generation with the Microsoft OLE 2-related file types, such as Word, Excel and so on. POI package

  • POI导出EXCEL经典实现 2014-07-29

    在web开发中,有一个经典的功能,就是数据的导入导出.特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作.而数据导出的格式一般是EXCEL或者PDF,我这里就用两篇文章分别给大家介绍下.(注意,我们这里说的数据导出可不是数据库中的数据导出!么误会啦^_^) 呵呵,首先我们来导出EXCEL格式的文件吧.现在主流的操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI.这里我们用Apache POI!我们先去Apach

  • SpringMVC+Hibernate +MySql+ EasyUI实现POI导出Excel(二) 2014-11-21

    SpringMVC+Hibernate +MySql+ EasyUI实现CRUD(一) 大概的截图.很简单的小功能 注:使用的是MyEclipse 10.0 javaee 6.0 tomcat 6.0 导出指定列名.使用VO接受参数. SpringMVC+Hibernate +MySql+ EasyUI实现POI导出Excel(二) 和 批量删除数据 小功能的实现 1.datagrid新加的导出按钮代码 /*导出excel 按钮*/ toolbar:[{ id:'btnsave', text:'

  • Struts2+ExtJS+poi导出excel 2015-04-22

    首先生成Excel import java.io.IOException; import java.io.OutputStream; 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.

  • Java file operations method 2010-03-29

    Transfer from the following code: http://lionelxx777.javaeye.com/blog/358128 Java file operations to the text of the two methods. 1. The usual practice is to use FileInputStream and FileOutputStream to carry out operations, the above two categories r

  • Full excavation Java Excel API to use 2010-04-22

    Friends use Windows operating system Excel (spreadsheet) will definitely be familiar, but to use the Java language to manipulate the Excel file is not an easy task. Increasingly popular in Web applications today, through the Web to manipulate Excel f

  • EXCEL file using JAVA operation (JAVA EXCEL API) 2010-04-22

    JAVA EXCEL API Introduction Java Excel is an open source project through which Java developers can read Excel document, create a new Excel file, update an existing Excel file. Non-Windows operating system using the API can also be pure Java applicati