JAVA读取excel

qq_30148167

于 2018-07-27 14:08:40 发布

阅读量178

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_30148167/article/details/81236161

今天写安卓color.xml文件时想多放颜色都放到里面，以后想用什么就不用百度了。于是就搜了一下，都是表格形式的。

如果一个一个复制还不如每次都百度了，于是我在网上找了Java读excel的代码，把所有的颜色和二进制值都用控制台拼字符串打出来了。

这个程序的jar包，代码和 color.xml

1.commons-collections4-4.1.jar

2.poi-3.17-beta1.jar

3.poi-ooxml-3.17-beta1.jar

4.poi-ooxml-schemas-3.17-beta1.jar

5.xmlbeans-2.6.0.jar

在http://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans/3.0.0可以搜到



import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Testpoi {

    public static void main(String[] args) {
        Workbook wb =null;
        Sheet sheet = null;
        Row row = null;
        List<Map<String,String>> list = null;
        String cellData = null;
        String filePath = "E:\\1.xlsx";
        String columns[] = {"name","age","score"};
        wb = readExcel(filePath);
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<Map<String,String>>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 1; i<rownum; i++) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(row !=null){
                    for (int j=0;j<colnum;j++){
                        cellData = (String) getCellFormatValue(row.getCell(j));
                        map.put(columns[j], cellData);
                    }
                }else{
                    break;
                }
                list.add(map);
            }
        }
        //遍历解析出来的list
        for (Map<String,String> map : list) {
                System.out.println("<color name=\""+map.get("name")+"\">"+map.get("score")+"</color>"+"<!--"+map.get("age")+"-->");
        }

    }
    //读取excel
    public static Workbook readExcel(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }
            
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC:{
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            }
            case Cell.CELL_TYPE_FORMULA:{
                //判断cell是否为日期格式
                if(DateUtil.isCellDateFormatted(cell)){
                    //转换为日期格式YYYY-mm-dd
                    cellValue = cell.getDateCellValue();
                }else{
                    //数字
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            case Cell.CELL_TYPE_STRING:{
                cellValue = cell.getRichStringCellValue().getString();
                break;
            }
            default:
                cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }

}

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <color name="colorPrimary">#3F51B5</color>
    <color name="colorPrimaryDark">#303F9F</color>
    <color name="colorAccent">#FF4081</color>
    <color name="LightPink">#FFB6C1</color><!--浅粉色-->
    <color name="Pink">#FFC0CB</color><!--粉红-->
    <color name="Crimson">#DC143C</color><!--猩红-->
    <color name="LavenderBlush">#FFF0F5</color><!--脸红的淡紫色-->
    <color name="PaleVioletRed">#DB7093</color><!--苍白的紫罗兰红色-->
    <color name="HotPink">#FF69B4</color><!--热情的粉红-->
    <color name="DeepPink">#FF1493</color><!--深粉色-->
    <color name="MediumVioletRed">#C71585</color><!--适中的紫罗兰红色-->
    <color name="Orchid">#DA70D6</color><!--兰花的紫色-->
    <color name="Thistle">#D8BFD8</color><!--蓟-->
    <color name="plum">#DDA0DD</color><!--李子-->
    <color name="Violet">#EE82EE</color><!--紫罗兰-->
    <color name="Magenta">#FF00FF</color><!--洋红-->
    <color name="Fuchsia">#FF00FF</color><!--灯笼海棠（紫红色）-->
    <color name="DarkMagenta">#8B008B</color><!--深洋红色-->
    <color name="Purple">#800080</color><!--紫色-->
    <color name="MediumOrchid">#BA55D3</color><!--适中的兰花紫-->
    <color name="DarkVoilet">#9400D3</color><!--深紫罗兰色-->
    <color name="DarkOrchid">#9932CC</color><!--深兰花紫-->
    <color name="Indigo">#4B0082</color><!--靛青-->
    <color name="BlueViolet">#8A2BE2</color><!--深紫罗兰的蓝色-->
    <color name="MediumPurple">#9370DB</color><!--适中的紫色-->
    <color name="MediumSlateBlue">#7B68EE</color><!--适中的板岩暗蓝灰色-->
    <color name="SlateBlue">#6A5ACD</color><!--板岩暗蓝灰色-->
    <color name="DarkSlateBlue">#483D8B</color><!--深岩暗蓝灰色-->
    <color name="Lavender">#E6E6FA</color><!--薰衣草花的淡紫色-->
    <color name="GhostWhite">#F8F8FF</color><!--幽灵的白色-->
    <color name="Blue">#0000FF</color><!--纯蓝-->
    <color name="MediumBlue">#0000CD</color><!--适中的蓝色-->
    <color name="MidnightBlue">#191970</color><!--午夜的蓝色-->
    <color name="DarkBlue">#00008B</color><!--深蓝色-->
    <color name="Navy">#000080</color><!--海军蓝-->
    <color name="RoyalBlue">#4169E1</color><!--宝蓝-->
    <color name="CornflowerBlue">#6495ED</color><!--矢车菊的蓝色-->
    <color name="LightSteelBlue">#B0C4DE</color><!--淡钢蓝-->
    <color name="LightSlateGray">#778899</color><!--浅石板灰-->
    <color name="SlateGray">#708090</color><!--石板灰-->
    <color name="DoderBlue">#1E90FF</color><!--道奇蓝-->
    <color name="AliceBlue">#F0F8FF</color><!--爱丽丝蓝-->
    <color name="SteelBlue">#4682B4</color><!--钢蓝-->
    <color name="LightSkyBlue">#87CEFA</color><!--淡蓝色-->
    <color name="SkyBlue">#87CEEB</color><!--天蓝色-->
    <color name="DeepSkyBlue">#00BFFF</color><!--深天蓝-->
    <color name="LightBLue">#ADD8E6</color><!--淡蓝-->
    <color name="PowDerBlue">#B0E0E6</color><!--火药蓝-->
    <color name="CadetBlue">#5F9EA0</color><!--军校蓝-->
    <color name="Azure">#F0FFFF</color><!--蔚蓝色-->
    <color name="LightCyan">#E1FFFF</color><!--淡青色-->
    <color name="PaleTurquoise">#AFEEEE</color><!--苍白的绿宝石-->
    <color name="Cyan">#00FFFF</color><!--青色-->
    <color name="Aqua">#00FFFF</color><!--水绿色-->
    <color name="DarkTurquoise">#00CED1</color><!--深绿宝石-->
    <color name="DarkSlateGray">#2F4F4F</color><!--深石板灰-->
    <color name="DarkCyan">#008B8B</color><!--深青色-->
    <color name="Teal">#008080</color><!--水鸭色-->
    <color name="MediumTurquoise">#48D1CC</color><!--适中的绿宝石-->
    <color name="LightSeaGreen">#20B2AA</color><!--浅海洋绿-->
    <color name="Turquoise">#40E0D0</color><!--绿宝石-->
    <color name="BabyGreen">#7FFFAA</color><!--绿玉-->
    <color name="MediumAquamarine">#00FA9A</color><!--适中的碧绿色-->
    <color name="MediumSpringGreen">#F5FFFA</color><!--适中的春天的绿色-->
    <color name="MintCream">#00FF7F</color><!--薄荷奶油-->
    <color name="SpringGreen">#3CB371</color><!--春天的绿色-->
    <color name="SeaGreen">#2E8B57</color><!--海洋绿-->
    <color name="Honeydew">#F0FFF0</color><!--蜂蜜-->
    <color name="LightGreen">#90EE90</color><!--淡绿色-->
    <color name="PaleGreen">#98FB98</color><!--苍白的绿色-->
    <color name="DarkSeaGreen">#8FBC8F</color><!--深海洋绿-->
    <color name="LimeGreen">#32CD32</color><!--酸橙绿-->
    <color name="Lime">#00FF00</color><!--酸橙色-->
    <color name="ForestGreen">#228B22</color><!--森林绿-->
    <color name="Green">#008000</color><!--纯绿-->
    <color name="DarkGreen">#006400</color><!--深绿色-->
    <color name="Chartreuse">#7FFF00</color><!--查特酒绿-->
    <color name="LawnGreen">#7CFC00</color><!--草坪绿-->
    <color name="GreenYellow">#ADFF2F</color><!--绿黄色-->
    <color name="OliveDrab">#556B2F</color><!--橄榄土褐色-->
    <color name="Beige">#6B8E23</color><!--米色（浅褐色）-->
    <color name="LightGoldenrodYellow">#FAFAD2</color><!--浅秋麒麟黄-->
    <color name="Ivory">#FFFFF0</color><!--象牙色-->
    <color name="LightYellow">#FFFFE0</color><!--浅黄色-->
    <color name="Yellow">#FFFF00</color><!--纯黄-->
    <color name="Olive">#808000</color><!--橄榄-->
    <color name="DarkKhaki">#BDB76B</color><!--深卡其布-->
    <color name="LemonChiffon">#FFFACD</color><!--柠檬薄纱-->
    <color name="PaleGodenrod">#EEE8AA</color><!--灰秋麒麟-->
    <color name="Khaki">#F0E68C</color><!--卡其布-->
    <color name="Gold">#FFD700</color><!--金-->
    <color name="Cornislk">#FFF8DC</color><!--玉米色-->
    <color name="GoldEnrod">#DAA520</color><!--秋麒麟-->
    <color name="FloralWhite">#FFFAF0</color><!--花的白色-->
    <color name="OldLace">#FDF5E6</color><!--老饰带-->
    <color name="Wheat">#F5DEB3</color><!--小麦色-->
    <color name="Moccasin">#FFE4B5</color><!--鹿皮鞋-->
    <color name="Orange">#FFA500</color><!--橙色-->
    <color name="PapayaWhip">#FFEFD5</color><!--番木瓜-->
    <color name="BlanchedAlmond">#FFEBCD</color><!--漂白的杏仁-->
    <color name="NavajoWhite">#FFDEAD</color><!--Navajo白-->
    <color name="AntiqueWhite">#FAEBD7</color><!--古代的白色-->
    <color name="Tan">#D2B48C</color><!--晒黑-->
    <color name="BrulyWood">#DEB887</color><!--结实的树-->
    <color name="Bisque">#FFE4C4</color><!--（浓汤）乳脂，番茄等-->
    <color name="DarkOrange">#FF8C00</color><!--深橙色-->
    <color name="Linen">#FAF0E6</color><!--亚麻布-->
    <color name="Peru">#CD853F</color><!--秘鲁-->
    <color name="PeachPuff">#FFDAB9</color><!--桃色-->
    <color name="SandyBrown">#F4A460</color><!--沙棕色-->
    <color name="Chocolate">#D2691E</color><!--巧克力-->
    <color name="SaddleBrown">#8B4513</color><!--马鞍棕色-->
    <color name="SeaShell">#FFF5EE</color><!--海贝壳-->
    <color name="Sienna">#A0522D</color><!--黄土赭色-->
    <color name="LightSalmon">#FFA07A</color><!--浅鲜肉（鲑鱼）色-->
    <color name="Coral">#FF7F50</color><!--珊瑚-->
    <color name="OrangeRed">#FF4500</color><!--橙红色-->
    <color name="DarkSalmon">#E9967A</color><!--深鲜肉（鲑鱼）色-->
    <color name="Tomato">#FF6347</color><!--番茄-->
    <color name="MistyRose">#FFE4E1</color><!--薄雾玫瑰-->
    <color name="Salmon">#FA8072</color><!--鲜肉（鲑鱼）色-->
    <color name="Snow">#FFFAFA</color><!--雪-->
    <color name="LightCoral">#F08080</color><!--淡珊瑚色-->
    <color name="RosyBrown">#BC8F8F</color><!--玫瑰棕色-->
    <color name="IndianRed">#CD5C5C</color><!--印度红-->
    <color name="Red">#FF0000</color><!--纯红-->
    <color name="Brown">#A52A2A</color><!--棕色-->
    <color name="FireBrick">#B22222</color><!--耐火砖-->
    <color name="DarkRed">#8B0000</color><!--深红色-->
    <color name="Maroon">#800000</color><!--栗色-->
    <color name="White">#FFFFFF</color><!--纯白-->
    <color name="WhiteSmoke">#F5F5F5</color><!--白烟-->
    <color name="Gainsboro">#DCDCDC</color><!--Gainsboro-->
    <color name="LightGray">#D3D3D3</color><!--浅灰色-->
    <color name="Silver">#C0C0C0</color><!--银白色-->
    <color name="DarkGray">#A9A9A9</color><!--深灰色-->
    <color name="Gray">#808080</color><!--灰色-->
    <color name="DimGray">#696969</color><!--暗淡的灰色-->
    <color name="Black">#000000</color><!--纯黑-->

</resources>