赞
踩
- import com.alibaba.fastjson.JSONArray;
- import com.alibaba.fastjson.JSONObject;
- import org.apache.poi.ss.usermodel.*;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import org.springframework.web.multipart.MultipartFile;
-
- import java.io.*;
- import java.text.DecimalFormat;
- import java.time.LocalDate;
- import java.time.format.DateTimeFormatter;
- import java.util.ArrayList;
目录
<span style="background-color:#f8f8f8"><span style="color:#333333">url: jdbc:mysql://192.168.6.158:3806/sequencing_manage?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=CONVERT_TO_NULL&allowMultiQueries=true</span></span>
一、依赖
- <span style="background-color:#f8f8f8"><span style="color:#333333">
- <span style="color:#981a1a"><!--</span> <span style="color:#aa5500">//pdf加图片--></span>
- <span style="color:#981a1a"><</span><span style="color:#000000">dependency</span><span style="color:#981a1a">></span>
- <span style="color:#981a1a"><</span><span style="color:#000000">groupId</span><span style="color:#981a1a">></span><span style="color:#000000">org</span>.<span style="color:#000000">apache</span>.<span style="color:#000000">pdfbox</span><span style="color:#981a1a"></</span><span style="color:#000000">groupId</span><span style="color:#981a1a">></span>
- <span style="color:#981a1a"><</span><span style="color:#000000">artifactId</span><span style="color:#981a1a">></span><span style="color:#000000">pdfbox</span><span style="color:#981a1a"></</span><span style="color:#000000">artifactId</span><span style="color:#981a1a">></span>
- <span style="color:#981a1a"><</span><span style="color:#000000">version</span><span style="color:#981a1a">></span><span style="color:#116644">2.0</span>.<span style="color:#116644">25</span><span style="color:#981a1a"></</span><span style="color:#000000">version</span><span style="color:#981a1a">></span>
- <span style="color:#981a1a"></</span><span style="color:#000000">dependency</span><span style="color:#981a1a">></span>
- </span></span>
二、控制层代码
- <span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">import</span> <span style="color:#000000">org</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">apache</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">pdfbox</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">pdmodel</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">PDDocument</span><span style="color:var(--select-text-font-color) !important">;</span>
- <span style="color:#770088">import</span> <span style="color:#000000">org</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">apache</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">pdfbox</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">pdmodel</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">PDPage</span><span style="color:var(--select-text-font-color) !important">;</span>
- <span style="color:#770088">import</span> <span style="color:#000000">org</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">apache</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">pdfbox</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">pdmodel</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">PDPageContentStream</span><span style="color:var(--select-text-font-color) !important">;</span>
- <span style="color:#770088">import</span> <span style="color:#000000">org</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">apache</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">pdfbox</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">pdmodel</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">graphics</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">image</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">PDImageXObject</span><span style="color:var(--select-text-font-color) !important">;</span>
- <span style="color:#770088">public</span> <span style="color:#770088">class</span> <span style="color:#0000ff">pdf添加图片</span><span style="color:var(--select-text-font-color) !important"> {</span>
-
- <span style="color:#770088">public</span> <span style="color:#770088">static</span> <span style="color:#008855">void</span> <span style="color:#000000">main</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#008855">String</span><span style="color:var(--select-text-font-color) !important">[] </span><span style="color:#000000">args</span><span style="color:var(--select-text-font-color) !important">) </span><span style="color:#770088">throws</span> <span style="color:#000000">IOException</span><span style="color:var(--select-text-font-color) !important"> {</span>
- <span style="color:#008855">String</span> <span style="color:#000000">pdfPath</span> <span style="color:#981a1a">=</span> <span style="color:#aa1111">"E:\\PDFpath\\"</span><span style="color:var(--select-text-font-color) !important">;</span>
- <span style="color:#000000">File</span> <span style="color:#000000">pdffile</span> <span style="color:#981a1a">=</span> <span style="color:#770088">new</span> <span style="color:#000000">File</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#000000">pdfPath</span><span style="color:var(--select-text-font-color) !important">);</span>
- <span style="color:#000000">PDDocument</span> <span style="color:#000000">document</span> <span style="color:#981a1a">=</span> <span style="color:#000000">PDDocument</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">load</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#000000">pdffile</span><span style="color:var(--select-text-font-color) !important">);</span>
- <span style="color:#000000">PDPage</span> <span style="color:#000000">page</span> <span style="color:#981a1a">=</span> <span style="color:#000000">document</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">getPage</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#116644">0</span><span style="color:var(--select-text-font-color) !important">);</span>
- <span style="color:#000000">PDPageContentStream</span> <span style="color:#000000">contentStream</span> <span style="color:#981a1a">=</span> <span style="color:#770088">new</span> <span style="color:#000000">PDPageContentStream</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#000000">document</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#000000">page</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#000000">PDPageContentStream</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">AppendMode</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">APPEND</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#221199">false</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#221199">false</span><span style="color:var(--select-text-font-color) !important">);</span>
-
- <span style="color:#aa5500">// 个人签名</span>
- <span style="color:#000000">PDImageXObject</span> <span style="color:#000000">testPerson</span> <span style="color:#981a1a">=</span> <span style="color:#000000">PDImageXObject</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">createFromFile</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#aa1111">"E:\\食品环境报告模板\\suting.png"</span><span style="color:var(--select-text-font-color) !important">,</span><span style="color:#000000">document</span><span style="color:var(--select-text-font-color) !important">);</span>
- <span style="color:#000000">contentStream</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">drawImage</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#000000">testPerson</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">108</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">290</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">55</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">35</span><span style="color:var(--select-text-font-color) !important">);</span>
-
- <span style="color:#000000">PDImageXObject</span> <span style="color:#000000">checkPerson</span> <span style="color:#981a1a">=</span> <span style="color:#000000">PDImageXObject</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">createFromFile</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#aa1111">"E:\\食品环境报告模板\\2.png"</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#000000">document</span><span style="color:var(--select-text-font-color) !important">);</span>
- <span style="color:#000000">contentStream</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">drawImage</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#000000">checkPerson</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">300</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">305</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">65</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">25</span><span style="color:var(--select-text-font-color) !important">);</span>
- <span style="color:#aa5500">//</span>
- <span style="color:#000000">PDImageXObject</span> <span style="color:#000000">issuePerson</span> <span style="color:#981a1a">=</span> <span style="color:#000000">PDImageXObject</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">createFromFile</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#aa1111">"E:\\食品环境报告模板\\3.png"</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#000000">document</span><span style="color:var(--select-text-font-color) !important">);</span>
- <span style="color:#000000">contentStream</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">drawImage</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#000000">issuePerson</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">465</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">305</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">65</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">25</span><span style="color:var(--select-text-font-color) !important">);</span>
-
- <span style="color:#aa5500">// 公章</span>
- <span style="color:#000000">PDImageXObject</span> <span style="color:#000000">cachetImage</span> <span style="color:#981a1a">=</span> <span style="color:#000000">PDImageXObject</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">createFromFile</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#aa1111">"E:\\食品环境报告模板\\4.png"</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#000000">document</span><span style="color:var(--select-text-font-color) !important">);</span>
- <span style="color:#000000">contentStream</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">drawImage</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#000000">cachetImage</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">396</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">223</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">110</span><span style="color:var(--select-text-font-color) !important">, </span><span style="color:#116644">110</span><span style="color:var(--select-text-font-color) !important">);</span>
-
- <span style="color:#000000">contentStream</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">close</span><span style="color:var(--select-text-font-color) !important">();</span>
- <span style="color:#000000">document</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">save</span><span style="color:var(--select-text-font-color) !important">(</span><span style="color:#000000">pdfPath</span><span style="color:var(--select-text-font-color) !important">);</span>
- <span style="color:#000000">document</span><span style="color:var(--select-text-font-color) !important">.</span><span style="color:#000000">close</span><span style="color:var(--select-text-font-color) !important">();</span>
- <span style="color:var(--select-text-font-color) !important"> }</span>
- <span style="color:var(--select-text-font-color) !important">}</span></span></span>
一、先在项目根目录中创建一个lib文件,和src文件夹同级别,在lib中导入jar包。
jar包下载链接: aspose-words-15.8.0 提取码: abbt
二、在pom文件中加入jar包
- <span style="background-color:#f8f8f8"><span style="color:#333333"><dependency>
- <groupId>com.aspose</groupId>
- <artifactId>aspose-words</artifactId>
- <version>15.8.0</version>
- <scope>system</scope>
- <systemPath>${project.basedir}/lib/aspose-words-15.8.0-jdk16.jar</systemPath>
- </dependency>
- <dependency>
- <groupId>org.testng</groupId>
- <artifactId>testng</artifactId>
- <version>RELEASE</version>
- <scope>compile</scope>
- </dependency></span></span>
插入第三方jar包加上这句话才能打包部署到服务器
- <span style="background-color:#f8f8f8"><span style="color:#333333"> <configuration>
- <includeSystemScope>true</includeSystemScope>
- </configuration></span></span>
三、将License.xml直接放到resources的根目录下即可。
- <span style="background-color:#f8f8f8"><span style="color:#333333"><?xml version="1.0" encoding="UTF-8" ?>
- <License>
- <Data>
- <Products>
- <Product>Aspose.Total for Java</Product>
- <Product>Aspose.Words for Java</Product>
- </Products>
- <EditionType>Enterprise</EditionType>
- <SubscriptionExpiry>20991231</SubscriptionExpiry>
- <LicenseExpiry>20991231</LicenseExpiry>
- <SerialNumber>8bfe198c-7f0c-4ef8-8ff0-acc3237bf0d7</SerialNumber>
- </Data>
- <Signature>sNLLKGMUdF0r8O1kKilWAGdgfs2BvJb/2Xp8p5iuDVfZXmhppo+d0Ran1P9TKdjV4ABwAgKXxJ3jcQTqE/2IRfqwnPf8itN8aFZlV3TJPYeD3yWE7IT55Gz6EijUpC7aKeoohTb4w2fpox58wWoF3SNp6sK6jDfiAUGEHYJ9pjU=</Signature>
- </License></span></span>
四、工具类
- <span style="background-color:#f8f8f8"><span style="color:#333333">import java.io.File;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.InputStream;
-
- import com.aspose.words.Document;
- import com.aspose.words.License;
- import com.aspose.words.SaveFormat;
- import org.testng.annotations.Test;
-
- public class Word2PdfAsposeUtil {
-
-
- public static boolean getLicense() {
- boolean result = false;
- try {
- InputStream is = Test.class.getClassLoader().getResourceAsStream("\\license.xml"); // license.xml应放在..\WebRoot\WEB-INF\classes路径下
- License aposeLic = new License();
- aposeLic.setLicense(is);
- result = true;
- } catch (Exception e) {
- e.printStackTrace();
- }
- return result;
- }
-
- public static boolean doc2pdf(String inPath, String outPath) {
- if (!getLicense()) { // 验证License 若不验证则转化出的pdf文档会有水印产生
- return false;
- }
- FileOutputStream os = null;
- try {
- long old = System.currentTimeMillis();
- File file = new File(outPath); // 新建一个空白pdf文档
- os = new FileOutputStream(file);
- Document doc = new Document(inPath); // Address是将要被转化的word文档
- doc.save(os, SaveFormat.PDF);// 全面支持DOC, DOCX, OOXML, RTF HTML, OpenDocument, PDF,
- // EPUB, XPS, SWF 相互转换
- long now = System.currentTimeMillis();
- System.out.println("pdf转换成功,共耗时:" + ((now - old) / 1000.0) + "秒"); // 转化用时
- } catch (Exception e) {
- e.printStackTrace();
- return false;
- }finally {
- if (os != null) {
- try {
- os.flush();
- os.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- return true;
- } </span></span>
六、测试运行
- <span style="background-color:#f8f8f8"><span style="color:#333333"> public static void main(String[] arg){
- //已存在的word文档
- String docPath = "D:\\homo\\1.docx";
- //存放pdf的路径
- String pdfPath = "D:\\homo\\2.pdf";
- Word2PdfAsposeUtil.doc2pdf(docPath,pdfPath);
- }
- }</span></span>
一、依赖
- <span style="background-color:#f8f8f8"><span style="color:#333333"> <dependency>
- <groupId>org.apache.pdfbox</groupId>
- <artifactId>pdfbox</artifactId>
- <version>2.0.25</version>
- </dependency></span></span>
二、工具类
- <span style="background-color:#f8f8f8"><span style="color:#333333">
-
- import org.apache.pdfbox.pdmodel.PDDocument;
- import org.apache.pdfbox.pdmodel.encryption.*;
-
- import java.io.File;
- import java.io.IOException;
-
- public class PDFencryptUtil {
-
- //对文件夹下所有的文件加密
- public static void encryptem(String path) throws IOException{
- File dir = new File(path);
- File[] files = dir.listFiles();
- for (int i = 0; i < files.length; i++) {
- String fileName = files[i].getPath();
- encrypt(fileName,fileName);
- }
-
- }
-
- /**
- *对一个文件加密
- * @param srcpath 要加密的文件路径
- * @param despath 加密完成的文件路径
- * @throws IOException
- */
- public static void encrypt(String srcpath,String despath) throws IOException {
- //编辑功能的密码
- String ownerPassWord = "spacegen123";
- //打开功能的密码
- String userPassWord = "";
- File file = new File(srcpath);
- // long start = System.currentTimeMillis();
- PDDocument load = PDDocument.load(file);
-
- AccessPermission permissions = new AccessPermission();
- // 是否可以插入/删除/旋转页面
- permissions.setCanAssembleDocument(false);
- // 是否可以复制和提取内容
- permissions.setCanExtractContent(false);
-
- permissions.setCanExtractForAccessibility(false);
- // 设置用户是否可以填写交互式表单字段(包括签名字段)
- permissions.setCanFillInForm(false);
- // 设置用户是否可以修改文档
- permissions.setCanModify(false);
- // 设置用户是否可以添加或修改文本注释并填写交互式表单字段,如果canModify()返回true,则创建或修改交互式表单字段(包括签名字段)。
- permissions.setCanModifyAnnotations(false);
- // 设置用户是否可以打印。
- permissions.setCanPrint(false);
- // 设置用户是否可以降级格式打印文档
- permissions.setCanPrintDegraded(false);
-
- StandardProtectionPolicy p = new StandardProtectionPolicy(ownerPassWord, userPassWord, permissions);
- SecurityHandler sh = new StandardSecurityHandler(p);
- sh.prepareDocumentForEncryption(load);
- PDEncryption encryptionOptions = new PDEncryption();
- encryptionOptions.setSecurityHandler(sh);
-
- load.setEncryptionDictionary(encryptionOptions);
- load.save(despath);
-
- }
- }
- </span></span>
一、依赖
在lib添加jar包,去下面的链接下载,搜spire.doc或spire.doc.free
- <span style="background-color:#f8f8f8"><span style="color:#333333"> <dependency>
- <groupId>e-iceblue</groupId>
- <artifactId>spire.doc.free</artifactId>
- <version>5.2.0</version>
- <scope>system</scope>
- <systemPath>${project.basedir}/lib/spire.doc.free-5.2.0.jar</systemPath>
- </dependency>
-
- <configuration>
- <includeSystemScope>true</includeSystemScope>
- </configuration></span></span>
二、代码
- <span style="background-color:#f8f8f8"><span style="color:#333333">XWPFDocument copyDocument = new XWPFDocument(OPCPackage.open(FileName));
- int size = copyDocument.getTables().size();
-
- if (size < 25) {
- //加载示例文件
- Document doc = new Document();
- doc.loadFromFile(FileName);
- for (int j = 0; j < size; j++) {
-
- //doc--要加图片的word也可以是路径
- //j * 17--要加图片的段落位置
- //E:\\食品环境报告模板\\4.png--图片的位置
- //70f--宽 28f--高 421--离左边的距离 445--离上面的位置
- //TextWrappingStyle.In_Front_Of_Text---在文字上方
- //TextWrappingStyle.Behin---在文件下方
- addimage(doc, j * 17, "E:\\食品环境报告模板\\4.png", 110f, 110f, 350, 438,
- TextWrappingStyle.In_Front_Of_Text);
- addimage(doc, j * 17, "E:\\食品环境报告模板\\3.png", 70f, 28f, 421, 445,
- TextWrappingStyle.In_Front_Of_Text);
- addimage(doc, j * 17, "E:\\食品环境报告模板\\2.png", 70f, 23f, 253, 445,
- TextWrappingStyle.In_Front_Of_Text);
- addimage(doc, j * 17, "E:\\食品环境报告模板\\1.png", 50f, 32f, 69, 447,
- TextWrappingStyle.Behind);
-
- }
-
- //保存文档
- doc.saveToFile(FileName);
- doc.close();
- }</span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333"> // 复制多页
- public static void copyWordModeld(String userExcel,String wordpath) throws Exception {
-
- List<List<String>> dataList = readxlsx(userExcel);
- XWPFDocument copyDocument = new XWPFDocument(OPCPackage.open("E:\\食品环境报告模板\\食品环境新型冠状病毒核酸检测报告.docx"));
-
-
-
- Set<String> set = new HashSet<>();
- for (int i = 0; i < dataList.size(); i++) {
- String name = dataList.get(i).get(8) + "-" + dataList.get(i).get(7);
- // set.add(dataList.get(i).get(7));
- set.add(name);
- }
-
-
- Iterator<String> iterator = set.iterator();
- while (iterator.hasNext()) {
- XWPFDocument pasteDocument = new XWPFDocument(OPCPackage.open("E:\\食品环境报告模板\\公司图标.docx"));
- int count = 0;
- String company = iterator.next();
-
- for (int i = 0; i < dataList.size(); i++) {
- if (dataList.get(i).get(7).equals(company.split("-")[1])) {
- count++;
- }
- }
-
- for (int j = 0; j < count; j++) {
-
- // 获取段落信息
- List<XWPFParagraph> copyParagraphs = copyDocument.getParagraphs();
- XWPFTable copyTable = copyDocument.getTables().get(0);
- for (int i = 0; i < copyParagraphs.size(); i++) {
-
- //复制表格
- if (i == 0) {
- pasteDocument.createTable();
- pasteDocument.setTable(j, copyTable);
- }
-
- //复制段落
- pasteDocument.createParagraph();
- pasteDocument.setParagraph(copyParagraphs.get(i), i + copyParagraphs.size() * j);
- }
- }
- //删除最后一个元素(删除空白字段)
- try {
- int summary = pasteDocument.getBodyElements().size();
- pasteDocument.removeBodyElement(summary - 1);
- } catch (Exception e) {
- e.printStackTrace();
- }
-
- //创建文件夹
- // File dest = new File("E:\\" + "生成的word\\" + uuid + "\\" + company + ".docx");
- File dest = new File(wordpath + "\\" + company + ".docx");
- if (!dest.getParentFile().exists()) {
- dest.getParentFile().mkdirs();
- }
-
- // pasteDocument.write(new FileOutputStream("E:\\" + "生成的word\\" + uuid + "\\" + company + ".docx"));
- pasteDocument.write(new FileOutputStream(wordpath + "\\" + company + ".docx"));
-
- }
-
- }</span></span>
一、Util类
- <span style="background-color:#f8f8f8"><span style="color:#333333">package com.example.upload.util;
-
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.UnsupportedEncodingException;
- import java.security.MessageDigest;
- import java.security.NoSuchAlgorithmException;
-
- public class MD5Util {
- /**
- * 计算字符串的MD5值
- * @param string 明文
- * @return 字符串的MD5值
- */
- public static String md5(String string) {
- if (string.isEmpty()) {
- return "";
- }
- MessageDigest md5 = null;
- try {
- md5 = MessageDigest.getInstance("MD5");
- byte[] bytes = md5.digest(string.getBytes("UTF-8"));
- String result = "";
- for (byte b : bytes) {
- String temp = Integer.toHexString(b & 0xff);
- if (temp.length() == 1) {
- temp = "0" + temp;
- }
- result += temp;
- }
- return result;
- } catch (NoSuchAlgorithmException e) {
- e.printStackTrace();
- } catch (UnsupportedEncodingException e) {
- e.printStackTrace();
- }
- return "";
- }
-
- public static void main(String[] args) {
- String aaa = md5("aaa");
- System.out.println(md5("spacegen"));
- System.out.println(aaa);
- }
-
-
- /**
- * 计算文件的MD5值
- * @param file 文件File
- * @return 文件的MD5值
- */
- public static String md5(File file) {
- if (file == null || !file.isFile() || !file.exists()) {
- return "";
- }
- FileInputStream in = null;
- String result = "";
- byte buffer[] = new byte[0124];
- int len;
- try {
- MessageDigest md5 = MessageDigest.getInstance("MD5");
- in = new FileInputStream(file);
- while ((len = in.read(buffer)) != -1) {
- md5.update(buffer, 0, len);
- }
- byte[] bytes = md5.digest();
-
- for (byte b : bytes) {
- String temp = Integer.toHexString(b & 0xff);
- if (temp.length() == 1) {
- temp = "0" + temp;
- }
- result += temp;
- }
- } catch (Exception e) {
- e.printStackTrace();
- }finally {
- if(null!=in){
- try {
- in.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- return result;
- }
- }
- </span></span>
在yml添加这个。最外面,从顶。
- <span style="background-color:#f8f8f8"><span style="color:#333333">mybatis:
- configuration:
- log-impl: org.apache.ibatis.logging.stdout.StdOutImpl</span></span>
方法一:必须是void才不会报错方法
- @RequestMapping(value = "/downloads", method = RequestMethod.GET)
- public void downloadFile( HttpServletResponse response, @RequestParam("path") String path) throws IOException {
- File file = new File(path);
- InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
-
- // 设置响应头信息
- response.setContentType("application/octet-stream");
- response.setHeader("Content-Disposition", "attachment;filename=" + file.getName());
- response.setHeader("Content-Length", String.valueOf(file.length()));
-
- OutputStream outputStream = response.getOutputStream();
- byte[] buffer = new byte[BUFFER_SIZE];
-
- int bytesRead;
- while ((bytesRead = inputStream.read(buffer)) != -1) {
- outputStream.write(buffer, 0, bytesRead);
- }
-
- inputStream.close();
- outputStream.flush();
- outputStream.close();
- }
方法二:
- <span style="background-color:#f8f8f8"><span style="color:#333333">import java.io.*;
-
- public class 前端下载文件 {
-
- //下载文件到本地
- public HttpServletResponse download(String path, HttpServletResponse response) {
- try {
- // path是指欲下载的文件的路径。
- File file = new File(path);
- // 取得文件名。
- String filename = file.getName();
-
- // 以流的形式下载文件。
- InputStream fis = new BufferedInputStream(new FileInputStream(path));
- byte[] buffer = new byte[fis.available()];
- fis.read(buffer);
- fis.close();
- // 清空response
- response.reset();
- // 设置response的Header
- response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
- response.addHeader("Content-Length", "" + file.length());
- OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
- response.setContentType("application/octet-stream");
- toClient.write(buffer);
- toClient.flush();
- toClient.close();
- } catch (IOException ex) {
- ex.printStackTrace();
- }
- return response;
- }
- @RequestMapping("/d")
- public void a(HttpServletResponse response){
- download("E:\\ccc\\采购申请.docx",response);
- }
- }</span></span>
一、依赖
- <span style="background-color:#f8f8f8"><span style="color:#333333"><!-- jwt-->
- <dependency>
- <groupId>com.auth0</groupId>
- <artifactId>java-jwt</artifactId>
- <version>3.10.3</version>
- </dependency></span></span>
二、工具类
- <span style="background-color:#f8f8f8"><span style="color:#333333">package com.example.natreport.Util;
-
- import com.auth0.jwt.JWT;
- import com.auth0.jwt.JWTCreator;
- import com.auth0.jwt.algorithms.Algorithm;
- import com.auth0.jwt.interfaces.DecodedJWT;
- import com.auth0.jwt.interfaces.JWTVerifier;
-
- import java.util.Calendar;
- import java.util.Map;
-
- public class JWTUtils {
- // 签名密钥
- private static final String SECRET = "!DAR$";
-
- /**
- * 生成token
- * @param payload token携带的信息
- * @return token字符串
- */
- public static String getToken(Map<String,String> payload){
- // 指定token过期时间为7天
- Calendar calendar = Calendar.getInstance();
- calendar.add(Calendar.DATE, 7);
-
- JWTCreator.Builder builder = JWT.create();
- // 构建payload
- payload.forEach((k,v) -> builder.withClaim(k,v));
- // 指定过期时间和签名算法
- String token = builder.withExpiresAt(calendar.getTime()).sign(Algorithm.HMAC256(SECRET));
- return token;
- }
-
-
- /**
- * 解析token
- * @param token token字符串
- * @return 解析后的token
- */
- public static DecodedJWT decode(String token){
- JWTVerifier jwtVerifier = JWT.require(Algorithm.HMAC256(SECRET)).build();
- DecodedJWT decodedJWT = jwtVerifier.verify(token);
- return decodedJWT;
- }
- }
- </span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333">package com.example.upload.controller;
-
- import java.io.*;
- import java.util.ArrayList;
- import java.util.List;
- import java.util.zip.ZipEntry;
- import java.util.zip.ZipOutputStream;
-
- //压缩压缩压缩压缩压缩压缩压缩压缩压缩压缩压缩压缩压缩压缩压缩
- public class 压缩压缩ZipUtils {
- private static final int BUFFER_SIZE = 2 * 1024;
-
- /**
- * 压缩成ZIP 方法 * @param srcDir 压缩文件夹路径
- * @param out 压缩文件输出流
- * @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
- * false:所有文件跑到压缩包根目录下(注意:不保留目录结构可能会出现同名文件,会压缩失败)
- * @throws RuntimeException 压缩失败会抛出运行时异常
- */
- public static void toZip(String srcDir, OutputStream out, boolean KeepDirStructure)
- throws RuntimeException{
-
- long start = System.currentTimeMillis();
- ZipOutputStream zos = null ;
- try {
- zos = new ZipOutputStream(out);
- File sourceFile = new File(srcDir);
- compress(sourceFile,zos,sourceFile.getName(),KeepDirStructure);
- long end = System.currentTimeMillis();
- System.out.println("压缩完成,耗时:" + (end - start) +" ms");
- } catch (Exception e) {
- throw new RuntimeException("zip error from ZipUtils",e);
- }finally{
- if(zos != null){
- try {
- zos.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
-
- }
-
- /**
- * 压缩成ZIP 方法 * @param srcFiles 需要压缩的文件列表
- * @param out 压缩文件输出流
- * @throws RuntimeException 压缩失败会抛出运行时异常
- */
- public static void toZip(List<File> srcFiles , OutputStream out)throws RuntimeException {
- long start = System.currentTimeMillis();
- ZipOutputStream zos = null ;
- try {
- zos = new ZipOutputStream(out);
- for (File srcFile : srcFiles) {
- byte[] buf = new byte[BUFFER_SIZE];
- zos.putNextEntry(new ZipEntry(srcFile.getName()));
- int len;
- FileInputStream in = new FileInputStream(srcFile);
- while ((len = in.read(buf)) != -1){
- zos.write(buf, 0, len);
- }
- zos.closeEntry();
- in.close();
- }
- long end = System.currentTimeMillis();
- System.out.println("压缩完成,耗时:" + (end - start) +" ms");
- } catch (Exception e) {
- throw new RuntimeException("zip error from ZipUtils",e);
- }finally{
- if(zos != null){
- try {
- zos.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- }
-
-
- /**
- * 递归压缩方法
- * @param sourceFile 源文件
- * @param zos zip输出流
- * @param name 压缩后的名称
- * @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
- * false:所有文件跑到压缩包根目录下(注意:不保留目录结构可能会出现同名文件,会压缩失败)
- * @throws Exception
- */
- private static void compress(File sourceFile, ZipOutputStream zos, String name,
- boolean KeepDirStructure) throws Exception{
- byte[] buf = new byte[BUFFER_SIZE];
- if(sourceFile.isFile()){
- // 向zip输出流中添加一个zip实体,构造器中name为zip实体的文件的名字
- zos.putNextEntry(new ZipEntry(name));
- // copy文件到zip输出流中
- int len;
- FileInputStream in = new FileInputStream(sourceFile);
- while ((len = in.read(buf)) != -1){
- zos.write(buf, 0, len);
- }
- // Complete the entry
- zos.closeEntry();
- in.close();
- } else {
- //是文件夹
- File[] listFiles = sourceFile.listFiles();
- if(listFiles == null || listFiles.length == 0){
- // 需要保留原来的文件结构时,需要对空文件夹进行处理
- if(KeepDirStructure){
- // 空文件夹的处理
- zos.putNextEntry(new ZipEntry(name + "/"));
- // 没有文件,不需要文件的copy
- zos.closeEntry();
- }
-
- }else {
- for (File file : listFiles) {
- // 判断是否需要保留原来的文件结构
- if (KeepDirStructure) {
- // 注意:file.getName()前面需要带上父文件夹的名字加一斜杠,
- // 不然最后压缩包中就不能保留原来的文件结构,即:所有文件都跑到压缩包根目录下了
- compress(file, zos, name + "/" + file.getName(),KeepDirStructure);
- } else {
- compress(file, zos, file.getName(),KeepDirStructure);
- }
-
- }
- }
- }
- }
- /** 测试方法一 */
- public void download(HttpServletResponse response) throws IOException {
- ServletOutputStream outputStream = response.getOutputStream();
- toZip("D:\\a",outputStream,true);
- }
- /** 测试方法二 */
- public static void main(String[] args) throws Exception {
- /** 测试压缩方法 */
- /** 重点,下面那一行E:/testZip.zip是要生成的压缩包名字和位置,可能直接在盘符下面会报错 */
- FileOutputStream fos1= new FileOutputStream(new File("E:/testZip.zip"));
- 压缩压缩ZipUtils.toZip("E:/testZip", fos1,true);
-
- /** 测试压缩方法 */
- List<File> fileList = new ArrayList<>();
- fileList.add(new File("D:/Java/jdk1.7.0_45_64bit/bin/jar.exe"));
- fileList.add(new File("D:/Java/jdk1.7.0_45_64bit/bin/java.exe"));
- FileOutputStream fos2= new FileOutputStream(new File("c:/mytest02.zip"));
- 压缩压缩ZipUtils.toZip(fileList, fos2);
- }
- }</span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333">package com.example.upload.controller;
-
- import java.io.File;
-
- public class 删除文件夹 {
- // 删除文件夹
- private static void deleteDirectory(File file) {
- if (file.isFile()) {// 表示该文件不是文件夹
- file.delete();
- } else {
- // 首先得到当前的路径
- String[] childFilePaths = file.list();
- for (String childFilePath : childFilePaths) {
- File childFile = new File(file.getAbsolutePath() + "/" + childFilePath);
- deleteDirectory(childFile);
- }
- file.delete();
- }
- }
-
- public static void main(String[] args) {
- File del_file = new File("D:\\ppppp");
- deleteDirectory(del_file);
- }
- }
- </span></span>
方法一:
- <span style="background-color:#f8f8f8"><span style="color:#333333">import org.springframework.web.bind.annotation.RequestParam;
- import org.springframework.web.multipart.MultipartFile;
-
- import java.io.File;
- import java.io.IOException;
-
- public class 上传文件 {
-
-
- public String handleFileUpload(@RequestParam("file") MultipartFile file) {
-
- if (file.isEmpty()) {
- return "文件为空";
- }
- // 获取文件名
- String fileName = file.getOriginalFilename();
- System.out.println("上传的文件名为:" + fileName);
- // 获取文件的后缀名可以不用
- String suffixName = fileName.substring(fileName.lastIndexOf("."));
- System.out.println("上传的后缀名为:" + suffixName);
- // 文件上传后的路径
- String filePath = "D://";
- File dest = new File(filePath + fileName);
- // 检测是否存在目录
- if (!dest.getParentFile().exists()) {
- dest.getParentFile().mkdirs();
- }
- try {
- file.transferTo(dest);
- return "上传成功";
- } catch (IllegalStateException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- return "上传失败";
- }
- }</span></span>
方法二:
- <span style="background-color:#f8f8f8"><span style="color:#333333">import java.io.*;
-
- public class 文件上传 {
- //upload file,普通java上传文件方式
- public String uploadFile() throws IOException{
-
- //file为获取到的前台的文件,每个框架获取的方式不一样,可按需去查询
- File file = new File("D:\\es test\\R_2021_06_28_11_39_50_user_sn247560213-233-20210628-ZYL-01_Auto_user_sn247560213-233-20210628-ZYL-01_380.bc_summary.xls");
-
-
- //拼接成完整的指定的文件路径名,创建新文件
- // String filePath = basePath+File.separator+fileName;
- String filePath = "D:\\aaa\\R_2021_06_28_11_39_50_user_sn247560213-233-20210628-ZYL-01_Auto_user_sn247560213-233-20210628-ZYL-01_380.bc_summary.xls";
- File filelocal = new File(filePath);
- if(filelocal.exists()){
- filelocal.delete();
- }
-
- //使用输入流读取前台的file文件
- InputStream is=new FileInputStream(file);
-
- //循环读取输入流文件内容,通过输出流将内容写入新文件
- OutputStream os=new FileOutputStream(filelocal);
- byte buffer[]=new byte[1024];
- int cnt=0;
- while((cnt=is.read(buffer))>0){
- os.write(buffer, 0, cnt);
- }
- //关闭输入输出流
- os.close();
- is.close();
-
- return null;
- }
- public static void main(String[] args) {
- 文件上传 test = new 文件上传();
- try {
- test.uploadFile();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }</span></span>
方法一:
- <span style="background-color:#f8f8f8"><span style="color:#333333">只要在参数哪里写图片1的“HttpServletResponse response”输出。
- 然后图片2,response获取输出流,然后把workbook写到输入流和流一起输出给前端。wirte()是吧文件写到流里面~!!!
- </span></span>
图片1
图片2
- public void downloadResult(String resultPath, HttpServletResponse response) throws IOException {
-
- // 设置文件MIME类型
- response.setContentType(getMimeType(resultPath));
- // 设置Content-Disposition头,让浏览器以附件形式下载文件
- response.setHeader("Content-Disposition", "attachment; filename=\"" + new File(resultPath).getName() + "\"");
-
- // 读取文件并写入响应流
- FileInputStream inputStream = new FileInputStream(resultPath);
- int c;
- while ((c = inputStream.read()) != -1) {
- response.getWriter().write(c);
- }
- inputStream.close();
- }
方法二:
- <span style="background-color:#f8f8f8"><span style="color:#333333">import javax.servlet.http.HttpServletResponse;
- import java.io.*;
-
-
- public class 前端下载文件 {
-
- //下载文件到本地
- public HttpServletResponse download(String path, HttpServletResponse response) {
- try {
- // path是指欲下载的文件的路径。
- File file = new File(path);
- // 取得文件名。
- String filename = file.getName();
-
- // 以流的形式下载文件。
- InputStream fis = new BufferedInputStream(new FileInputStream(path));
- byte[] buffer = new byte[fis.available()];
- fis.read(buffer);
- fis.close();
- // 清空response
- response.reset();
- // 设置response的Header
- response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
- response.addHeader("Content-Length", "" + file.length());
- OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
- response.setContentType("application/octet-stream");
- toClient.write(buffer);
- toClient.flush();
- toClient.close();
- } catch (IOException ex) {
- ex.printStackTrace();
- }
- return response;
- }
-
- public void a(HttpServletResponse response){
- download("E:\\ccc\\采购申请.docx",response);
- }
- }
- </span></span>
一、依赖
- <span style="background-color:#f8f8f8"><span style="color:#333333"> <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>fastjson</artifactId>
- <version>1.2.76</version>
- </dependency></span></span>
二、方法
- <span style="background-color:#f8f8f8"><span style="color:#333333">import com.alibaba.fastjson.JSONArray;
- import com.alibaba.fastjson.JSONObject;
-
- import java.sql.Timestamp;
- import java.util.ArrayList;
- import java.util.Collections;
- import java.util.Comparator;
- import java.util.List;
-
- public class 将Array排序 {
- //将JsonArray字符串类型的排序
- /**
- * array 传入待排序的 JSONArray
- * sortColName 排序字段
- * isAsc 升序还是降序,true表示升序
- * sortColName,isAsc 需要加 final,否则现有环境 jenkins 打版本会失败
- */
- public static JSONArray jsonArraySort(JSONArray array, final String sortColName,
- final boolean isAsc) {
- JSONArray sortedJsonArray = new JSONArray();
- List<JSONObject> jsonList = new ArrayList<JSONObject>();
- for (int i = 0; i < array.size(); i++) {
- jsonList.add(array.getJSONObject(i));
- }
-
- //将 JSONArray 转换成 List,本质上还是对 List 进行排序
- Collections.sort(jsonList, new Comparator<JSONObject>() {
- @Override
- public int compare(JSONObject o1, JSONObject o2) {
- String valA = o1.getString(sortColName);
- String valB = o2.getString(sortColName);
- if (isAsc) {
- // 升序
- return valA.compareTo(valB);
- } else {
- return valB.compareTo(valA);
- }
- }
- });
- // 此时jsonList已经是排完序的集合了
- for(JSONObject obj : jsonList) {
- sortedJsonArray.add(obj);
- }
- return sortedJsonArray;
- }
-
-
- //将JsonArray按照时间排序
- /**
- * array 传入待排序的 JSONArray
- * sortColName 排序字段
- * isAsc 升序还是降序,true表示升序
- * sortColName,isAsc 需要加 final,否则现有环境 jenkins 打版本会失败
- */
- public static JSONArray jsonArraySort1(JSONArray array, final String sortColName,
- final boolean isAsc) {
- JSONArray sortedJsonArray = new JSONArray();
- List<JSONObject> jsonList = new ArrayList<JSONObject>();
- for (int i = 0; i < array.size(); i++) {
- jsonList.add(array.getJSONObject(i));
- }
-
- //将 JSONArray 转换成 List,本质上还是对 List 进行排序
- Collections.sort(jsonList, new Comparator<JSONObject>() {
- @Override
- public int compare(JSONObject o1, JSONObject o2) {
- Timestamp valA = o1.getTimestamp(sortColName);
- Timestamp valB = o2.getTimestamp(sortColName);
- if (isAsc) {
- if (valA.getTime()>valB.getTime()){
- return 1;
- }
- if (valA.getTime()<valB.getTime()){
- return -1;
- }else return 0;
- } else {
- if (valB.getTime()>valA.getTime()){
- return 1;
- } if (valB.getTime()<valA.getTime()){
- return -1;
- }else return 0;
- }
- }
- });
- // 此时jsonList已经是排完序的集合了
- for(JSONObject obj : jsonList) {
- sortedJsonArray.add(obj);
- }
- return sortedJsonArray;
- }
- }</span></span>
重点是注解
在主类上添加 @EnableScheduling
注解来启用任务调度器。
- <span style="background-color:#f8f8f8"><span style="color:#333333">
- import org.springframework.scheduling.annotation.Scheduled;
- //定时执行
- public class 每天执行一次 {
- // 每天执行一次(可以百度也可以每周每个月的具体时间做什么)
- @Scheduled(cron = "0 0 0 * * ?")
- public static void main(String[] args) {
-
- }
- }
- </span></span>
1.先在pom添加依赖
- <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>easyexcel</artifactId>
- <version>3.3.3</version>
- </dependency>
2.在实体类字段上写注解对应excel的表头.
@ExcelProperty(value = "基因")
然后再没有用到的对应字段或者直接在类上加上注解
@ExcelIgnoreUnannotated 表示忽略未被其他 Excel 相关注解标记的字段
3.代码
- public void exportPositive(HttpServletResponse response) {
- List<Positive> positiveList = this.list(null);
- // 设置响应头,告诉浏览器返回的是一个Excel文件
- response.setContentType("application/vnd.ms-excel");
- response.setCharacterEncoding("utf-8");
- response.setHeader("Content-disposition", "attachment;filename=Positive.xlsx");
-
- // 使用EasyExcel将数据写入输出流
- try (ServletOutputStream outputStream = response.getOutputStream()) {
- ExcelWriter excelWriter = EasyExcel.write(outputStream, Positive.class).build();
- WriteSheet writeSheet = EasyExcel.writerSheet("Positive").registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
- excelWriter.write(positiveList, writeSheet);
- excelWriter.finish();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
多个sheet
- // 使用EasyExcel将数据写入输出流
- try (ServletOutputStream outputStream = response.getOutputStream()) {
- ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
-
- List<JcZhZAudit> zhList = jcZhZAudits.stream().filter(audit -> audit.getCategory().equals("1")).collect(Collectors.toList());
- WriteSheet zhSheet = EasyExcel.writerSheet(0, "综合").head(JcZhZAudit.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
- excelWriter.write(zhList, zhSheet);
-
- List<JcZhZAudit> ztList = jcZhZAudits.stream().filter(audit -> audit.getCategory().equals("2")).collect(Collectors.toList());
- WriteSheet ztSheet = EasyExcel.writerSheet(1, "专题").head(JcZhZAudit.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
- excelWriter.write(ztList, ztSheet);
-
- List<JcZhZAudit> jdList = jcZhZAudits.stream().filter(audit -> audit.getCategory().equals("3")).collect(Collectors.toList());
- WriteSheet jdSheet = EasyExcel.writerSheet(2, "进度").head(JcZhZAudit.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
- excelWriter.write(jdList, jdSheet);
-
- excelWriter.finish();
- } catch (IOException e) {
- e.printStackTrace();
- }
生成一个工作簿 2010以上格式使用 XSSFWorkBook 对象, 2003格式使用 HSSFWorkBook 对象, 其他对象操作基本一样.
public void test1() { HSSFWorkbook workbook = new HSSFWorkbook();
- <span style="background-color:#f8f8f8"><span style="color:#333333">CellStyle cellStyle = workbook.createCellStyle();
- cellStyle.setBorderBottom(BorderStyle.THIN);
- cellStyle.setBorderLeft(BorderStyle.THIN);
- cellStyle.setBorderRight(BorderStyle.THIN);
- cellStyle.setBorderTop(BorderStyle.THIN);
-
- Font font = workbook.createFont();
- font.setFontName("宋体");
- font.setFontHeightInPoints((short) 12);
- cellStyle.setFont(font);
-
- HSSFSheet sheet = workbook.createSheet("Sheet1");
- //设置单元格宽度
- sheet.setColumnWidth(0, 30 * 256);
- sheet.setColumnWidth(1, 30 * 256);
- sheet.setColumnWidth(2, 30 * 256);
-
- Row row0 = sheet.createRow(0);
- Cell cell0 = row0.createCell(0);
- cell0.setCellValue("序号");
- cell0.setCellStyle(cellStyle);
-
- Cell cell1 = row0.createCell(1);
- cell1.setCellValue("姓名");
-
- Cell cell2 = row0.createCell(2);
- cell2.setCellValue("成绩");
-
- OutputStream os = null;
- try {
- os = new FileOutputStream("d:\\测试生成2003.xls");
- workbook.write(os);
- os.close();
- } catch (Exception e) {
- e.printStackTrace();
- }</span></span>
}
@Test public void test2() { XSSFWorkbook workbook = new XSSFWorkbook();
- <span style="background-color:#f8f8f8"><span style="color:#333333">CellStyle cellStyle = workbook.createCellStyle();
- cellStyle.setBorderBottom(BorderStyle.THIN);
- cellStyle.setBorderLeft(BorderStyle.THIN);
- cellStyle.setBorderRight(BorderStyle.THIN);
- cellStyle.setBorderTop(BorderStyle.THIN);
-
- Font font = workbook.createFont();
- font.setFontName("宋体");
- font.setFontHeightInPoints((short) 12);
- cellStyle.setFont(font);</span></span>
XSSFSheet sheet = workbook.createSheet("Sheet1"); Row row0 = sheet.createRow(0); Cell cell0 = row0.createCell(0); cell0.setCellValue("序号"); cell0.setCellStyle(cellStyle); Cell cell1 = row0.createCell(1); cell1.setCellValue("姓名"); Cell cell2 = row0.createCell(2); cell2.setCellValue("成绩"); OutputStream os = null; try { os = new FileOutputStream("d:\测试生成2010.xlsx"); workbook.write(os); os.close(); } catch (Exception e) { e.printStackTrace(); } } ———————————————— 版权声明:本文为CSDN博主「Trouvailless」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。 原文链接:java使用poi生成excel_ikt4435的博客-CSDN博客_poi生成excel
方法一:
- // 日期格式化字符串
- String dateFormat = "yyyy-MM-dd";
- // 创建日期格式化器
- CreationHelper creationHelper = workbook.getCreationHelper();
- CellStyle cellStyle = workbook.createCellStyle();
- cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(dateFormat));
- HSSFCell cell7 = row.createCell(7);
- cell7.setCellValue(sampleInformation.getUpdateTime());
- cell7.setCellStyle(cellStyle);
- <span style="background-color:#f8f8f8"><span style="color:#333333">使用XSSFWorkbook获取CreationHelper和CellStyle的实例。获取格式并将其设置为sell。
-
- CellStyle cellStyle = workbook.createCellStyle();
- CreationHelper createHelper = workbook.getCreationHelper();
- short dateFormat = createHelper.createDataFormat().getFormat("yyyy-dd-MM");
- cellStyle.setDataFormat(dateFormat);
- 最后只需要将风格设置到单元格即可
-
- cell.setCellStyle(cellStyle);</span></span>
好用
easyexcel方法
1.先在pom添加依赖
- <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>easyexcel</artifactId>
- <version>3.3.3</version>
- </dependency>
2.在实体类字段上写注解对应excel的表头.
@ExcelProperty(value = "基因")
然后再没有用到的对应字段或者直接在类上加上注解
@ExcelIgnoreUnannotated 表示忽略未被其他 Excel 相关注解标记的字段
- @ExcelProperty(value = "基因")
- private String geneSymbol;
-
- /**
- * 核苷酸变异
- */
- @ExcelProperty(value = "核苷酸变异")
- private String hgvsc;
-
- /**
- * 氨基酸变异
- */
- @ExcelProperty(value = "氨基酸变异")
- private String hgvsp;
-
- /**
- * 药物名称
- */
- @ExcelProperty(value = "药物名称")
- private String drugName;
3.1如果不做特殊处理判断不需要监听器(监听器设置为null)直接这样获取到所有行的数据。会吧每一行生成相应的实体类“BiodbDrugAnalysis”。
drugAnalysisTable.getInputStream()是传入的文件的流也可以传入路径path
BiodbDrugAnalysis.class是对应的实体类
public void batchAddDrugAnalysistest(@RequestBody MultipartFile drugAnalysisTable) throws IOException { List<BiodbDrugAnalysis> list = EasyExcel.read(drugAnalysisTable.getInputStream(), BiodbDrugAnalysis.class,null).sheet().doReadSync(); biodbDrugAnalysisService.saveOrUpdateBatch(list); }
3.2 如果要对每一行进行处理需要监听器
new BiodbDrugAnalysisListener()就是我们创建的监听器
public void batchAddDrugAnalysistest(@RequestBody MultipartFile drugAnalysisTable) throws IOException { EasyExcel.read(drugAnalysisTable.getInputStream(), BiodbDrugAnalysis.class,new BiodbDrugAnalysisListener()).sheet().doReadSync(); }
3.2.1开始创建监听器 invoke和doAfterAllAnalysed是必须的。invoke中的data是每一行的数据。
BiodbDrugAnalysis是对应的实体类
doAfterAllAnalysed是数据读取完成后的操作
processArrayList是我们自己定义的方法用来处理集合数据
- package com.sys.biodbsys_backend.utils;
-
- import com.alibaba.excel.context.AnalysisContext;
- import com.alibaba.excel.event.AnalysisEventListener;
- import com.sys.biodbsys_backend.entity.biodb.BiodbDrugAnalysis;
- import com.sys.biodbsys_backend.service.BiodbDrugAnalysisService;
- import com.sys.biodbsys_backend.service.impl.BiodbDrugAnalysisServiceImpl;
- import org.springframework.stereotype.Component;
-
- import java.util.ArrayList;
- import java.util.List;
-
- @Component
- public class BiodbDrugAnalysisListener extends AnalysisEventListener<BiodbDrugAnalysis> {
-
- ArrayList<BiodbDrugAnalysis> biodbDrugAnalyses = new ArrayList<>();
-
- @Override
- public void invoke(BiodbDrugAnalysis data, AnalysisContext context) {
- // 处理每行数据
- biodbDrugAnalyses.add(data);
-
- System.out.println("Read data: " + data.toString());
- }
-
- @Override
- public void doAfterAllAnalysed(AnalysisContext context) {
- // 数据读取完成后的操作
- processArrayList(biodbDrugAnalyses);
- System.out.println("All data has been read.");
- }
-
- private void processArrayList(List<BiodbDrugAnalysis> biodbDrugAnalyses) {
- // 进行后续处理,例如保存到数据库
- // ...
- BiodbDrugAnalysisService biodbDrugAnalysisService = new BiodbDrugAnalysisServiceImpl();
- biodbDrugAnalysisService.saveOrUpdateBatch(biodbDrugAnalyses);
- }
-
- }
一、混合读取
- <span style="background-color:#f8f8f8"><span style="color:#333333">FileInputStream fis = null;
- Workbook wb = null;
- try {
- fis = new FileInputStream(path);
- if (filePath.equals(".xls")){
- wb = new HSSFWorkbook(fis);
- }else if (filePath.equals(".xlsx")){
- wb = new XSSFWorkbook(fis);
- }
- } catch (IOException e) {
- e.printStackTrace();
- }
- int lastRowNum = wb.getSheetAt(0).getLastRowNum();
- Sheet sheetAt = wb.getSheetAt(0);</span></span>
读取Excel的数字会变成科学计数法
- <span style="background-color:#f8f8f8"><span style="color:#333333"> if (j == 3) {
- org.apache.poi.ss.usermodel.Cell cell = sheetAt.getRow(i).getCell(j);
- //如果CELL_TYPE_NUMERIC爆红就改成if (cell.getCellType() == CellType.NUMERIC) 因为poi版本4.多中org.apache.poi.ss.usermodel.Cell类中的CELL_TYPE_NUMERIC已被废弃。取而代之的是使用CellType枚举类型来表示单元格的类型
- if (cell.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) {
- long longVal = Math.round(cell.getNumericCellValue());
- Double doubleVal = cell.getNumericCellValue();
- if (Double.parseDouble(longVal + ".0") == doubleVal) {
- cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
- oneData.add(sheetAt.getRow(i).getCell(j).toString());
- }
- } else {
- oneData.add(sheetAt.getRow(i).getCell(j).toString());
- }
-
- } else {
- try {
- oneData.add(sheetAt.getRow(i).getCell(j).toString());
- } catch (Exception e) {
- oneData.add("");
- }
- }</span></span>
读取Excel时间会变成 "21-五月-2019"
- <span style="background-color:#f8f8f8"><span style="color:#333333"> if (j==7){
- //读取时间格式要用 getDateCellValue() 读取时间
- if(HSSFDateUtil.isCellDateFormatted(sheetAt.getRow(i).getCell(j))){
- oneData.add(sheetAt.getRow(i).getCell(j).getDateCellValue().toString());
- }
- }
-
-
- //时间格式
- String datestr= "Mon Aug 15 11:24:39 CST 2016";//Date的默认格式显示
- Date date=new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy", Locale.UK)).parse(datestr);
- //格式化
- SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
- String sDate=sdf.format(date);
- System.out.println(sDate);</span></span>
二、读取xls文件
依赖
- <span style="background-color:#f8f8f8"><span style="color:#333333"> <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.15</version>
- </dependency>
- </span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333">import jxl.Cell;
- import jxl.Sheet;
- import jxl.Workbook;
- import jxl.WorkbookSettings;
- import jxl.read.biff.BiffException;
-
- import java.io.*;
- import java.util.ArrayList;
- import java.util.List;
-
- //读取xls文件!!!
- /**
- * Created by yjl on 2018-07-18.
- */
- public class 读取excel数据 {
- public static void main(String[] args) {
- 读取excel数据 obj = new 读取excel数据();
- // 这个是excel数据文件
- File file = new File("E:/aaa/工作簿1.xls");
- // obj.readExcel(file);
- try {
- //得到所有数据
- List<List<String>> allData=readExcel2(file);
- System.out.println(allData);
- } catch (Exception e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
-
- /**
- * 获取数据
- * @param file
- * @return
- * @throws Exception
- */
- public static List<List<String>> readExcel2(File file) throws Exception {
-
- // 创建输入流,读取Excel
- InputStream is = new FileInputStream(file.getAbsolutePath());
- // jxl提供的Workbook类
- //Workbook wb = Workbook.getWorkbook(is);//这样会出现乱码,改成下面的这种形式
-
- WorkbookSettings workbookSettings = new WorkbookSettings();
- workbookSettings.setEncoding("ISO-8859-1");
- Workbook wb= Workbook.getWorkbook(is,workbookSettings);
-
-
-
- // 只有一个sheet,直接处理
- //创建一个Sheet对象
- Sheet sheet = wb.getSheet(0);
- // 得到所有的行数
- int rows = sheet.getRows();
- // 所有的数据
- List<List<String>> allData = new ArrayList<List<String>>();
- // 越过第一行 它是列名称
- for (int j = 2; j < rows; j++) {
-
- List<String> oneData = new ArrayList<String>();
- // 得到每一行的单元格的数据
- Cell[] cells = sheet.getRow(j);
- for (int k = 0; k < cells.length; k++) {
-
- oneData.add(cells[k].getContents().trim());
- }
- // 存储每一条数据
- allData.add(oneData);
- // 打印出每一条数据
- //System.out.println(oneData);
-
- }
- return allData;
-
- }
- }</span></span>
三、读取xlsx文件
依赖
- <span style="background-color:#f8f8f8"><span style="color:#333333"> <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- <version>3.15</version>
- </dependency>
-
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.15</version>
- </dependency>
- </span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333">import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.ss.usermodel.Sheet;
- import org.apache.poi.ss.usermodel.Workbook;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.util.ArrayList;
- import java.util.List;
-
- public class 读取xlsx数据 {
-
- //读取xlsx数据
- public static List<List<String>> readxlsx(String path) throws IOException {
- FileInputStream fis = null;
- String filePath = path.substring(path.lastIndexOf("."));
-
- Workbook wb = null;
- try {
- fis = new FileInputStream(path);
- if (filePath.equals(".xls")) {
- wb = new HSSFWorkbook(fis);
- } else if (filePath.equals(".xlsx")) {
- wb = new XSSFWorkbook(fis);
- }
- } catch (IOException e) {
- e.printStackTrace();
- }
-
- Sheet sheetAt = wb.getSheetAt(0);
- int lastRowNum = sheetAt.getLastRowNum();
- List<List<String>> lists = new ArrayList<>();
-
- for (int i = 0; i <=lastRowNum ; i++) {
-
- List<String> oneData = new ArrayList<String>();
- short lastCellNum = sheetAt.getRow(0).getLastCellNum();
-
- for (int j = 0; j < lastCellNum; j++) {
- oneData.add(sheetAt.getRow(i).getCell(j).toString());
- }
- lists.add(oneData);
- }
- return lists;
- }
-
- public static void main(String[] args) throws IOException{
- List<List<String>> readxlsx = readxlsx("E:\\ddd\\d7.xlsx");
- System.out.println(readxlsx);
- }
- }
- </span></span>
- public static List<List<String>> readxlsx(@RequestParam("file") MultipartFile file){
- Workbook wb = null;
- String filePath = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
- try {
- InputStream inputStream = new BufferedInputStream(file.getInputStream());
- if (filePath.equals(".xls")) {
- wb = new HSSFWorkbook(inputStream);
- } else if (filePath.equals(".xlsx")) {
- wb = new XSSFWorkbook(inputStream);
- }
- } catch (IOException e) {
- e.printStackTrace();
- }
- Sheet sheetAt = wb.getSheetAt(0);
- int lastRowNum = sheetAt.getLastRowNum();
- List<List<String>> lists = new ArrayList<>();
-
- for (int i = 0; i <=lastRowNum ; i++) {
-
- List<String> oneData = new ArrayList<String>();
- short lastCellNum = sheetAt.getRow(0).getLastCellNum();
-
- for (int j = 0; j < lastCellNum; j++) {
- oneData.add(sheetAt.getRow(i).getCell(j).toString());
- }
- lists.add(oneData);
- }
- return lists;
- }
- <span style="background-color:#f8f8f8"><span style="color:#333333">// 1.迭代遍历:
- Set<String> set = new HashSet<String>();
- Iterator<String> it = set.iterator();
- while (it.hasNext()) {
- String str = it.next();
- System.out.println(str);
- }
-
- // 2.for循环遍历:
- for (String str : set) {
- System.out.println(str);
- }
- </span></span>
- map.forEach((key,value)->{
- System.out.println(key);
- System.out.println(value);
- });
- <span style="background-color:#f8f8f8"><span style="color:#333333">import java.io.File;
- import java.util.ArrayList;
- import java.util.List;
-
- public class 遍历文件夹查找文件下的所有同类型文件 {
- public static List<File> getFileList(String strPath) {
- File dir = new File(strPath);
- File[] files = dir.listFiles(); // 该文件目录下文件全部放入数组
- // List<Object> objects = new List<>();
- // List<int> l = new List<int>();
- ArrayList<File> filelist = new ArrayList<>();
-
- if (files != null) {
- for (int i = 0; i < files.length; i++) {
- String fileName = files[i].getName();
- if (files[i].isDirectory()) { // 判断是文件还是文件夹
- getFileList(files[i].getAbsolutePath()); // 获取文件绝对路径
- } else if (fileName.endsWith("tn.bc_summary.xls")) { // 判断文件名是否以.avi结尾
- String strFileName = files[i].getAbsolutePath();
- System.out.println("---" + strFileName);
- filelist.add(files[i]);
- } else {
- continue;
- }
- }
-
- }
- System.out.println(filelist);
- return filelist;
- }
-
- public static void main(String[] args) {
- getFileList("D:\\filesearchtest");
- }
- }</span></span>
导入依赖
- <span style="background-color:#f8f8f8"><dependency>
- <groupId>com.baomidou</groupId>
- <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
- <version>${version}</version>
- </dependency>
- </span>
配置数据源。
- <span style="background-color:#f8f8f8"><span style="color:#333333">spring:
- datasource:
- dynamic:
- primary: master #设置默认的数据源或者数据源组,默认值即为master
- strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
- datasource:
- master:
- url: jdbc:mysql://xx.xx.xx.xx:3306/dynamic
- username: root
- password: 123456
- driver-class-name: com.mysql.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
- slave_1:
- url: jdbc:mysql://xx.xx.xx.xx:3307/dynamic
- username: root
- password: 123456
- driver-class-name: com.mysql.jdbc.Driver
- slave_2:
- url: ENC(xxxxx) # 内置加密,使用请查看详细文档
- username: ENC(xxxxx)
- password: ENC(xxxxx)
- driver-class-name: com.mysql.jdbc.Driver
- #......省略
- #以上会配置一个默认库master,一个组slave下有两个子库slave_1,slave_2
- </span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333"># 多主多从 纯粹多库(记得设置primary) 混合配置
- spring: spring: spring:
- datasource: datasource: datasource:
- dynamic: dynamic: dynamic:
- datasource: datasource: datasource:
- master_1: mysql: master:
- master_2: oracle: slave_1:
- slave_1: sqlserver: slave_2:
- slave_2: postgresql: oracle_1:
- slave_3: h2: oracle_2:
- </span></span>
使用 @DS 切换数据源。
@DS 可以注解在方法上或类上,同时存在就近原则 方法上注解 优先于 类上注解。
注解 | 结果 |
---|---|
没有@DS | 默认数据源 |
@DS("dsName") | dsName可以为组名也可以为具体某个库的名称 |
- <span style="background-color:#f8f8f8"><span style="color:#333333">@Service
- @DS("slave")
- public class UserServiceImpl implements UserService {
-
- @Autowired
- private JdbcTemplate jdbcTemplate;
-
- public List selectAll() {
- return jdbcTemplate.queryForList("select * from user");
- }
-
- @Override
- @DS("slave_1")
- public List selectByCondition() {
- return jdbcTemplate.queryForList("select * from user where age >10");
- }
- }
- </span></span>
dao层
- <span style="background-color:#f8f8f8"><span style="color:#333333">@Insert("insert into receivedCount (time,batch) values(#{time},#{batch})")
- @Options(useGeneratedKeys = true,keyProperty = "PackagingCode",keyColumn = "PackagingCode")
- Boolean insertCount(receivedCount receivedCount);</span></span>
implement层
- <span style="background-color:#f8f8f8"><span style="color:#333333">@Override
- public JSONObject insertCount( receivedCount receivedCount) {
- JSONObject jsonObject = new JSONObject();
- if (dao.insertCount(receivedCount)==true){
- jsonObject.put("code",200);
- jsonObject.put("date",receivedCount.getPackagingCode());
- }else jsonObject.put("code",500);
- return jsonObject;
-
- }</span></span>
实体类
实体类添加PackagingCode(PackagingCode和dao中的@Options注解里面一样,不在数据库字段中)
- <span style="background-color:#f8f8f8"><span style="color:#333333">public enum Light {
-
- // 利用构造函数传参
-
- RED (1), GREEN (3), YELLOW (2);
-
-
-
- // 定义私有变量(可以不写)
-
- private int nCode ;
-
-
-
- // 构造函数,枚举类型只能为私有
-
- private Light( int _nCode) {
-
- this . nCode = _nCode;(这行可以不写)
-
- }
- //这下面可以都不写
- @Override
-
- public String toString() {
- return String.valueOf ( this . nCode );
- }</span></span>
如果不打算赋值
- <span style="background-color:#f8f8f8"><span style="color:#333333">public enum Light {
- // 利用构造函数传参
-
- RED, GREEN, YELLOW ;
- }</span></span>
添加依赖
- <span style="background-color:#f8f8f8"><span style="color:#333333">
- <!--Hutool-->
- <dependency>
- <groupId>cn.hutool</groupId>
- <artifactId>hutool-all</artifactId>
- <version>5.7.16</version>
- </dependency></span></span>
controller
- <span style="background-color:#f8f8f8"><span style="color:#333333">import cn.hutool.json.JSONUtil;
- import com.alibaba.fastjson.JSONObject;
-
- ExamAttend examAttend = new ExamAttend();
- examAttend.setExamName("考试");
- examAttend.setExamAttendUserUuid("123");
- String examAttendStr = JSONUtil.toJsonStr(examAttend);
- JSONObject jsonObject = JSONObject.parseObject(examAttendStr);
- System.out.println(jsonObject);
- </span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333">//服务器执行命令行方法
- public static List<String> executeNewFlow(List<String> commands) {
- List<String> rspList = new ArrayList<String>();
- Runtime run = Runtime.getRuntime();
- try {
- Process proc = run.exec("/bin/bash", null, null);
- BufferedReader in = new BufferedReader(new InputStreamReader(proc.getInputStream()));
- PrintWriter out = new PrintWriter(new BufferedWriter(new OutputStreamWriter(proc.getOutputStream())), true);
- for (String line : commands) {
- out.println(line);
- }
- // out.println("cd /home/test");
- // out.println("pwd");
- // out.println("rm -fr /home/proxy.log");
- out.println("exit");// 这个命令必须执行,否则in流不结束。
- String rspLine = "";
- while ((rspLine = in.readLine()) != null) {
- System.out.println(rspLine);
- rspList.add(rspLine);
- }
- proc.waitFor();
- in.close();
- out.close();
- proc.destroy();
- } catch (IOException e1) {
- e1.printStackTrace();
- } catch (InterruptedException e) {
- e.printStackTrace();
- }
- return rspList;
- }</span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333">@RequestMapping("/analyse")
- public void analyse(String R1,String R2,String PROJECT,String MIN,String MAX){
- List<String> commands= new ArrayList<>();
- commands.add("cd /home/yaoms");
- commands.add("touch test.txt");
- executeNewFlow(commands);
- }</span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333">public static boolean isContainChinese(String str) {
-
- Pattern p = Pattern.compile("[\u4e00-\u9fa5]");
- Matcher m = p.matcher(str);
- if (m.find()) {
- return true;
- }
- return false;
- }</span></span>
例如“123 ”转成“123”
- <span style="background-color:#f8f8f8"><span style="color:#333333">public static Integer strToInt(String str){
- str = str.trim();
- String resStr = "";
- if(str != null && !str.equals("")){
- for (int i = 0; i < str.length(); i++){
- if(str.charAt(i) >= 48 && str.charAt(i) <= 57){
- resStr += str.charAt(i);
- }
- }
- return Integer.valueOf(resStr) ;
- }
- return 0;
- }</span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333">package com.example.received.controller;
-
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import org.jumpmind.symmetric.csv.CsvReader;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
-
- import java.io.*;
- import java.nio.charset.Charset;
-
- /**
- *-----------------------maven 依赖----------------------------
- * CSV解析依赖包
- * <dependency>
- * <groupId>org.jumpmind.symmetric</groupId>
- * <artifactId>symmetric-csv</artifactId>
- * <version>3.5.19</version>
- * </dependency>
- *
- * APACHE POI包
- * <dependency>
- * <groupId>org.apache.poi</groupId>
- * <artifactId>poi-ooxml</artifactId>
- * <version>3.17</version>
- * </dependency>
- *
- * -----------------------------------------------------------
- *
- *
- * @description
- */
- public class CsvToXlsxUtil {
-
- private static final Logger log = LoggerFactory.getLogger(CsvToXlsxUtil.class);
-
- //CSV常用分隔符,如需动态扩展设置成配置项
- private static final char[] DELIMITERS = {
- ',',
- ';',
- '\001',
- ' ',
- '\t',
- '|',
- '#',
- '&'
- };
-
-
- public static void main(String[] args) {
- String csvFile = "D:/a/10.20.csv";
- System.out.println(csvToXLSX(csvFile));
- }
-
- /**
- * 读取CSV文件并写入到XLSX文件中,默认编码
- * @param csvFileAddress
- * @return
- */
- // public static String csvToXLSX(String csvFileAddress) {
- // return csvToXLSX(csvFileAddress,"GBK");
- // }
- public static String csvToXLSX(String csvFileAddress) {
- return csvToXLSX(csvFileAddress,"UTF-8");
- }
-
- /**
- * 读取CSV文件并写入到XLSX文件中,指定CSV文件编码
- * @param csvFileAddress
- * @param charset
- * @return
- */
- public static String csvToXLSX(String csvFileAddress,String charset) {
- String xlsxFileAddress = "";
- FileOutputStream fileOutputStream = null;
- try {
- char delimiter = getDelimiter(csvFileAddress);
- xlsxFileAddress = csvFileAddress.replace("csv","xlsx"); //xlsx file address
- // xlsxFileAddress = csvFileAddress.split("\\.")[0]+".xls";
-
- XSSFWorkbook workBook = new XSSFWorkbook();
- XSSFSheet sheet = workBook.createSheet(getSheetName(csvFileAddress));
- int RowNum=-1;
- CsvReader csvReader = new CsvReader(csvFileAddress, delimiter, Charset.forName(charset));
- while (csvReader.readRecord()){
- RowNum++;
- XSSFRow currentRow=sheet.createRow(RowNum);
- for(int i=0;i<csvReader.getColumnCount();i++){
- currentRow.createCell(i).setCellValue(csvReader.get(i));
- }
- }
- fileOutputStream = new FileOutputStream(xlsxFileAddress);
- workBook.write(fileOutputStream);
- return getFileName(xlsxFileAddress);
- } catch (Exception e) {
- log.error("CsvToXlsxUtil exception :" , e);
- }finally {
- try {
- fileOutputStream.close();
- } catch (IOException e) {
- log.error("CsvToXlsxUtil close FileOutputStream exception :" , e);
- }
- }
- return getFileName(xlsxFileAddress);
- }
-
-
- /**
- * 设置excel文件的sheet名称
- * 获取CSV文件名作为Excel文件的sheet名称
- * @param path
- * @return
- */
- private static String getSheetName(String path){
- try {
- String[] file = getFileName(path).split("\\.");
- return file[0];
- }catch (Exception e){
- log.error("CsvToXlsxUtil get sheet name exception : ",e);
- return "Sheet";
- }
- }
-
-
- /**
- * 根据资源路径切割获取文件名
- * @param path
- * @return
- */
- private static String getFileName(String path){
- String[] paths = path.contains("\\")?path.split("\\\\"):path.split("/");
- return paths[paths.length-1];
- }
-
- /**
- * 常用CSV分隔符数组遍历资源第一行,分隔的字段数多的为资源分隔符
- * 异常情况下默认用’,‘作为分隔符
- * @param path 资源路径
- * @return
- */
- private static char getDelimiter(String path){
- BufferedReader br = null;
- char delimiter = ',';
- try {
- br = new BufferedReader(new FileReader(path));
- String line = br.readLine();
- CsvReader csvReader;
- int columCount = 0;
- for (char delimiterTest : DELIMITERS){
- csvReader = new CsvReader(getStringStream(line),delimiterTest,Charset.forName("UTF-8"));
- if(csvReader.readRecord()){
- int newColumnCount = csvReader.getColumnCount();
- if(newColumnCount>columCount){
- columCount = newColumnCount;
- delimiter = delimiterTest;
- }
- }
- }
- } catch (Exception e) {
- log.error("CsvToXlsxUtil get delimiter exception :",e);
- }finally {
- try {
- br.close();
- } catch (IOException e) {
- log.error("CsvToXlsxUtil get delimiter close BufferedReader exception :",e);
- }
- }
- return delimiter;
- }
-
-
- /**
- * 字符串转输入流
- * 把CSV文件第一行数据转成输入流
- * @param sInputString
- * @return
- */
- private static InputStream getStringStream(String sInputString){
- if (sInputString != null && !sInputString.equals("")){
- try{
- ByteArrayInputStream tInputStringStream = new ByteArrayInputStream(sInputString.getBytes());
- return tInputStringStream;
- }catch (Exception e){
- log.error("CsvToXlsxUtil get StringStream exception :",e);
- }
- }
- return null;
- }
- }</span></span>
方法一:
- <span style="background-color:#f8f8f8"><span style="color:#333333"> /**
- * BufferedReader 读取
- * @param filePath
- * @return
- */
- public static ArrayList<String> readCsvByBufferedReader(String filePath) {
- File csv = new File(filePath);
- csv.setReadable(true);
- csv.setWritable(true);
- InputStreamReader isr = null;
- BufferedReader br = null;
- try {
- isr = new InputStreamReader(new FileInputStream(csv), "UTF-8");
- br = new BufferedReader(isr);
- } catch (Exception e) {
- e.printStackTrace();
- }
- String line = "";
- ArrayList<String> records = new ArrayList<>();
- try {
- while ((line = br.readLine()) != null) {
- System.out.println(line);
- records.add(line);
- }
- System.out.println("csv表格读取行数:" + records.size());
- } catch (IOException e) {
- e.printStackTrace();
- }
- return records;
- }</span></span>
方法二:
添加依赖
- <span style="background-color:#f8f8f8"><span style="color:#333333"> <!--csv文件操作-->
- <dependency>
- <groupId>net.sourceforge.javacsv</groupId>
- <artifactId>javacsv</artifactId>
- <version>2.0</version>
- </dependency></span></span>
- <span style="background-color:#f8f8f8"><span style="color:#333333">public static ArrayList<String> readCsvByCsvReader(String filePath) {
- ArrayList<String> strList = null;
- try {
- ArrayList<String[]> arrList = new ArrayList<String[]>();
- strList = new ArrayList<String>();
- CsvReader reader = new CsvReader(filePath, ',', Charset.forName("UTF-8"));
- while (reader.readRecord()) {
- // System.out.println(Arrays.asList(reader.getValues()));
- arrList.add(reader.getValues()); // 按行读取,并把每一行的数据添加到list集合
- }
- reader.close();
- System.out.println("读取的行数:" + arrList.size());
- // 如果要返回 String[] 类型的 list 集合,则直接返回 arrList
- // 以下步骤是把 String[] 类型的 list 集合转化为 String 类型的 list 集合
- for (int row = 0; row < arrList.size(); row++) {
- // 组装String字符串
- // 如果不知道有多少列,则可再加一个循环
- String ele = arrList.get(row)[0] + "," + arrList.get(row)[1] + ","
- + arrList.get(row)[2] + "," + arrList.get(row)[3]+ "," + arrList.get(row)[4] + "," + arrList.get(row)[5]+ "," + arrList.get(row)[6]
- + "," + arrList.get(row)[7]+ "," + arrList.get(row)[8];
- System.out.println(ele);
- strList.add(ele);
-
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- return strList;
- }</span></span>
@Transactional 详解 示例_spring to do的博客-CSDN博客_@transactional 链接很棒不懂去看
情况一:都有事务注解,异常在子方法出现,事务生效
情况二:都有事务注解,异常在主方法出现,事务生效
情况三:只有主方法有事务注解,异常在子方法出现,事务生效
情况四:只有主方法有事务注解,异常在主方法出现,事务生效
情况五:只有子方法有事务注解,异常在子方法出现,事务不生效
一、使用实例
@Transactional 事务注解开启事务,有异常就能回滚
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(); //手动回滚失误 (有时候需要有时候不需要不能就加上这句话)
- <span style="background-color:#f8f8f8"><span style="color:#333333">@PostMapping("/import")
- @Transactional(rollbackFor = Exception.class)
- public JSONObject tryfileimport(@RequestParam("file") MultipartFile file)throws ParseException, IOException {
- JSONObject jsonObject = new JSONObject();
- try {
- jsonObject.put("code",200);
- fileimport(file);
- }
- catch (Exception e){
- //下面这句是重点
- TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
- jsonObject.put("code",500);
- jsonObject.put("message","问题出在"+errorPos);
- jsonObject.put("error",e);
- }
-
- return jsonObject;
- }
- @Transactional(rollbackFor = Exception.class)
- public void fileimport(@RequestParam("file") MultipartFile file) throws ParseException, IOException {</span></span>
二、使用实例二
结论:当无事务方法调用有事务的方法时事务不会生效, 而主方法有事务去调用其他方法,无论被调用的方法有无事务,且是否出现异常(有异常需要能够抛出不被捕获),都触发事务。
/*
情况一:都有事务注解,异常在子方法出现,事务生效 */ @Override @Transactional public Long addBook(Book book) { Long result = add(book); return result; }
@Transactional public Long add(Book book){ Long result = bookDao.addBook(book); int i = 1/0; return result; }
/*
情况二:都有事务注解,异常在主方法出现,事务生效 */ @Override @Transactional public Long addBook(Book book) { Long result = add(book); int i = 1/0; return result; }
@Transactional public Long add(Book book){ Long result = bookDao.addBook(book); return result; }
/*
情况三:只有主方法有事务注解,异常在子方法出现,事务生效 */ @Override @Transactional public Long addBook(Book book) { Long result = add(book); return result; }
public Long add(Book book){ Long result = bookDao.addBook(book); int i = 1/0; return result; }
/*
情况四:只有主方法有事务注解,异常在主方法出现,事务生效 */ @Override @Transactional public Long addBook(Book book) { Long result = add(book); int i = 1/0; return result; }
public Long add(Book book){ Long result = bookDao.addBook(book); return result; }
/*
情况五:只有子方法有事务注解,异常在子方法出现,事务不生效 */ @Override public Long addBook(Book book) { Long result = add(book); return result; }
@Transactional public Long add(Book book){ Long result = bookDao.addBook(book); int i = 1/0; return result; }
国际时区 TimeZone ID列表 参考链接
时区显示名称,时差,时区ID
因为数据库时区是GMT+08:00,所以时区设置成415的GMT+08:00,时间存入是无误的,如果时区设置成309的时区是GMT,而数据库是GMT+08:00,所以存进数据库的时候会在加8小时。
415、GMT+08:00,时差:8小时,timezone ID:Etc/GMT-8 309、中国标准时间,时差:8小时,timezone ID:Asia/Shanghai
JAVA中的时区设置 参考链接
第一种方式、通过代码指定。 可以直接放在方法里面的第一行位置。也可以在系统的Main文件(系统名+Application)文件下直接设置
- final TimeZone timeZone = TimeZone.getTimeZone("UTC");
- TimeZone.setDefault(timeZone);
- final TimeZone timeZone = TimeZone.getTimeZone("Etc/GMT-8");
- TimeZone.setDefault(timeZone);
方法一:好用!
在yml添加
jackson: date-format: yyyy-MM-dd HH:mm:ss time-zone: GMT+8
然后要是只要年月日就在实体类添加注解
/** * 接收日期 */ @JsonFormat(pattern = "yyyy-MM-dd") private Date receiveDate;
方法二:(不一定管用)
在实体类添加
- /**
- * 更新时间
- */
- @JSONField(format="yyyy-MM-dd HH:mm:ss")
- @JsonFormat(timezone = "Asia/shanghai")
- private Date updateTime;
-
- /**
- * 上机时间
- */
- @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
- private Date computerTime;
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-web</artifactId>
- </dependency>
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>fastjson</artifactId>
- <version>1.2.47</version>
- </dependency>
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- <scope>runtime</scope>
- </dependency>
- <dependency>
- <groupId>org.projectlombok</groupId>
- <artifactId>lombok</artifactId>
- </dependency>
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>druid</artifactId>
- <version>1.1.10</version>
- </dependency>
- <dependency>
- <groupId>com.baomidou</groupId>
- <artifactId>mybatis-plus-boot-starter</artifactId>
- <version>3.5.1</version>
- </dependency>
-
- <dependency>
- <groupId>com.github.yulichang</groupId>
- <artifactId>mybatis-plus-join</artifactId>
- <version>1.2.4</version>
- </dependency>
- <!-- https://mvnrepository.com/artifact/org.samba.jcifs/jcifs -->
- <dependency>
- <groupId>org.samba.jcifs</groupId>
- <artifactId>jcifs</artifactId>
- <version>1.3.3</version>
- </dependency>
- //遍历samba文件夹
- public static HashMap<String,String> getFileList(String instrument_name) throws MalformedURLException, SmbException, ParseException {
- String host = "192.168.2.16";
- String username = "spacegen";
- String password = "Bigroup123";
-
- String filePath = "/rawdata/CN500_QC/D$/Illumina/NextSeq Control Software Output/";
- NtlmPasswordAuthentication auth = new NtlmPasswordAuthentication(host, username, password);
- SmbFile remoteSmbFile = new SmbFile("smb://" + host + filePath, auth);
- // 过滤隐藏文件
- SmbFile[] listFiles = remoteSmbFile.listFiles(new FileFilterBySamb());
- // 遍历文件夹下所有文件
- HashMap<String, String> chipHashMap = new HashMap<>();
- SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- format.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
- for (SmbFile smbFile : listFiles) {
- long lastModified = smbFile.lastModified();
- String parse = format.format(lastModified);
- chipHashMap.put(smbFile.getName(),parse);
- }
- return chipHashMap;
- }
举个例子,使用 CompletableFuture 来处理异步:
- CompletableFuture<Void> future = CompletableFuture.runAsync(() -> {
- // 异步执行的任务
- });
-
- future.thenRun(() -> {
- // 处理异步任务执行完成后的结果
- });
- JSONObject object = new JSONObject();
- object.put("id", bed2hgvs.getId());
- object.put("bed", fileName);
-
- CompletableFuture<JSONObject> future = CompletableFuture.supplyAsync(() -> {
- // 异步执行的任务
- return Callinginterface(object);
- });
-
- // 添加异步执行完成后的回调函数
- future.thenApply(result -> {
- // 转换结果集为新的对象类型
- JSONObject newObj = new JSONObject();
- // 处理业务逻辑
- return newObj;
- }).thenAccept(result -> {
- // 输出转换后的结果
- System.out.println(result.toJSONString());
- });
举个例子,使用 Runnable 和 Thread 实现异步:
- Runnable task = new Runnable() {
- @Override
- public void run() {
- // 异步执行的任务
- }
- };
-
- Thread thread = new Thread(task);
- thread.start();
举个例子,在 UI 线程中开启异步请求:
- AsyncTask.execute(new Runnable() {
- @Override
- public void run() {
- // 异步执行的任务
- // 执行完毕后调用回调函数 onResponse()
- // 如果发生错误则调用回调函数 onError()
- }
- }, new ResponseListener() {
- @Override
- public void onResponse(Object response) {
- // 处理异步任务执行结果
- }
-
- @Override
- public void onError(Throwable throwable) {
- // 处理异步任务执行异常
- }
- });
在SSM框架中,通常会将实体类分为三种:
请求参数的实体类:通常用于接收客户端请求中的数据,并传递给Controller层或Service层进行处理。该实体类包名可以根据您的项目需求自定义,但通常建议命名为com.yourcompany.projectname.vo
(VO代表Value Object)。
数据库的实体类:通常用于映射数据库表和Java对象之间的关系。该实体类包名可以根据您的项目需求自定义,但通常建议命名为com.yourcompany.projectname.pojo
(POJO代表Plain Old Java Object)。
方法返回的实体类:通常用于封装方法调用的返回结果。该实体类包名可以根据您的项目需求自定义,但通常建议命名为com.yourcompany.projectname.dto
(DTO代表Data Transfer Object)。
- //根据表头动态读取文件
- public JSONArray readExcelByHeader(String filePath){
- JSONArray jsonArray = new JSONArray();
-
- try (BufferedReader reader = new BufferedReader(new FileReader(filePath))) {
- String line;
-
- int lineNumber = 0;
- String[] headers = null;
-
- while ((line = reader.readLine()) != null) {
- lineNumber++;
-
- if (lineNumber == 1) {
- // 第一行作为表头
- headers = line.split("\t");
- } else {
- String[] values = line.split("\t");
-
- if (values.length == headers.length) {
- JSONObject jsonObject = new JSONObject();
- //如果要安装表头的顺序用下面这句。JSONObject是无序的
- //HashMap<String, String> jsonObject = new LinkedHashMap<>();
-
- for (int i = 0; i < headers.length; i++) {
- jsonObject.put(headers[i], values[i]);
- }
-
- jsonArray.add(jsonObject);
- }
- }
- }
-
- // System.out.println(jsonArray.toString(4)); // 输出格式化后的JSON数组
- } catch (IOException e) {
- e.printStackTrace();
- }
- return jsonArray;
- }
也可以用“\\s+”表示他会匹配连续出现的一个或多个空白字符。
依赖
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>4.1.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- <version>4.1.1</version>
- </dependency>
- import com.alibaba.fastjson.JSONArray;
- import com.alibaba.fastjson.JSONObject;
- import org.apache.poi.ss.usermodel.*;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import org.springframework.web.multipart.MultipartFile;
-
- import java.io.*;
- import java.text.DecimalFormat;
- import java.time.LocalDate;
- import java.time.format.DateTimeFormatter;
- import java.util.ArrayList;
- public static JSONArray ReadExcelToJsonArray(MultipartFile file){
- try (InputStream inputStream = file.getInputStream();
- Workbook workbook = new XSSFWorkbook(inputStream)) {
-
- Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
-
- JSONArray result = new JSONArray();
- ArrayList<String> columnNameList = new ArrayList<>();
- int rowId = 0;
- //下面代码永远从第一行开始读
- for (Row row : sheet) {
- int colId = 0;
- //JSONObject rowData = new JSONObject();
- LinkedHashMap<String, Object> rowData = new LinkedHashMap<>();
- for (Cell cell : row) {
- //标记起始行,字段名称(键名称)
- if(rowId == 0){
- columnNameList.add(cell.getStringCellValue());
- } else {
- CellType cellType = cell.getCellType();
- // CellType cellType = cell.getCellTypeEnum();
- String columnName = "";
- if (colId >= columnNameList.size()){
- columnName = "error" + colId;
- }else columnName = columnNameList.get(colId);
- if (cellType == CellType.STRING) {
- rowData.put(columnName,cell.getStringCellValue());
- } else if (cellType == CellType.NUMERIC) {
- if (DateUtil.isCellDateFormatted(cell)) {
- LocalDate date = cell.getLocalDateTimeCellValue().toLocalDate();
- DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
- rowData.put(columnName, date.format(formatter));
- } else {
- double numericCellValue = cell.getNumericCellValue();
- DecimalFormat decimalFormat = new DecimalFormat("#.##########");
- if (numericCellValue % 1 == 0) {
- rowData.put(columnName, decimalFormat.format(numericCellValue));
- } else {
- rowData.put(columnName, numericCellValue);
- }
- }
- } else if (cellType == CellType.BLANK) {
- rowData.put(columnName,"");
- }
- }
- colId++;
- }
- if (rowId>0)
- result.add(rowData);
- rowId++;
- }
- return result;
- } catch (IOException e) {
- e.printStackTrace();
- }
- return new JSONArray();
- }
- RestTemplate restTemplate = new RestTemplate();
- String url = "https://lab.spacege:83/nodeDataPush";
- HttpHeaders headers = new HttpHeaders();
- //添加请求头
- headers.set("Authorization","8f379");
- headers.setContentType(MediaType.APPLICATION_JSON);
- JSONObject jsonObject = new JSONObject();
- jsonObject.put("ak","7kslasBb1tDmaTYyiUHT");
- jsonObject.put("sk","b8c6e7d18f69a523cb551bf3e768fd02");
- jsonObject.put("currentNode","生信分析");
- JSONArray jsonArray = new JSONArray();
- jsonArray.add("EC/230613373-D");
- jsonObject.put("specimenNumbers",jsonArray);
- HttpEntity<JSONObject> request = new HttpEntity<>(jsonObject, headers);// 将JSONObject作为请求体创建HttpEntity对象
- ResponseEntity<JSONObject> response = restTemplate.exchange(url, HttpMethod.POST, request, JSONObject.class); // 发送POST请求并获取响应
- System.out.println(response.getBody());
@TableId(type = IdType.ASSIGN_ID)
- public class User {
- @TableId(type = IdType.ASSIGN_ID)
- private Long id;
- private String name;
- // 其他属性和方法...
- }
在 Java 正则表达式中,"+" 表示匹配前面的字符或子表达式一次或多次。在这个例子中,"[,、,;;]+" 匹配任意数量的逗号、顿号或分号。
String[] b = a.split("[,、,;;]+");
如果在正则表达式 [,、,;;]
中没有加号 (+
),则表示只匹配单个逗号、顿号或分号。
String[] b = a.split("[,、,;;]+");
我要做一个mysql表,当我导入xls时,如果xls中对应格子不存在内容,则数据库中对应值为"" 而不是null,只有三种类型,BIGINT,VARCHAR,DATETIME ,INT默认值为0,并在注释那块写上对应的列名,额外添加创建日期,修改日期,创建人和修改人,其中创建日期在创建时会自动写入当前时间,修改日期默认为创建时间,发生修改时自动写入当前时间,请基于以下列名帮我生成对应的sql语句实验名称、仪器类型、仪器名称、数据来源、下机时间。列名帮我转换成英文
正常来说下面是可以的 但是他不行
- spring:
- config:
- # location: file:/D:/12/3/a.yml
- additional-location: /D:/12/3/a.yml
另一种方法在打包的时候加上红色命令
java -jar NGS_automate_Analyse-1.5.0.0.jar --spring.config.location=D:/12/3/application-dev.yml
如果ideaa会报错在idea的
在类中调用yml的值
- @Value("${myapp.abc}")
- private String fastqPath;
-
- @PostConstruct
- public void test() {
- System.out.println(fastqPath);
- }
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。