好记星e900,邪恶力量第9季16,魔本是妖
我要评论<!--操作excel-->
<dependency>
<groupid>org.apache.poi</groupid>
<artifactid>poi</artifactid>
<version>4.1.0</version>
</dependency>
<dependency>
<groupid>org.apache.poi</groupid>
<artifactid>poi-ooxml</artifactid>
<version>4.1.0</version>
</dependency>
<dependency>
<groupid>org.apache.poi</groupid>
<artifactid>poi-ooxml-schemas</artifactid>
<version>4.1.0</version>
</dependency>
/**
* 读取excel
*/
public static void readexcel() {
inputstream inputstream = null;
xssfworkbook xssfworkbook = null;
try {
string past = "/操作excel.xlsx";
inputstream = new fileinputstream(past);
xssfworkbook = new xssfworkbook(inputstream);
//获取sheet的个数
int numberofsheets = xssfworkbook.getnumberofsheets();
//获取指定的sheet
system.out.println(numberofsheets);
//通过指定名称获取
xssfsheet sheet = xssfworkbook.getsheet("笔记本");
//通过下标获取
xssfsheet sheetat = xssfworkbook.getsheetat(1);
if (sheetat != null) {
//最后一行有数据的
int lastrownum = sheetat.getlastrownum();
xssfrow row;
short lastcellnum;
xssfcell cell;
for (int i = 0; i <= lastrownum; i++) {
//获取指定行
row = sheetat.getrow(i);
if (row == null) {
continue;
}
//最后一列有数据的
lastcellnum = row.getlastcellnum();
for (int j = 0; j <= lastcellnum; j++) {
cell = row.getcell(j);
if (cell == null) {
continue;
}
//数据类型
celltype celltype = cell.getcelltype();
//字符串
if (celltype.string == celltype) {
system.out.println(cell.tostring());
}
//数字
else if (celltype.numeric == celltype) {
try {
system.out.println(cell.getdatecellvalue());
} catch (exception e) {
system.out.println(cell.tostring());
}
}
//……
else {
system.out.println(cell.tostring());
}
}
}
}
} catch (exception e) {
e.printstacktrace();
} finally {
if (inputstream != null) {
try {
inputstream.close();
} catch (ioexception e) {
e.printstacktrace();
}
}
}
}
2 便签名称 便签分类 创建时间 创建人 拥有人 小明的便签 学习便签 tue sep 03 00:00:00 cst 2019 小明 小明 小明的个人便签 个人便签 sun sep 08 00:00:00 cst 2019 小明 小明
/**
* 生成excel
*/
public static void createxcel() {
xssfworkbook xssfworkbook = new xssfworkbook();
//创建一个sheet
xssfsheet sheet1 = xssfworkbook.createsheet("第一个新建的sheet");
//设置高度和宽度，也可以每行每列单独分开设置
//参数为字符个数
sheet1.setdefaultcolumnwidth(20);
sheet1.setdefaultrowheight((short) (33 * 20));
//第二个参数为字符宽度的1/256
sheet1.setcolumnwidth(5, 30 * 256);
//设置单元格样式
xssfcellstyle cellstyle = xssfworkbook.createcellstyle();
// 字体样式
font fontstyle = xssfworkbook.createfont();
fontstyle.setbold(true);
// 字体
fontstyle.setfontname("等线");
// 大小
fontstyle.setfontheightinpoints((short) 11);
// 将字体样式添加到单元格样式中
cellstyle.setfont(fontstyle);
//水平居中
cellstyle.setalignment(horizontalalignment.center);
//垂直居中
cellstyle.setverticalalignment(verticalalignment.center);
//设置 单元格填充色
defaultindexedcolormap defaultindexedcolormap = new defaultindexedcolormap();
xssfcolor clr = new xssfcolor(defaultindexedcolormap);
byte[] bytes = {
(byte) 217,
(byte) 217,
(byte) 217
};
clr.setrgb(bytes);
cellstyle.setfillpattern(fillpatterntype.solid_foreground);
cellstyle.setfillforegroundcolor(clr);
//设置单元格不为锁定，可编辑，反是用了这个样式的都可编辑
cellstyle.setlocked(false);
//锁定整个sheet不可编辑
sheet1.protectsheet("1231312");
//创建一行数据
xssfrow row;
xssfcell cell;
row = sheet1.createrow(0);
cell = row.createcell(0);
//设值
cell.setcellvalue("2");
//合并单元格
cellrangeaddress cra = new cellrangeaddress(1, 1, 0, 3); // 起始行, 终止行, 起始列, 终止列
sheet1.addmergedregion(cra);
//设置合并单元格的样式
// 使用regionutil类为合并后的单元格添加边框
// 下边框
regionutil.setborderbottom(borderstyle.medium_dashed, cra, sheet1);
// 左边框
regionutil.setborderleft(borderstyle.medium_dashed, cra, sheet1);
row = sheet1.getrow(1);
//设置合并单元格内的文本样式
//但这个单元格的边框样式会覆盖上面设置的合并单元格的样式
cellutil.getcell(row, 0).setcellstyle(cellstyle);
//设置单个单元格的样式
row = sheet1.createrow(2);
cell = row.createcell(0);
cell.setcellstyle(cellstyle);
//设置数据校验
//序列校验
string[] strarray = {
"星期一",
"星期二",
"星期三"
};
xssfdatavalidationhelper dvhelper = new xssfdatavalidationhelper((xssfsheet) sheet1);
xssfdatavalidationconstraint dvconstraint = (xssfdatavalidationconstraint) dvhelper.createexplicitlistconstraint(strarray);
cellrangeaddresslist addresslist = new cellrangeaddresslist(3, 3, 0, 2);
xssfdatavalidation validation = (xssfdatavalidation) dvhelper.createvalidation(dvconstraint, addresslist);
//显示报错提示框
validation.setshowerrorbox(true);
validation.createerrorbox("错误提示", "只能选择指定的内容！");
//设置单元格右侧显示剪头符号,显示可用的选项，默认为true
validation.setsuppressdropdownarrow(true);
//显示提示信息
validation.setshowpromptbox(true);
validation.createpromptbox("提示信息", "请选择星期填入！");
sheet1.addvalidationdata(validation);
//保护工作薄不可被修改
xssfworkbook.lockstructure();
//这个不知道有啥用
xssfworkbook.lockrevision();
//锁定excel的窗口大小，不能无限制的横向，纵向拉伸。
xssfworkbook.lockwindows();
xssfworkbook.createsheet("第二个人sheet");
outputstream outputstream = null;
try {
outputstream = new fileoutputstream("/创建excel.xlsx");
xssfworkbook.write(outputstream);
outputstream.flush();
} catch (filenotfoundexception e) {
e.printstacktrace();
} catch (ioexception e) {
e.printstacktrace();
} finally {
if (outputstream != null) {
try {
outputstream.close();
} catch (ioexception e) {
e.printstacktrace();
}
}
}
}
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
如何在java 8 stream表达式实现if/else逻辑
Java中有界队列的饱和策略(reject policy)原理解析
网友评论