java中怎么把报表导出到excel
导入数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
@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
;
}
|
导出
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
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();
}
}
|
