Copy Book1 cell to Book2 cell

Nov 5, 2011 at 5:21 AM

Hi

I want to create a program that will get certain values from a cell in a excel book & export those selected cells into a new excel book. Im using Microsoft visual C# 2010. I cant seem to pass the value from book1 to book2.

Also i had to use ISheet & IRow as using HSSFSheet & HSSFRow caused a error of Cannot implicitly convert type 'NPOI.SS.UserModel.ISheet' to 'NPOI.HSSF.UserModel.HSSFSheet'. An explicit conversion exists (are you missing a cast?)

Im not sure how to fix the above.

 

        static HSSFWorkbook hssfworkbook;

static void writeExcel()
{
InitializeWorkbook();

FileStream fs = new FileStream((@"\Documents and Settings\Warren\My Documents\Visual Studio 2010\Projects\ExcelReader\Book1.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook1 = new HSSFWorkbook(fs, true);
ISheet sheet = hssfworkbook1.GetSheet("Sheet1");
IRow dataRow = sheet.GetRow(1); //should be row 2
dataRow.GetCell(1);

ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

//use newlines in cell
IRow row1 = sheet1.CreateRow(0); //1,2,3,4,5 down the page

ICell cell1 = row1.CreateCell(0); //A,B,C,D,E Across the page


cell1.SetCellValue(dataRow); //trying to put dataRow in here doesnt work
WriteToFile();

}

static void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();

////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "WEB Electronics";
hssfworkbook.DocumentSummaryInformation = dsi;

////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Excel Merger";
hssfworkbook.SummaryInformation = si;
}

static void WriteToFile()
{
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(@"test.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}

Dec 2, 2011 at 7:53 PM

In regards to why you get the cast exception, I use NPOI.SS.UserModel.Row and NPOI.SS.UserModel.Sheet to initialize Rows and Sheets, respectively

I have a wrapper that I use that you could do this pretty easily.  Check out my blog at http://oopstruggles.blogspot.com/ - or you can just download the wrapper here: http://www.box.com/s/kbea3pvti84a31i8q389

I'm always inclined to represent Excel data as a System.Data.DataTable, because I find the interface a bit more intuitive, which is why I put a ToDataTable() method in the wrapper.

The code to copy certain data from one workbook to another would look something like this:

ExcelFile file1 = new ExcelFile("ExistingFile.xls");
ExcelFile file2 = new ExcelFile();
file2.CreateFile("NewFile.xls");

DataTable file1AsDt = file1.ToDataTable();

int index = 0;
foreach(DataRow row in file1AsDt.Rows)
{
   ///Logic to decide if data needs
   ///to get copied
   if (....)
   {
       file2.WriteCellValue(index, 0, row[0].ToString());
       file2.WriteCellValue(index, 1, row[1].ToString());
   }

   index++;
}

file2.SaveFile();