This project is read-only.

Problem with apostrophe

Dec 9, 2009 at 11:38 AM
Edited Dec 11, 2009 at 7:03 AM

 

 

Welcome,
I have a little problem with displaying in the cells. Well, when in Excel, manually enters the apostrophe ( ' ) in a cell at the beginning of any sequence of characters and moved to another cell apostrophe became invisible. When I create a document using NPOI and I set a cell value at the beginning of a string, which is the apostrophe is visible when you open the document in the cell.
How to make an apostrophe become invisible - where I make a mistake?
Below code:
using System;
using System.Collections.Generic; 
using System.Text;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;

namespace SetCellValuesInXls
{
    class Program
    {
        static void Main(string[] args)
        {
            InitializeWorkbook();

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

                HSSFRow row = sheet1.CreateRow(1);

                row.CreateCell(1).SetCellValue("' <- The apostrophe should not be visible");

            WriteToFile();
        }

        static HSSFWorkbook hssfworkbook;

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

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

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

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;
        }
    }
}
Welcome,
I have a little problem with displaying in the cells. Well, when in Excel, manually enters the apostrophe ( ' ) in a cell at the beginning of any sequence of characters and moved to another cell apostrophe became invisible. When I create a document using NPOI and I set a cell value at the beginning of a string, which is the apostrophe is visible when you open the document in the cell.
How to make an apostrophe become invisible - where I make a mistake?
Below codeWelcome,
I have a little problem with displaying in the cells. Well, when in Excel, manually enters the apostrophe ( ' ) in a cell at the beginning of any sequence of characters and moved to another cell apostrophe became invisible. When I create a document using NPOI and I set a cell value at the beginning of a string, which is the apostrophe is visible when you open the document in the cell.
How to make an apostrophe become invisible - where I make a mistake?
Below
Dec 10, 2009 at 8:50 PM
You should just need to set the cell type of String explicitly.

On Thu, Dec 10, 2009 at 1:00 PM, Jaden <notifications@codeplex.com> wrote:

From: Jaden

 

 

Welcome,
I have a little problem with displaying in the cells. Well, when in Excel, manually enters the apostrophe ( ' ) in a cell at the beginning of any sequence of characters and moved to another cell apostrophe became invisible. When I create a document using NPOI and I set a cell value at the beginning of a string, which is the apostrophe is visible when you open the document in the cell.
How to make an apostrophe become invisible - where I make a mistake?
Below code:
using System;
using System.Collections.Generic; 
using System.Text;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;

namespace SetCellValuesInXls
{
    class Program
    {
        static void Main(string[] args)
        {
            InitializeWorkbook();

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

                HSSFRow row = sheet1.CreateRow(1);

                row.CreateCell(1).SetCellValue("' <- The apostrophe should not be visible");

            WriteToFile();
        }

        static HSSFWorkbook hssfworkbook;

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

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

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

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;
        }
    }
}
Welcome,
I have a little problem with displaying in the cells. Well, when in Excel, manually enters the apostrophe ( ' ) in a cell at the beginning of any sequence of characters and moved to another cell apostrophe became invisible. When I create a document using NPOI and I set a cell value at the beginning of a string, which is the apostrophe is visible when you open the document in the cell.
How to make an apostrophe become invisible - where I make a mistake?
Below codeWelcome,
I have a little problem with displaying in the cells. Well, when in Excel, manually enters the apostrophe ( ' ) in a cell at the beginning of any sequence of characters and moved to another cell apostrophe became invisible. When I create a document using NPOI and I set a cell value at the beginning of a string, which is the apostrophe is visible when you open the document in the cell.
How to make an apostrophe become invisible - where I make a mistake?
Below code

 

 

Read the full discussion online.

To add a post to this discussion, reply to this email (npoi@discussions.codeplex.com)

To start a new discussion for this project, email npoi@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com




--
slide-o-blog
http://slide-o-blog.blogspot.com/
Dec 11, 2009 at 7:18 AM
Edited Dec 11, 2009 at 7:18 AM

Thank you for you replay.

Set the type of cell in a "string" but has failed. After opening the document is still visible apostrophe.

 The following code:

 

static void Main(string[] args)
        {
            InitializeWorkbook();

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

                HSSFRow row = sheet1.CreateRow(1);

                row.CreateCell(1).SetCellType(3);
                row.GetCell(1).SetCellValue("' <- The apostrophe should not be visible");

            WriteToFile();
        }

Maybe here is an error somewhere?

Dec 11, 2009 at 9:55 AM
If you set the cell type to string, you shouldn't need the aprostrophe at all. It is used to denote strings in Excel, so if the  cell type is already string, you don't need the aprostrophe.

On Fri, Dec 11, 2009 at 12:18 AM, Jaden <notifications@codeplex.com> wrote:

From: Jaden

Thank you for you replay.

Set the type of cell in a "string" but has failed. After opening the document is still visible apostrophe.

 The following code:

 

static void Main(string[] args) { InitializeWorkbook(); HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1"
); HSSFRow row = sheet1.CreateRow(1); row.CreateCell(1).SetCellType(3); row.GetCell(1).SetCellValue("' <- The apostrophe should not be visible"); WriteToFile(); }

Maybe here is an error somewhere?

 

 

 

Read the full discussion online.

To add a post to this discussion, reply to this email (npoi@discussions.codeplex.com)

To start a new discussion for this project, email npoi@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com




--
slide-o-blog
http://slide-o-blog.blogspot.com/
Dec 11, 2009 at 11:26 AM
Unfortunately, when I set the cell type as a string as I showed earlier, and begin a sequence of character "-" (subtraction), the cell behaves as if it was entered in the formula.

slide_o_mix wrote:
If you set the cell type to string, you shouldn't need the aprostrophe at all. It is used to denote strings in Excel, so if the  cell type is already string, you don't need the aprostrophe.

On Fri, Dec 11, 2009 at 12:18 AM, Jaden <notifications@codeplex.com> wrote:

From: Jaden

Thank you for you replay.

Set the type of cell in a "string" but has failed. After opening the document is still visible apostrophe.

 The following code:

 

static void Main(string[] args) { InitializeWorkbook(); HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1"
); HSSFRow row = sheet1.CreateRow(1); row.CreateCell(1).SetCellType(3); row.GetCell(1).SetCellValue("' <- The apostrophe should not be visible"); WriteToFile(); }

Maybe here is an error somewhere?

 

 

 

Read the full discussion online.

To add a post to this discussion, reply to this email (npoi@discussions.codeplex.com)

To start a new discussion for this project, email npoi@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com




--
slide-o-blog
http://slide-o-blog.blogspot.com/

 

Dec 12, 2009 at 2:30 AM

Thank you for your reply, Slide!

Hi Jadon,

It looks you have 2 issues in your post. First is how to make the apostrophe invisible. The second is how to make the cell as a string when there is a hypen at the begining of a cell.

For the first issue, since you don't need to display the apostrophe, why do you put it at the begining. In Excel, the apostrophe at the begining while force the cell to display as a string and the apostrophe is invisible because it works like a command. The correct way here is to set the cell to string. Moreover, you don't need the apostrophe anymore in your cell value. So Slide is correct!

For the second issue, it's also a feature of Excel. When you input a hypen manually to the cell, it will force the cell to become a formula. However, it looks I cannot reproduce your issue with the following code:

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue("-This is a Sample");

The cell will still display as "-This is a Sample".

Let me know what's your case.

Dec 14, 2009 at 9:53 AM
tonyqus wrote:

Thank you for your reply, Slide!

Hi Jadon,

It looks you have 2 issues in your post. First is how to make the apostrophe invisible. The second is how to make the cell as a string when there is a hypen at the begining of a cell.

For the first issue, since you don't need to display the apostrophe, why do you put it at the begining. In Excel, the apostrophe at the begining while force the cell to display as a string and the apostrophe is invisible because it works like a command. The correct way here is to set the cell to string. Moreover, you don't need the apostrophe anymore in your cell value. So Slide is correct!

For the second issue, it's also a feature of Excel. When you input a hypen manually to the cell, it will force the cell to become a formula. However, it looks I cannot reproduce your issue with the following code:

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue("-This is a Sample");

The cell will still display as "-This is a Sample".

Let me know what's your case.

 

Thanks for the reply.

As the first of the issues or "How to make an apostrophe is not visible." According to what I wrote Slide I set the
cell type to a string using the following code:

sheet1.CreateRow (0). CreateCell (0). SetCellType (3);
 
However, this type of cell does not behave exactly like a cell with an apostrophe at the beginning. So when inserted
at the beginning "-" it behaves as if Excel contains the formula. For this reason I care so that an apostrophe at the
beginning he was invisible. Or perhaps I simply be something in the Excel?

 

Dec 28, 2009 at 4:32 PM
Edited Dec 28, 2009 at 4:48 PM

I had the same problem. I was trying to create a worksheet from scratch, which is then populated with data retrieved from an Oracle database. This worksheet is uploaded to a website where it's imported into SQL Server tables. The import process is very picky about the column titles; i.e. presence or absence of leading apostrophes. To make matters even more challenging, there was no consistency regarding which column titles required the apostrophes and which ones didn't. To solve the problem, I saved the upload file, with the correct column titles, but no data. Then when the extract from Oracle is performed, I open the file, populate the worksheets and save it under a different name, using the date/time stamp in the name, so I don't overwrite the "template".

string templateFile = "UploadTemplate.xls";

using (FileStream fs = new FileStream(templateFile, FileMode.Open, FileAccess.Read))
{
    this.wb = new HSSFWorkbook(fs);

    //
    // code to populate the worksheets goes here
    //

    // determine name of file to save workbook as
    string outputFile = string.Format("UploadTemplate{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));

    using (FileStream fs = new FileStream(outputFile, FileMode.Create))
    {
        // save the changes 
        this.wb.Write(fs);
    }

}

Perhaps you can adopt a similar approach?

Hope that helps!