Archive for category Excel

“Excel found unreadable content” error

I was exporting an Excel document using OpenXml, and added a new cell to be added to the export:

Cell newCell = new Cell() { CellReference = “A” + rowIndex };
r.InsertAt(newCell, cellIndex);
newCell.CellValue = new CellValue(item[i].Description);
newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
cellIndex++;

After making this change, I received the message “Excel found unreadable content in ‘My Excel file.xlsx’. Do you want to recover the contents of the workbook?

Error

The problem was that I was attempting to format the cell as a number when it was actually a string. I modified the line that set the cell DataType, and it was fine:

newCell.DataType = new EnumValue<CellValues>(CellValues.String);

Leave a comment

Changing the formatting of an Excel cell with NPOI

We use NPOI to generate Excel files. The old format for negative numbers was to have parentheses around them, but a new requirement was to instead use a minus sign.

The way we handle cell formatting is a custom function, GetCellStyle, that returns the new DataFormat object to adjust the way the field appears. Here is the code to do that:

HSSFWorkbook workbook;
private GetCellStyle(string cellStyle)
{
 CellStyle cs = workbook.CreateCellStyle();
 cs.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);($#,##0.00)");
}

And a cell style is set like so:

CellStyle currency = GetCellStyle("currency");
Row r7 = WS.GetRow(7);
r7.CreateCell(0).SetCellValue(dSomeCurrencyValue);
r7.GetCell(0).CellStyle = currency ;

So the current format surrounds negative numbers with parentheses. It was a small change to remove the parentheses and replace it with a minus sign:

cs.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);-$#,##0.00_)");

Leave a comment

Merging Excel cells with NPOI

I often get messages like this:

“Scott, you are the best, most awesome guy in the whole world. Every day, I think of only you and your cool coding site, and eagerly await each and every post. How did you get so awesome?”

So of course, I wanted to put this into an Excel sheet. Usually, I use Microsoft Excel Object Library for my Excel needs, but in this case, I’m using NPOI because the client wants to use it to say how great I am. (This happens a lot.)

HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet WS = hssfworkbook.CreateSheet("Sheet1");
Row row1 = WS.CreateRow(0);
Cell r1c1 = row1.CreateCell(0);
r1c1.SetCellValue("Scott, you are the best, most awesome guy in the whole world. Every day, I think of only you and your cool coding site, and eagerly await each and every post. How did you get so awesome?");
r1c1.CellStyle = red10;
r1c1.CellStyle.WrapText = true;
r1c1.Row.Height = 800;
NPOI.SS.Util.CellRangeAddress cra = new NPOI.SS.Util.CellRangeAddress(0,0,0,5);
WS.AddMergedRegion(cra);

OK, a little explanation of what just happened.

The first four rows created the sheet, row, and cell to hold my ego-boosting string. The next four rows set the value and style the cell. An item of interest is row 8, which reads:

r1c1.Row.Height = 800;

It doesn’t seem that interesting, but when a cell is merged using the NPOI code, it doesn’t automatically resize the row to show the string, so that has to be done manually. Otherwise, some of the cell text could be hidden.

The last two lines create a cell range. The first of these holds the CellRangeAddress function, which takes four parameters. In order, they are the row to start the range, the row to end the range, the column to start the range, and the column to end the range. This sets the area of cells that you want to merge into one cell. And then the last line  adds the range to the worksheet.

So there we have it, merging cells with NPOI.

1 Comment

Deleting Excel worksheets with VB.NET and LINQ

I never claim that the way I do it is the best way…

I created code to insert a worksheet into Excel for each user in the query. But the focus of this post is deleting those default sheets that Excel starts with, Sheet1, Sheet2, and Sheet3. When I tried to delete them at the beginning of my code, I was graciously given this friendly COMException:

A workbook must contain at least one visible worksheet.

No worries, I’ll just do it at the end of the process. I came up with two ways. First, the easy, so un-cool way to do it: Just count backwards from the worksheet count:

Dim bkWorkBook As Excel.Workbook = DirectCast(oXL.Workbooks.Add(), Excel.Workbook)
Dim shWorkSheet As Excel.Worksheet = DirectCast(bkWorkBook.Sheets("Sheet1"), Excel.Worksheet)
'Code to populate the worksheets
Dim Counter As Integer = bkWorkBook.Sheets.Count
CType(bkWorkBook.Sheets(Counter), Excel.Worksheet).Delete()
CType(bkWorkBook.Sheets(Counter - 1), Excel.Worksheet).Delete()
CType(bkWorkBook.Sheets(Counter - 2), Excel.Worksheet).Delete()

Oh, that’s too easy, though. Isn’t there some way I could use some cool LINQ extension method to do this? You betcha. This is the round-about way I came up with using OrderByDescending:

Dim SheetName As String = String.Empty
Dim SheetIds As New List(Of Integer)
For i As Integer = 1 To bkWorkBook.Sheets.Count
            SheetName = CType(bkWorkBook.Sheets(i), Excel.Worksheet).Name
            If SheetName = "Sheet1" OrElse SheetName = "Sheet2" OrElse SheetName = "Sheet3" Then
                SheetIds.Add(i)
            End If
Next
For Each Counter As Integer In SheetIds.OrderByDescending(Function(i As Integer) i)
            CType(bkWorkBook.Sheets(Counter), Excel.Worksheet).Delete()
Next

By the way, I used the first, much simpler, method.

Leave a comment