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

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; }
}