CSV转Excel，命中关键词标红
package com.wenwo.schedule.job.mainJob; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.nio.charset.Charset; import java.util.ArrayList; import java.util.HashSet; import java.util.List; import java.util.Set; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; 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.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.RegionUtil; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.csvreader.CsvReader; /** * 关键字过滤任务 * @author wengqi * @date 2018-08-27 * */ public class CopyOfCsvToExcelTagJob { int start = 0; int firstRow = 0; int lastRow = 0; String lastID = ""; int jisuan = 0; String path_h = ""; List<Integer[]> listSz = new ArrayList<Integer[]>(); Set<String> seterror = new HashSet<String>(); public void execute(String jobData) throws Exception { File file = new File("D://tag/csv/"); try { for (String string : file.list()) { System.out.println(string); start = 0; firstRow = 0; lastRow = 0; lastID = ""; jisuan = 0; createExcel(string); } } catch (Exception e) { e.printStackTrace(); } for (String iterable_element : seterror) { System.out.println(iterable_element); } System.out.println("===>OK"); } public void createExcel(String fileName) throws IOException{ //获取一个HSSFWorkbook对象 HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = getHSSFCellStyle(workbook); //创建一个sheet HSSFSheet sheet = workbook.createSheet("Sheet"); HSSFFont jiacuFont = jiacu(workbook); //表头 HSSFRow row = sheet.createRow(2); HSSFCellStyle styleS = workbook.createCellStyle(); styleS.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 styleS.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 读取文件 CsvReader wr = new CsvReader("D://tag/csv/" + fileName,',', Charset.forName("UTF-8")); wr.setSafetySwitch(false); Integer i = 0; int index = 0; int ii =0; while (wr.readRecord()) { start ++; i++; ii++; try { row = sheet.createRow(ii); List<String> list = new ArrayList<String>(); list.add(wr.getValues()[0]); list.add(wr.getValues()[1]); list.add(wr.getValues()[2]); list.add(wr.getValues()[3]); list.add(wr.getValues()[4]); list.add(wr.getValues()[5]); list.add(wr.getValues()[6]); list.add(wr.getValues()[7]); for (int j = 0; j < list.size(); j++) { try { String keyword = list.get(1); String word = list.get(7); // 单元格合并 if (j == 0 && StringUtils.isNotEmpty(list.get(0))){ if (list.get(0).equals(lastID)){ if (jisuan == 0){ firstRow = start-1; } jisuan++; lastRow = start; } else { jisuan = 0; if (firstRow != 0 && lastRow != 0){ listSz.add(new Integer[]{firstRow,lastRow}); firstRow = 0; lastRow =0; } } lastID = list.get(0); } if(keyword.length()>32765){ keyword = keyword.substring(0, 32764); } if(word.length()>32765){ word = word.substring(0, 32764); } if(j == 7){ // 命中关键词标红 try { row.createCell(j).setCellValue(getHSSFRichTextString(keyword ,word ,jiacuFont)); } catch (Exception e) { e.printStackTrace(); } } else{ row.createCell(j).setCellValue(list.get(j)); } } catch (Exception e) { e.printStackTrace(); } } } catch (Exception e) { e.printStackTrace(); } } FileOutputStream fos = new FileOutputStream(new File("D://tag/"+fileName+".xls")); workbook.write(fos); //将工作簿写到输出流中 fos.close(); } public HSSFFont jiacu(HSSFWorkbook workbook){ HSSFFont font = workbook.createFont(); font.setFontName("宋体"); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示 font.setColor(HSSFFont.COLOR_RED); font.setFontHeightInPoints((short)12); // 设置字体大小 return font; } /* * 关键词标红 */ public HSSFRichTextString getHSSFRichTextString(String guanjianci, String content, HSSFFont font){ HSSFRichTextString richString = new HSSFRichTextString(content); try { String shuzu[] = guanjianci.split("、"); for (int i = 0; i < shuzu.length; i++) { int size = content.indexOf(shuzu[i]); int kySize = shuzu[i].length(); richString.applyFont(size, size + kySize, font); } } catch (Exception e) { // TODO: handle exception } return richString; } /** * 创建一个style * * @param workbook * @return */ private static HSSFCellStyle getHSSFCellStyle(HSSFWorkbook workbook) { HSSFCellStyle style = workbook.createCellStyle(); //居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); return style; } public HSSFCellStyle getCellStyle(HSSFWorkbook workbook){ // 样式 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居中 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直 //cellStyle.setWrapText(true); return cellStyle; } public static void main(String[] args) throws Exception { CopyOfCsvToExcelTagJob job = new CopyOfCsvToExcelTagJob(); job.execute(null); } }