This blog contains steps to copy Formatting & Style from one Excel sheet to another sheet. Also, shows how to identify the Cell Type.
To implement formatting in the excel sheet while writing data, please visit the below link:
Copy Formatting & Style
- Below are the requirements:
- inputfile.xlsx file
- Sheet having list of few Electronic items with below columns
- S.No | Numeric Data
- Item Name | String Data
- Item Code | Numeric Data
- Quantity | Numeric Data
- Formatting of Header
- Background color Tan
- Text color White
- All fields are bold
- Formatting of Item Name column
- All data is in Italic font.
- Formatting of the Quantity column
- Values less than 100 is of Red Color and in Italic font
- Other values are of Green color
- Sheet having list of few Electronic items with below columns
- inputfile.xlsx file
Code Example
- Steps to copy the data and cell style from one excel sheet to another.
- Create references for Output File
- Create references for Input File
- Navigate throughout all the cells
- Store cell data in a variable
- Identify the Cell Type like Numeric, String etc.
- Identify the Cell Style for each cell
- Copy data from an input file to Output File
- Create a new cell style with reference to the input cell
- Create a new cell type with reference to the input cell
- Write data, cell style, cell type to the output file
- Save data to the Output file.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author ashok.kumar
*/
public class CopyFormatting {
// Creating Output file references.
static File outputFile=new File("./result/testfile_Result.xlsx");
static FileOutputStream fos;
static XSSFWorkbook outputWB;
static XSSFSheet outputSheet;
public static void main(String[] args) throws IOException {
// Creating Input File References.
File inputFile=new File("./input/testfile.xlsx");
FileInputStream fis=new FileInputStream(inputFile);
XSSFWorkbook inputWB=new XSSFWorkbook(fis);
XSSFSheet inputSheet=inputWB.getSheet("Sheet1");
// Call static method to create Result Excel file to copy the content.
createNewFile();
// Variable to refer current Row number in the output file.
int currentRowNum=0;
// Reading the data with formatting from input sheet.
// Writing the data with formatting to Output Sheet.
for (Row row : inputSheet)
{
Iterator<Cell> cellIterator=row.cellIterator();
//Reference to current Cell number into Output File.
int currentCellNum=0;
//Insert new row in the Output File.
createNewRow(currentRowNum);
// Navigating all the cells in a row.
while(cellIterator.hasNext())
{
Cell currentCell=cellIterator.next();
// Get Data for current cell.
String cellData=currentCell.toString();
System.out.println(cellData);
// Get Style for current cell.
XSSFCellStyle cellStyle=(XSSFCellStyle) currentCell.getCellStyle();
// Get Cell Type for current cell like String, Numeric etc.
CellType cellType=currentCell.getCellTypeEnum();
// Call static method to write data to Output Sheet.
writeData(currentRowNum,currentCellNum,cellStyle,cellData,cellType);
currentCellNum++;
}
currentRowNum++;
}
// Call static method to save all the data in the output file.
saveFile();
}
public static void createNewFile() throws FileNotFoundException
{
fos=new FileOutputStream(outputFile);
outputWB=new XSSFWorkbook();
outputSheet=outputWB.createSheet("Sheet1");
}
public static void createNewRow(int newRowNum)
{
outputSheet.createRow(newRowNum);
}
@SuppressWarnings("null")
public static void writeData(int currentRowNum, int currentCellNum, XSSFCellStyle cellStyle, String cellData, CellType cellType)
{
// Locating current row in the Output file
XSSFRow row=outputSheet.getRow(currentRowNum);
XSSFCell cell = null;
// Verifying the input cell type and write data in the same type
if(cellType==cellType.STRING)
{
cell=row.createCell(currentCellNum,CellType.STRING);
cell.setCellValue(cellData);
}
else if(cellType==cellType.NUMERIC)
{
cell=row.createCell(currentCellNum, CellType.NUMERIC);
cell.setCellValue(cellData);
System.out.println(cellData+" Numeric");
}
else
{
System.out.println("Cell Type not found...");
}
// Creating New Cell Style for Output File.
XSSFCellStyle newStyle=outputWB.createCellStyle();
// copy existing style for individual cell into Output File.
newStyle.cloneStyleFrom(cellStyle);
cell.setCellStyle(newStyle);
}
public static void saveFile() throws IOException
{
outputWB.write(fos);
System.out.println("File saved successfully...");
}
}Java Programs:
- Different ways to Reverse a String in Java.
- Copy formatting & style of cells from one sheet to another.
- Getting IP address and Hostname using InetAddress Class.
- User inputs via Command Prompt using arguments of main() method of a class.
- Program for List and ArrayList in Java.
- Useful methods and implementation under Scanner Class.
- Swapping two variable values without using any third variable.
- Difference between int x= 10 and y=010 in Java.
- Parameterized Constructors v/s Setter and Getter function in JAVA.
- Override a Static Method.
- TestNG – 1 || Introduction and benefits of TestNG Framework.
- TestNG – 2 || Installation process and a sample program of TestNG.
- TestNG – 3 || Create and execute multiple Test Cases.
- TestNG – 4 || Let’s understand @Test Annotation and attributes.
- TestNG – 5 || Understand Assertion in TestNG.
- TestNG – 6 || Use of @BeforeMethod and @AfterMethod.
- TestNG – 7 || Use of @BeforeClass and @AfterClass.
- TestNG – 8 || Creation and execution of Test Suites.
- TestNG – 9 || Let’s move deep into the Test Suites.
- TestNG – 10 || Use @BeforeTest and @AfterTest Annotations.
- TestNG – 11 || Groups attribute with @Test Annotation.
- TestNG – 12 || Use of @BeforeGroups & @AfterGroups.
- TestNG – 13 || Use of @BeforeSuite & @AfterSuite.
- TestNG – 14 || DataProvider annotation & attribute.
- TestNG – 15 || DataProvider with parameters.
- TestNG – 16 || Access data from Excel sheet using DataProvider.
- TestNG – 17 || Passing multiple Parameters in testng xml.
- TestNG – 18 || Multiple Browser and Parallel Execution in TestNG.
- TestNG -19 || Concept of Parallel Execution.
- TestNG – 20 || Run TestNG Program using main() method.
- Computer Basics -1 || Introduction and Structure of Computer.
- Computer Basics -2 || Types of Computers and Usage.
- Computer Basics -3 || What are the different types of Software?
- Computer Basics -4 || Importance of Operating System(OS).
- Computer Basics -5 || Understanding of Number System.
- Computer Basics -6 || Understanding MS-DOS Commands.
- Computer Basics -7 || Important Features of MS-Word.
- Computer Basics -8 || Let’s learn the usage of MS-Excel.
- Computer Basics -9 || Understand and Implement Data Validation in Excel.
- Computer Basics -10 || How to apply Filter in a data set in Excel?
- Computer Basics -11 || Using Charts in place of Data Tables in Excel.
- Computer Basics -12 || Advantages of PivotCharts over Simple Charts in Excel.
- Computer Basics -13 || Creating pivot charts/tables in Excel.
- Abbreviations to Full-Forms in Computer Basics.
- Basic Java – 1 || Understand Java before start learning JAVA.
- Basic Java – 2 || Variables and Data Types used in JAVA.
- Basic Java – 3 || Understanding Class, Objects, Methods in Java.
- Basic Java – 4 || More on methods(Return Type and Parameters)
- Basic Java – 5 || Methods- Call by Value and Call by Reference in Java.
- Basic Java – 6 || Understanding of Constructor and Destructor in JAVA.
- Basic Java – 7 || Static Variables and Methods.
- Basic Java – 8 || Lets learn about Arrays in Java.
- Basic Java – 9 || Performing multiple operations using Java Operators.
- Basic Java – 10 || Conditions (If and Switch) in JAVA.
- Basic Java – 11 || for and for-each in Java. (Loops Part-1)
- Basic Java – 12 || Alternate looping concepts while and do-while. (Loops Part-2)
- Basic Java – 13 || Decimal values v/s Octal base(8) values in JAVA.
- Basic Java – 14 || Learn about String literals in Java.
- Basic Java – 15 || Runtime User Input using Scanner Class (Part-1).
- Basic Java – 16 || Runtime User Input using BufferedReader Class (Part-2).
- Basic Java – 17 || Runtime User Input using Console Class (Part-3).
- Basic Java – 18 || Difference between break and continue keywords.
- Basic Java – 19 || Sending Email using Java (Part-1).
- Basic Java – 20 || Sending Email with attachment using Java (Part-2).
- Basic Java – 21 || Stack memory and Heap memory in Java.
- Basic Java – 22 || Let’s learn more about String.
- Basic Java – 23 || String, StringBuffer & StringBuilder in Java.
- Basic Java – 24 || Exception Handling using Try Catch.
- File Handling | Reading data from word document(.doc or .docx) in JAVA.
- File Handling | Reading data from Excel files (.xls or .xlsx) using JAVA.
- File Handling | Writing data into an Excel(.XLSX or .XLS) File.
- File Handling | Implement formatting in Excel using Java.
- File Handling | Copy existing data from one workbook to another workbook in Java.
- File Handling | Reading data from PDF file using JAVA.
- File Handling || Traverse folders and subfolders in Java.
- File Handling || Reading and Writing data from a text file.
- File Handling || Multiple file creation using template based input data.
- Update cell values in the same Excel File. || File Handling
- Framework || Simple example of Key Driven Framework using excel sheet in Selenium(JAVA).
- QnA || How to use Constructors in Abstract class?
- QnA | Difference between Integer and int keywords.
- QnA | Can main method be overloaded?
- QnA | How do I reverse a String/Sentence in Java?
- QnA | Perform Multiplication and Division without * or / or % operators.
- QnA | How do I get the default value of data type?
- QnA | How to split String if it contains period symbol (.) in between?
- Selenium-12 || Select Class to handle drop-down.
- Selenium-11 || Use of Actions and Action Classes.
- Selenium-10 || Taking Screenshots using Selenium
- Selenium-9 || Understanding WebDriver API.
- Selenium-8 || Implementing Wait(s) in Selenium.
- Selenium-7 || Let’s learn to create complex XPath.
- Selenium-6 || XPath is the best way to locate web elements.
- Selenium-5 || Locating web elements using various type of Locators.
- Selenium-4 || Handling multiple web browsers.
- Selenium-3 || First program using Selenium Web Driver.
- Selenium-2 || Let’s learn Selenium IDE.
- Selenium-1 || Understanding Selenium and Selenium WebDriver.
- JIRA Tutorials-2 || Implement Search and Filter on JIRA Issues.
- JIRA Tutorials-1 || Basic understanding of JIRA.
