java解析excel文件为txt文件

系统管理员 2022-07-15 08:26 377阅读 0赞

java解析excel文件，并将其以建松格式保存到txt文件中。  
1、maven配置

<dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi</artifactId>
       <version>3.9</version>
    </dependency>
    
    
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-core</artifactId>
        <version>2.6.6</version>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.2.2</version>
    </dependency>

2、java代码实现

import com.fasterxml.jackson.annotation.JsonInclude;
    import com.fasterxml.jackson.databind.DeserializationFeature;
    import com.fasterxml.jackson.databind.ObjectMapper;
    import com.fasterxml.jackson.databind.PropertyNamingStrategy;
    import com.fasterxml.jackson.databind.SerializationFeature;
    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.poifs.filesystem.POIFSFileSystem;
    
    import java.io.*;
    import java.text.SimpleDateFormat;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.LinkedHashMap;
    import java.util.Map;
    import java.util.concurrent.ConcurrentLinkedQueue;
    
    /** * Created by xxx on 2016/11/8. */
    public class ExcelToTxt {
    
        private static Map<Integer, String> keyMapName = new LinkedHashMap<Integer, String>();
        private static Map<String, String> keyMapContext = new LinkedHashMap<String, String>();
    
        static ConcurrentLinkedQueue<String> queues = new ConcurrentLinkedQueue<String>();
    
        static ObjectMapper objectMapper = new ObjectMapper()
                .setSerializationInclusion(JsonInclude.Include.NON_EMPTY)
                .disable(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES)
                .disable(SerializationFeature.FAIL_ON_EMPTY_BEANS)
               .setPropertyNamingStrategy(PropertyNamingStrategy.CAMEL_CASE_TO_LOWER_CASE_WITH_UNDERSCORES);
    
        public static void main(String[] args){
    
            try {
                HSSFWorkbook wb = null;
                POIFSFileSystem fs = null;
    
                String filePath = "F:\\datafortag\\quan-2016-11-08.xls";
                fs = new POIFSFileSystem(new FileInputStream(new File(filePath)));
                wb = new HSSFWorkbook(fs);
    
                HSSFSheet sheet = wb.getSheetAt(0);
                Integer rowNum;
                HSSFRow rowName = sheet.getRow(0);
                for (int cellNum = rowName.getFirstCellNum(); cellNum < rowName.getLastCellNum(); cellNum++){ 
                    keyMapName.put(cellNum, rowName.getCell(cellNum).getStringCellValue());
                }
    
                for(rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++){ 
                    HSSFRow row = sheet.getRow(rowNum);
                    if(row != null){
                        for (int cellNum = row.getFirstCellNum(); cellNum< row.getLastCellNum(); cellNum++){ 
                            if(row.getCell(cellNum).getStringCellValue() != null && !"".equals(row.getCell(cellNum).getStringCellValue())) {
                                keyMapContext.put(keyMapName.get(cellNum), row.getCell(cellNum).getStringCellValue());
                            }
                        }
                    }
                    if(keyMapContext != null && keyMapContext.size() > 0) {
                        queues.add(objectMapper.writeValueAsString(keyMapContext));
                    }
                }
    
                File file = new File("F:\\datafortag\\quan-2016-11-08.txt");
                FileOutputStream fileOutputStream = new FileOutputStream(file);
                OutputStreamWriter outputStream = new OutputStreamWriter(fileOutputStream);
                BufferedWriter bufferedWriter = new BufferedWriter(outputStream);
    
                int i = 0;
                while (true){
                    if(!queues.isEmpty()) {
                        String context = queues.poll();
                        if(context == null) continue;
                        bufferedWriter.write(context);
                        bufferedWriter.newLine();
                        i++;
                        System.out.println("total ：" + i);
                    }
                    if(queues.isEmpty()){
                        try{
                            bufferedWriter.flush();
                            bufferedWriter.close();
                            outputStream.flush();
                            outputStream.close();
                            fileOutputStream.flush();
                            fileOutputStream.close();
                        }
                        catch (Exception e){
                            e.printStackTrace(System.out);
                        }
                        break;
                    }
                }
            } catch (Exception e){
    
            }
        }
    }