Software Programming: Excel Code

Wednesday 28 September 2022

How to upload password Protected Excel Using java

In the previous post, we saw how some columns were made password protected, now we will see how to upload the created file.

First of all we make brows form.

Uploading Excel

NPOIFSFileSystem class is inside the "org.apache.poi.poifs.filesystem.NPOIFSFileSystem" package Biff8EncryptionKey class is inside the "org.apache.poi.hssf.record.crypto.Biff8EncryptionKey" package

Import Jar Name is poi-3.17.jar and call Some class

import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;

import org.apache.poi.ss.usermodel.DataFormatter;

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.xssf.usermodel.XSSFWorkbook;

Java controlling Method is uploadFile:

@RequestMapping(value = "/uploadFile", method = RequestMethod.POST)
	public ModelAndView uploadFile(
	@ModelAttribute("SlrUploadxlsFromBean") SlrUploadxlsFromBean mvcJavaBeanObj) throws Exception {
	
                CommonsMultipartFile filePath = mvcJavaBeanObj.getFilePath();
				mvcJavaBeanObj.setFileName(filePath.getOriginalFilename());
				String FILE_PATH = contextPath.getRealPath("/WEB-INF/downloads/" + filePath.getOriginalFilename());
				byte[] bytes = filePath.getBytes();
				FileCopyUtils.copy(bytes, new File(FILE_PATH));
				String FILE_PATH2 = contextPath.getRealPath("/WEB-INF/downloads/" + mvcJavaBeanObj.getFileName());
				File fiobj = new File(FILE_PATH2);
				NPOIFSFileSystem fileSystem = new NPOIFSFileSystem(fiobj, true);
				Biff8EncryptionKey.setCurrentUserPassword("a143mk");
				Workbook crdeitdebitbook = new HSSFWorkbook(fileSystem);
				String name = crdeitdebitbook.getSheetAt(0).getSheetName();
				Sheet mySheet = crdeitdebitbook.getSheet(name);
				List<Row> rows = Lists.newArrayList(mySheet.rowIterator());
				final DataFormatter df = new DataFormatter();
				Iterator<Row> rowIterator = rows.iterator();
				System.out.println("Password_Protected_Column_1 |Password_Protected_Column_2 | EDIT_ABLE_COLUMN");
				while (rowIterator.hasNext()) {
					Row row = rowIterator.next();
					if (rowRounter == 0) {
						rowRounter++;
					} else {
					     String colum_obj1 = row.getCell(0).toString().trim();
						String colum_obj2 = row.getCell(1).toString().trim();
						String colum_obj3 = row.getCell(2).toString().trim();
					System.out.println(colum_obj1+" | "+colum_obj2+" | "+colum_obj3);
					}
					}
					
					}

Output:

Thursday 22 September 2022

How to create password Protected Excel for some Column Label in java

September 22, 2022 Excel Code , java , Spring MVC 1 comment :

Introduction
How to password protect some columns of Excel using java and editable some columns inside an excel sheet.
Spring MVC comes with AbstractJExcelView class to export data to Excel file via JExcelAPI library. In this tutorial, it show the use of AbstractJExcelView class in Spring MVC application to export data to Excel file for download

Write the jasper-view.xml configuration.

Import Jar Name jxl-2.6.12.jar and spring-webmvc-4.3.7.RELEASE.jar and call Some classes

package dms.a143mk.excel;
import java.util.ArrayList;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.view.document.AbstractJExcelView;
import antlr.Utils;
import jxl.CellView;
import jxl.Sheet;
import jxl.SheetSettings;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class PasswordProteExcelDemo extends AbstractJExcelView {
	@Override
	protected void buildExcelDocument(WritableWorkbook workbook, HttpServletRequest request, 
	HttpServletResponse response) throws Exception {
          
		  Map<Integer, ArrayList<String>> viewCustomer = new HashMap<Integer, ArrayList<String>>();
			ArrayList<String> recvReportList = new ArrayList<String>();
				recvReportList.add("a143mk");
				recvReportList.add("Programming");
				recvReportList.add("");
				viewCustomer.put(1, recvReportList);
		
		    WritableSheet sheet = null;
		    response.setHeader("content-disposition", "attachment; filename=FineName.CSV");
			sheet = workbook.createSheet("Excel_View", 0);
			sheet.getSettings().setPassword("a143mk");
			sheet.getSettings().setProtected(true);
			sheet.setColumnView(0, 30);
			sheet.setColumnView(1, 30);
			sheet.setColumnView(2, 30);
			
			
			WritableCellFormat readonly = new WritableCellFormat();
			readonly.setBackground(Colour.GRAY_25);
			readonly.setLocked(true);
			
			
			WritableCellFormat cellNumberFor = new WritableCellFormat(NumberFormats.INTEGER);
			cellNumberFor.setBackground(Colour.GRAY_25);
			cellNumberFor.setLocked(true);
		    
			WritableCellFormat unlockedresion = new WritableCellFormat();
			unlockedresion.setLocked(false);
			WritableCellFormat untpCell = new WritableCellFormat(NumberFormats.INTEGER);
			untpCell.setLocked(false);
			
			if(!viewCustomer.isEmpty()){
				sheet.addCell(new Label(0, 0, "Password_Protected_Column_1",readonly));	
				sheet.addCell(new Label(1, 0, "Password_Protected_Column_2",readonly));
			    sheet.addCell(new Label(3, 0, "EDIT_ABLE_COLUMN",unlockedresion));
            	int rowCount = 1;
				for (Map.Entry<Integer, ArrayList<String>> entry : viewCustomer.entrySet()) {
					
					sheet.addCell(new Label(0, rowCount,  entry.getValue().get(0),readonly));
					sheet.addCell(new Label(1, rowCount,  entry.getValue().get(1),readonly));
					sheet.addCell(new Label(2, rowCount,  entry.getValue().get(2),unlockedresion));
						
					rowCount++;
				}
			}
		
	}

}

Sunday 29 May 2016

Excel File Creation Code

May 29, 2016 Excel Code , java

This tutorial explains how to generate Excel files in Java using poi jar file. Download poi jar

import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 *
 * @author manojkumar
 */
public class xlsfile {
    public static void main(String[] args) {
        
        try {
            HSSFWorkbook wb=new HSSFWorkbook();
            HSSFSheet sheet=wb.createSheet("Student Information");
            HSSFRow rowhed=sheet.createRow((short)0);
            rowhed.createCell((short)0).setCellValue("Full Name");
            rowhed.createCell((short)1).setCellValue("Full Address");
            rowhed.createCell((short)2).setCellValue("Mobil No.");
            HSSFRow row=sheet.createRow((short)1);
            row.createCell((short)0).setCellValue("Manoj");
             row.createCell((short)1).setCellValue("Harpalpur");
              row.createCell((short)2).setCellValue("81200638");
              FileOutputStream filepath=new FileOutputStream("F://a.xls");
              wb.write(filepath);
              System.err.println("Done");
              filepath.close();
        } catch (Exception e) {
            System.err.println(e);
        }
    }   
}

Subscribe to: Posts ( Atom )

Wednesday 28 September 2022

How to upload password Protected Excel Using java

Thursday 22 September 2022

How to create password Protected Excel for some Column Label in java

Sunday 29 May 2016

Excel File Creation Code

OUR TECH-PROJECTS

DOWNLOAD ANY ONLINE VIDEOS

LABELS

Development Accessories

POPULAR POSTS