package util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.RichTextString;
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.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
public static final String EXCEL_XLS = "xls";
public static final String EXCEL_XLSX = "xlsx";
public static void readExcel(String path){
FileInputStream is = null;
FileOutputStream os =null;
Workbook workbook =null;
try{
File excelFile = new File(path); // 创建文件对象
is = new FileInputStream(excelFile);
workbook = getWorkbok(is, excelFile);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置前景填充样式
cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
CreationHelper factory = workbook.getCreationHelper();
//获取sheet页总数
int sheetNums = workbook.getNumberOfSheets();
RichTextString str =null;//注释字符串 内容
Comment comment = null;//注释对象
boolean flag=false;
for(int s=0;s<sheetNums;s++){//遍历sheet
Sheet sheet = workbook.getSheetAt(s);
int rowNum =sheet.getLastRowNum();//总行数
Drawing patr = sheet.createDrawingPatriarch();
for(int r=1;r<rowNum;r++){//第二行 开始//遍历行
if(sheet.getRow(r)==null){//行为空
continue;
}
int colNum=getColNum(sheet,0);
for(int c=0;c<colNum;c++){//遍历列
Cell cell = sheet.getRow(r).getCell(c);
if(cell==null){
continue;
}
String value=(String)getCellValue( cell);//解析单元格
System.out.println(r+" 行 "+c+" 列 : "+value);
if("".equals(value)){//添加注释
flag=true;
if(excelFile.getName().endsWith(EXCEL_XLS)){
comment = (Comment) patr.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 0, (short) 2, 1));
comment.setString(new HSSFRichTextString("不能为空!"));//注释
}
if(excelFile.getName().endsWith(EXCEL_XLSX)){
comment = (Comment) patr.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 0, 0, (short) 2, 1));
str = factory.createRichTextString("不能为空!");
comment.setString(str);
}
cell.setCellComment(comment);
}
}
}
}
if(flag==true) {//excel数据不规范,注释不规范原因,并保存到磁盘
// 写数据
os = new FileOutputStream(excelFile);
workbook.write(os);//回填
os.flush();
}
}catch(Exception e){
e.printStackTrace();
}finally {
try {
if(is!=null)is.close();
if(os!=null)os.close();
if(workbook!=null)workbook.close();
}catch(Exception e2) {
}
}
}
/**
* 获取这个cell格子的值
*
* @param cell
* @return
*/
public static Object getCellValue(Cell cell) throws NullPointerException{
if(cell==null){
return "";
}
Object value = null;
int type = cell.getCellType();//获取格式
if (type == Cell.CELL_TYPE_BOOLEAN) {
value = new Boolean(cell.getBooleanCellValue());
} else if (type == Cell.CELL_TYPE_NUMERIC) {
try{
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {//日期
value = cell.getDateCellValue();
SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");
value=sdf.format(value);
} else {//数字
DecimalFormat df = new DecimalFormat("0");
value = df.format(cell.getNumericCellValue());
}
}catch(NullPointerException e){
e.printStackTrace();
throw new NullPointerException(e.getMessage());
}catch(IllegalArgumentException e){
e.printStackTrace();
throw new NullPointerException(e.getMessage());
}catch(ArithmeticException e){
e.printStackTrace();
throw new NullPointerException(e.getMessage());
}catch(Exception e){
e.printStackTrace();
throw new NullPointerException(e.getMessage());
}
} else if (type == Cell.CELL_TYPE_STRING) {
value = cell.getStringCellValue();
} else if (type == Cell.CELL_TYPE_BLANK) {
value = "";
} else if (type == Cell.CELL_TYPE_ERROR) {
value = new Byte(cell.getErrorCellValue());
} else if (type == Cell.CELL_TYPE_FORMULA) {
value = cell.getCellFormula();
} else {
value = "";
}
return value;
}
/**
* 获取列数
* @param sheet
* @param r
* @return
*/
public static int getColNum(Sheet sheet,int r){
int colNum=0;
Row row=sheet.getRow(r);
while(true){
Cell cell = row.getCell(colNum);
if(cell==null){
break;
}
String value=cell.toString().trim();
if("".equals(value)){
break;
}
++colNum;
}
return colNum;
}
/**
* 判断Excel的版本,获取Workbook
* @param in 输入流
* @param file 文件名
* @return
* @throws IOException
*/
public static Workbook getWorkbok(InputStream in, File file) throws IOException {
try{
Workbook wb = null;
if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
in.close();
return wb;
}catch(Exception e){
if(in!=null){
in.close();
}
throw new IOException(e.getMessage());
}
}
/**
* 解析excel
* @param download_file
* @param conn
* @param map
*/
public static void WriteExcel(String download_file,List<Map> list){
FileInputStream is = null;
FileOutputStream os = null;
File newFile =null;
Workbook workbook =null;
try{
newFile = new File(download_file);
is = new FileInputStream(newFile); //读取复制好的文件
workbook = new HSSFWorkbook(is); //默认读取2003版的Excel
int sheetNums = workbook.getNumberOfSheets();
// 遍历Sheet
for(int i=0;i<sheetNums;i++){//循环sheet
Sheet sheet = workbook.getSheetAt(i);
if(list!=null&&list.get(0)!=null&&!list.get(0).isEmpty()){
int rowNum=list.size();
for(int r=0;r<rowNum;r++){//遍历行
int colNum=0;
Map map=list.get(r);
for(int c=0;c<colNum;c++){//遍历列
Row row=sheet.getRow(r);
if(row==null){
row=sheet.createRow(r);
}
Cell cell =row.getCell(c);
if(cell==null){//
cell=row.createCell(c);
}
cell.setCellValue("");//写入
}
}
}
}
os = new FileOutputStream(newFile);
workbook.write(os);//写入到磁盘
os.flush();
}catch(Exception e){
e.printStackTrace();
}finally {
try {
if(is!=null)is.close();
if(os!=null)os.close();
if(workbook!=null)workbook.close();
}catch(Exception e2) {
}
}
}
}