导入数据库
@RequestMapping("/uploadOrderFile")
@ResponseBody
public Object uploadOrderFile(HttpServletRequest request, HttpServletResponse response,@RequestParam(value = "file") MultipartFile[] files) throws ServletException, IOException, ParseException{
Workbook rwb=null;
if(files!=null && files.length>0){
try {
// String filePath = request.getSession().getServletContext().getRealPath("/") + "\\uploadOrderFile\\" + files.getOriginalFilename();
// System.out.println("----------"+filePath);
rwb = Workbook.getWorkbook(files[0].getInputStream());
Sheet rs=rwb.getSheet(0);//默认0是第一张表，或者rwb.getSheet(Sheet1)Excel要导入的表名
int clos=rs.getColumns();//得到所有的列
int rows=rs.getRows();//得到所有的行
//存放Excel表抬头名称以及对应的列
Map<Integer,Object> map=new HashMap<Integer, Object>();
//实体类集合存放每次循环获得的值
List<Medicine> medicineList=new ArrayList<Medicine>();
for (int i = 0; i < rows; i++) {
//创建实体类
Medicine medicine=new Medicine();
if(i==0){//遍历第一行获取抬头跟对应的列
for (int j = 0; j <clos; j++) {//取得每个抬头名称对应的列
if(rs.getCell(j, i).getContents().equals("品名")){
map.put(j,"品名");
}else if(rs.getCell(j, i).getContents().equals("商品编号")){
map.put(j,"商品编号");
}else if(rs.getCell(j, i).getContents().equals("生产日期")){
map.put(j,"生产日期");
}else if(rs.getCell(j, i).getContents().equals("产地")){
map.put(j,"产地");
}else if(rs.getCell(j, i).getContents().equals("生产厂家")){
map.put(j,"生产厂家");
}else if(rs.getCell(j, i).getContents().equals("批号")){
map.put(j,"批号");
}
}
}else{
//循环遍历map 》》》存的Excel表的抬头名称以及对应的列
for (int j = 0; j < clos; j++) {
if(map.get(j)==null){//如果=null 进入下一个循环
continue;
}
if(map.get(j).equals("品名")){
//如果为空结束当前循环，进入下一个循环
if(rs.getCell(j, i).getContents()==null||rs.getCell(j, i).getContents().equals("")){
continue;
}
medicine.setMedicineName(rs.getCell(j, i).getContents());
}else if(map.get(j).equals("商品编号")&&map.get(j)!=null){
//如果为空结束当前循环，进入下一个循环
if(rs.getCell(j, i).getContents()==null||rs.getCell(j, i).getContents().equals("")){
continue;
}
medicine.setMedicineCode(rs.getCell(j, i).getContents());
}else if(map.get(j).equals("生产日期")&&map.get(j)!=null){
medicine.setCreateTime(rs.getCell(j, i).getContents());
if(rs.getCell(j, i).getContents()!=null && !rs.getCell(j, i).getContents().equals("")){
//如果生产日期存在 则+三年给到期日期赋值
//CommonUtil.getMedicineEffectiveTime为封装好的类
medicine.setEffectTime(CommonUtil.getMedicineEffectiveTime(rs.getCell(j, i).getContents(),3));
}
}else if(map.get(j).equals("产地")&&map.get(j)!=null){
medicine.setAddress(rs.getCell(j, i).getContents());
}else if(map.get(j).equals("生产厂家")&&map.get(j)!=null){
medicine.setProducingArea(rs.getCell(j, i).getContents());
}else if(map.get(j).equals("批号")&&map.get(j)!=null){
medicine.setBatchNumber(rs.getCell(j, i).getContents());
}
}
medicineList.add(medicine);//获得的值放入集合中
}
}
//新增用到的list
List<Medicine> addList=new ArrayList<Medicine>();
//修改用到的list
List<Medicine> updateList=new ArrayList<Medicine>();
//导入数据
for(int i=0;i<medicineList.size();i++){
//判断商品编号是否存在
if(medicineService.selectMedicineCode(medicineList.get(i).getMedicineCode()).size()>0){
//如果存在则修改
updateList.add(medicineList.get(i));
}else{
addList.add(medicineList.get(i));
}
}
int update=0;
int add=0;
if(updateList!=null&&updateList.size()>0){
update=medicineService.updateMedicine(updateList);
}
if(addList!=null&&addList.size()>0){
add= medicineService.addMedicine(addList);
}
if(update>0||add>0){
return new ResponseModel().attr(ResponseModel.KEY_DATA,"数据导入成功！");
}else{
return new ResponseModel().attr(ResponseModel.KEY_ERROR,"数据导入失败！");
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else{
return new ResponseModel().attr(ResponseModel.KEY_ERROR,"没有需要导入的数据！");
}
return null;
}
导出
package beans.excel;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Boolean;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class MutiStyleExcelWrite {
public void createExcel(OutputStream os) throws WriteException,IOException {
//创建工作薄
WritableWorkbook workbook = Workbook.createWorkbook(os);
//创建新的一页
WritableSheet sheet = workbook.createSheet("First Sheet", 0);
//构造表头
sheet.mergeCells(0, 0, 4, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
Label title = new Label(0,0,"JExcelApi支持数据类型详细说明",titleFormate);
sheet.setRowView(0, 600, false);//设置第一行的高度
sheet.addCell(title);
//创建要显示的具体内容
WritableFont color = new WritableFont(WritableFont.ARIAL);//选择字体
color.setColour(Colour.GOLD);//设置字体颜色为金黄色
WritableCellFormat colorFormat = new WritableCellFormat(color);
Label formate = new Label(0,1,"数据格式",colorFormat);
sheet.addCell(formate);
Label floats = new Label(1,1,"浮点型");
sheet.addCell(floats);
Label integers = new Label(2,1,"整型");
sheet.addCell(integers);
Label booleans = new Label(3,1,"布尔型");
sheet.addCell(booleans);
Label dates = new Label(4,1,"日期格式");
sheet.addCell(dates);
Label example = new Label(0,2,"数据示例",colorFormat);
sheet.addCell(example);
//浮点数据
//设置下划线
WritableFont underline= new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE,WritableFont.NO_BOLD,false,UnderlineStyle.SINGLE);
WritableCellFormat greyBackground = new WritableCellFormat(underline);
greyBackground.setBackground(Colour.GRAY_25);//设置背景颜色为灰色
Number number = new Number(1,2,3.1415926535,greyBackground);
sheet.addCell(number);
//整形数据
WritableFont boldNumber = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//黑体
WritableCellFormat boldNumberFormate = new WritableCellFormat(boldNumber);
Number ints = new Number(2,2,15042699,boldNumberFormate);
sheet.addCell(ints);
//布尔型数据
Boolean bools = new Boolean(3,2,true);
sheet.addCell(bools);
//日期型数据
//设置黑体和下划线
WritableFont boldDate = new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE,WritableFont.BOLD,false,UnderlineStyle.SINGLE);
WritableCellFormat boldDateFormate = new WritableCellFormat(boldDate,DateFormats.FORMAT1);
Calendar c = Calendar.getInstance();
Date date = c.getTime();
DateTime dt = new DateTime(4,2,date,boldDateFormate);
sheet.addCell(dt);
//把创建的内容写入到输出流中，并关闭输出流
workbook.write();
workbook.close();
os.close();
}
}