Java Common Tools class Excel operation class and the dependency package download _java

Source: Internet
Author: User
Tags first row

Dependency Pack Download:http://xiazai.jb51.net/201407/tools/java-excel-dependency (jb51.net). rar

Excel Tool class Excelutil.java source code:

Package com.itjh.javaUtil;
Import Java.io.File;
Import Java.io.FileInputStream;
Import java.io.FileNotFoundException;
Import Java.io.FileOutputStream;
Import java.io.IOException;
Import Java.io.OutputStream;
Import Java.text.DecimalFormat;
Import java.util.LinkedList;

Import java.util.List;

Import Javax.servlet.http.HttpServletResponse;
Import Org.apache.poi.hssf.usermodel.HSSFCell;
Import org.apache.poi.hssf.usermodel.HSSFRichTextString;
Import Org.apache.poi.hssf.usermodel.HSSFRow;
Import Org.apache.poi.hssf.usermodel.HSSFSheet;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
Import Org.apache.poi.ss.usermodel.Cell;
Import Org.apache.poi.ss.usermodel.DateUtil;
Import Org.apache.poi.ss.usermodel.Row;
Import Org.apache.poi.ss.usermodel.Sheet;
Import Org.apache.poi.ss.usermodel.Workbook;

Import Org.apache.poi.ss.usermodel.WorkbookFactory; /** * Encapsulates operations on Excel, including local read-write Excel and output excel in streaming, and supports Office 2007. <br/> * Dependent on POi-3.9-20121203.jar,poi-ooxml-3.9-20121203.jar,poi-ooxml-schemas-3.9-* 20121203.jar,dom4j-1.6.1.jar<br/> *  Argument constructor parameter is full path to Excel <br/> * * @author Song Lijun * @date July 03, 2014/public class Excelutil {//Excel file path private

	String path = "";
	When you write to Excel, the column widths are automatically extended to fit the content.

	Private Boolean autocolumnwidth = false; /** * Parameterless constructor default/public Excelutil () {}/** * Parameter constructor * * @param path * Excel path/public excelutil (String Path)
	{This.path = path;
	 /** * Reads the values of all cells on a workbook.
	 * * @param sheetorder * Workbook number, starting from 0.
	 * @return List<object[]> The values of all cells.
	 * @throws ioexception * Load Excel file IO exception.
	 * @throws FileNotFoundException * Excel file did not find an exception. * @throws invalidformatexception * @author Song Lijun * @date July 03, 2014/Public list<object[]> read (int sheetord ER) throws FileNotFoundException, IOException, invalidformatexception {fileinputstream fis = new FileInputStream (PAT
		h); Workbook workbook = Workbookfactory.creaTe (FIS);
		if (FIS!= null) {fis.close ();
		} Sheet Sheet = Workbook.getsheetat (Sheetorder);
		Used to record Excel values list<object[]> valuelist = new linkedlist<object[]> ();
		Iterate through each row and column.
			for (row Row:sheet) {//each row object[] rowobject = null; for (cell cell:row) {//Cell.getcelltype is the type switch (Cell.getcelltype ()) {case Cell.cell_typ that gets the value stored in the cell. E_boolean://Get BOOLEAN object Method Rowobject = Collectionutil.addobjecttoarray (Rowobject, Cell.getbooleancellva
					Lue ());
				Break Case Cell.cell_type_numeric://First See if it is a date format if (dateutil.iscelldateformatted (Cell)) {//Read date format rowobj
					ECT = Collectionutil.addobjecttoarray (Rowobject, Cell.getdatecellvalue ());
						else {DecimalFormat df = new DecimalFormat ();
						The value of the cell, replaced by String value = Df.format (Cell.getnumericcellvalue ()). Replace (",", ""); Read the number Rowobject = Collectionutil.addobjecttoarray (rowobject, value);
					} break; Case Cell.cell_type_formula://Read Formula Rowobject = Collectionutil.addobjecttoarray (Rowobject, CELL.GETCELLF
					Ormula ());
				Break Case cell.cell_type_string://Read STRING Rowobject = Collectionutil.addobjecttoarray (Rowobject, Cell. Getr
					Ichstringcellvalue (). toString ());
				Break
			}//Add this line to the list.
		Valuelist.add (Rowobject);
	return valuelist;
	 /** * Reads the value of a cell on a workbook.
	 * * @param sheetorder * Workbook number, starting from 0.
	 * @param colum * Number of columns starting from 1 * @param row * Number of rows starting from 1 * @return cell value.
	 * @throws Exception * Load Excel exception. * @author Song Lijun * @date July 03, 2014 * * public String read (int sheetorder, int colum, int row) throws Exception {File
		InputStream fis = new FileInputStream (path);
		Workbook workbook = workbookfactory.create (FIS);
		if (FIS!= null) {fis.close ();
		} Sheet Sheet = Workbook.getsheetat (Sheetorder);
		Row rows = Sheet.getrow (row-1); Cell cell = Rows.getcell (colum-1);
		String content = Cell.getstringcellvalue ();
	return content;
	 /** * Writes values in the specified workbook, row, and column.
	 * * @param sheetorder * Workbook serial number, based on 0.
	 * @param colum * column is based on the 1 * @param row * Line based on 1 * @param content * What will be written.
	 * @throws Exception * Save exception after writing. * @author Song Lijun * @date July 03, 2014/public void write (int sheetorder, int colum, int row, String content) throws
		Exception {FileInputStream fis = new FileInputStream (path);
		Workbook workbook = workbookfactory.create (FIS);
		if (FIS!= null) {fis.close ();
		} Sheet Sheet = Workbook.getsheetat (Sheetorder);
		Row rows = Sheet.createrow (row-1);
		Cell cell = Rows.createcell (colum-1);
		Cell.setcellvalue (content);
		FileOutputStream fileout = new FileOutputStream (path);
		Workbook.write (fileout);

	Fileout.close ();
	 /** * Gets the ordinal number of the last record in a workspace, equivalent to the number of rows of data in the workbook.
	 * * @param sheetorder * Work area serial number * @return int serial number.
	 * @throws IOException * Load Excel exceptions with Excel paths. * @throws InvalidformatexceptiOn * @author Song Lijun * @date July 03, 2014/public int getsheetlastrownum (int sheetorder) throws IOException, Invali
		dformatexception {FileInputStream fis = new FileInputStream (path);
		Workbook workbook = workbookfactory.create (FIS);
		if (FIS!= null) {fis.close ();
		} Sheet Sheet = Workbook.getsheetat (Sheetorder);
	return Sheet.getlastrownum (); /** * Generates a content-containing Excel on disk, Path property * * @param sheetname * Exported sheet name * @param fieldName * Column array group * @pa RAM Data * @throws IOException * @author Song Lijun * @date July 03, 2014/public void Makeexcel (String sheetn Ame, string[] fieldName, list<object[]> data) throws IOException {//Generate work thin in memory hssfworkbook workbook = Makew
		Orkbook (SheetName, fieldName, data);
		Intercepting folder path String FilePath = path.substring (0, Path.lastindexof ("\"));
		If the path does not exist, create the path File File = new file (FilePath);
		System.out.println (path+ "-----------" +file.exists ());
		if (!file.exists ()) file.mkdirs (); FiLeoutputstream fileout = new FileOutputStream (path);
		Workbook.write (fileout);
	Fileout.close ();
	 /** * Exports excel in the output stream. * * @param excelname * Exported Excel names include extension * @param sheetname * Exported sheet name * @param fieldName * Column array Group * @ param Data Group * @param response * Response * @throws IOException * Conversion Stream IO Error * @author Song Lijun * @date July 03, 2014 */public void Makestreamexcel (string excelname, String sheetname, string[] FieldName, list<object[]& Gt
		Data, HttpServletResponse response) throws IOException {outputstream os = null; Response.reset (); Empty output stream OS = Response.getoutputstream (); Get output stream Response.setheader ("content-disposition", "attachment; Filename= "+ New String (Excelname.getbytes ()," iso-8859-1 ")); Set Output file header Response.setcontenttype ("Application/msexcel");
		Define output type//generate work in memory thin hssfworkbook workbook = Makeworkbook (SheetName, fieldName, data);
		Os.flush ();
	Workbook.write (OS);
/** * Generates the workbook object to memory according to the criteria.	 * * @param sheetname * Sheet Object name * @param fieldName * First column name * @param data * @return Hssfworkbook * @author Song Lijun * @date July 03, 2014 * * Private hssfworkbook Makeworkbook (String sheetname, string[] FieldName, Li
		St<object[]> data) {//used to record the maximum column width, automatically adjust column width.

		Integer collength[] = new Integer[fieldname.length];
		Produce work Thin object Hssfworkbook workbook = new Hssfworkbook ();
		Generate Worksheet Object Hssfsheet sheet = Workbook.createsheet ();
		For the worksheet to support Chinese, set the character set to Utf_16 workbook.setsheetname (0, SheetName);
		Produces a row hssfrow row = sheet.createrow (0);
		Generating cell hssfcell cells;
			Write the names for each field for (int i = 0; i < fieldname.length i++) {//Cell cell = Row.createcell that creates the first row of individual field names ((short) i);
			Sets the contents of the cell to be a string type Cell.setcelltype (hssfcell.cell_type_string);
			To be able to enter Chinese in the cell, set the character set to Utf_16//cell.setencoding (HSSFCELL.ENCODING_UTF_16);
			Assign value to cell contents Cell.setcellvalue (New Hssfrichtextstring (fieldname[i)); Initialize column width Collength[i] = fieldname[i].geTbytes (). length;
		}//Temporary cell contents String tempcellcontent = "";
			Write each record, each record corresponds to one row in the Excel table for (int i = 0; i < data.size (); i++) {object[] tmp = Data.get (i);
			Generates row = Sheet.createrow (i + 1);
				for (int j = 0; J < Tmp.length J + +) {cell = Row.createcell (short) j);
				Sets the cell character type STRING cell.setcelltype (hssfcell.cell_type_string); Tempcellcontent = (Tmp[j] = = null)?
				"": tmp[j].tostring ();

				Cell.setcellvalue (New hssfrichtextstring (tempcellcontent));
				If the column width is automatically adjusted.
						if (autocolumnwidth) {if (J >= collength.length) {////) The number of header columns is less than the number of data columns.
					Collength = Collectionutil.addobjecttoarray (Collength, Tempcellcontent.getbytes (). length);
						else {//If the width of this content is greater than the previous maximum, follow this setting width.
						if (Collength[j] < Tempcellcontent.getbytes (). Length) {Collength[j] = tempcellcontent.getbytes (). length;
		The column width is automatically adjusted in the}}}//.
			if (autocolumnwidth) {//Adjusts the maximum width corresponding to this column of text. for (int i = 0; i < fieldName.length;
			i++) {sheet.setcolumnwidth (i, collength[i] * 2 * 256);
	} return workbook;
	 /** * Feature: Sets whether the column width is automatically extended to fit content when writing to Excel, and defaults to false. * * @author Song Lijun * @date July 03, 2014 * @param autocolumnwidth * True or false */public void Setautocolumnwidth (Boolean autocolumnwidth)
	{this.autocolumnwidth = Autocolumnwidth; }
}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.