Excel 读取并解密

大家好，又见面了，我是你们的朋友全栈君。package com;

/**

*

*/

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

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;

import com.utils.CryptDESUtil;

/**

* @author qu

*/

public class Main {

private static SimpleDateFormat sdf=new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);

public static void main(String[] args) throws Exception {

//一下是创建新的Excel 参数

File directory = new File(“1234解密.xls”);

directory.getCanonicalPath(); //得到的是C:/test/abc

String path = directory.getAbsolutePath(); //得到的是执行程序所在的文件路径

//File filewrite=new File(“E:\\解密1234.xls”);

File filewrite=new File(path);

filewrite.createNewFile();

OutputStream os=new FileOutputStream(filewrite);

//创建工作薄

WritableWorkbook workbook = jxl.Workbook.createWorkbook(os);

//创建新的一页

WritableSheet newSheet = workbook.createSheet(“First Sheet”,0);

//一下是处理被机密的文件

Workbook wb =null;

Sheet sheet = null;

Row row = null;

List<Map<String,String>> list = null;

String cellData = null;

File directory2 = new File(“1234.xlsx”);

directory2.getCanonicalPath(); //得到的是C:/test/abc

String path2 = directory2.getAbsolutePath(); //得到的是C:/test/abc

//String filePath = “E:\\1234.xlsx”;

wb = readExcel(path2);

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();

//打印行数和列数

//System.out.println(“rownum=”+rownum+”,colnum=”+colnum);

for (int i = 0; i<rownum; i++) {//遍历行数

row = sheet.getRow(i);

if(row !=null){

for (int j=0;j<colnum;j++){//遍历列

cellData = (String)getCellFormatValue(row.getCell(j));

//————

//前面参数是列，后面参数是行

String sss = CryptDESUtil.decodeDES(cellData);

Label newLabel = new Label(j,i,sss);//执行解密

newSheet.addCell(newLabel);

//————

}

}else{

break;

}

}

workbook.write();

workbook.close();

os.close();

}

System.out.println(“——–Success——–“);

}

//读取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:{

//判断cell是否为日期格式

if(DateUtil.isCellDateFormatted(cell)){

//转换为日期格式YYYY-mm-dd

cellValue = sdf.format(cell.getDateCellValue());

}else{

//数字

cellValue = String.valueOf(cell.getNumericCellValue());

}

break;

}

case Cell.CELL_TYPE_FORMULA:{

//判断cell是否为日期格式

if(DateUtil.isCellDateFormatted(cell)){

//转换为日期格式YYYY-mm-dd

cellValue = sdf.format(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;

}

}