How to fit the columns in an excel sheet

Question

I'm writing a list to an excel sheet generated using EPPlus with .xlsx extension. Then using worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns(); method I tried to fit the columns.

This is how I write data

using (ExcelPackage xlPackage = new ExcelPackage(newFile))
      {
        System.Data.DataTable dt = new System.Data.DataTable();
        var ws = xlPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == language.Culture);
        if (ws == null)
         {
          int i = 1, j = 0;
           worksheet = xlPackage.Workbook.Worksheets.Add(language.Culture);
           foreach (ExcelFields fieldValues in UnmatchedFieldList)
           {
                 //code
            }
           else
             {
                int i = 0;
                worksheet = xlPackage.Workbook.Worksheets[language.Culture];
                colCount = worksheet.Dimension.End.Column;
                 rowCount = worksheet.Dimension.End.Row;
                 foreach (ExcelFields fieldValues in UnmatchedFieldList)
                 {
                       worksheet.Cells[rowCount + 1, count + 1].Value = itemName;
                  }
                 worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
                 xlPackage.Save();
             }

I read data as

            string sheetName = language.Culture;
            var excelFile = new ExcelQueryFactory(excelPath);
            IQueryable<Row> excelSheetValues = from workingSheet in excelFile.Worksheet(sheetName) select workingSheet;
            string[] headerRow = excelFile.GetColumnNames(sheetName).ToArray();

At headerRow it is throwing the below error

When I'm trying to read the data from excel it is throwing an exception

External table is not in the expected format

I found out,this is due to the columns are not formatted(width) correctly. When I manually set the columns width by double clicking the cell and run the code it is working fine

So I want to achieve this using code

Could you post all of your code that generates the file and then reads it? — Ernie S, Jul 09 '15 at 13:49
It seems you are tying to write data with EPPlus and read it with Linq2Excel? Very similar to this: http://stackoverflow.com/questions/31165959/how-to-retrieve-column-names-from-a-excel-sheet. The problem is EPPlus does not generate a fully formatted excel file - it creates the raw basic XML inside a renamed zip file (xlsx is just a zip file). Then when you open in excel it finishes it up. I suspect L2E is expecting it fully formatted You best bet is to do everything in EPPlus. What do you need L2E for that you cannot do in EPPlus? — Ernie S, Jul 10 '15 at 12:00
Can't we read the data using Linq2Excel? When I have tried the above answer it is throwing me an error @Ernie — D Rao, Jul 13 '15 at 04:00
It would appear as though you cannot since no one else has chimed in. Like I asked before, what are you trying to do in L2E that you cant with EPPlus? If you have to have L2E then EPPlus may not be the best option. @D Rao — Ernie S, Jul 13 '15 at 12:08
Actually I'm retrieving all the row values and looping through each row and retrieving each cell value. I have a column "ITEMID", if that is null I have to do something else @Ernie — D Rao, Jul 13 '15 at 16:26

Saveendra Ekanayake · Answer 1 · 2015-07-09T07:37:06.953

0

External table is not in the expected format exception is occurred because of exception of connection string so there fore check your connection string with following sample

public static string docPath= @"C:\sourcefolder\myfile.xlsx";
public static string ConnectionString= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + docPath + ";Extended Properties=Excel 12.0;";

or if you use LinqToExcel then check whether you have set setting the DatabaseEngine property like as follows

public string docPath= @"C:\sourcefolder\myfile.xlsx";
var excelFile = new ExcelQueryFactory(docPath);
excelFile.DatabaseEngine = DatabaseEngine.Ace;

edited Jul 09 '15 at 07:37

answered Jul 09 '15 at 07:24

Saveendra Ekanayake

3,153
6
34
44

I'm doing it using LinqToExcel and dont want to use connection strings and it is working fine when the cells are formatted properly @Saveendra Sri – D Rao Jul 09 '15 at 07:32
Check weather database DatabaseEngine property. Look edited answer. – Saveendra Ekanayake Jul 09 '15 at 07:41
`string sheetName = language.Culture; var excelFile = new ExcelQueryFactory(excelPath); IQueryable excelSheetValues = from workingSheet in excelFile.Worksheet(sheetName) select workingSheet;` This is what I'm doing to retrieve data @ Saveendra Sri Ekanayake – D Rao Jul 09 '15 at 08:18
Add DatabaseEngine like this (excelFile.DatabaseEngine = DatabaseEngine.Ace;) – Saveendra Ekanayake Jul 09 '15 at 08:40
Yeah I have tried it, even this didn't resolve the problem – D Rao Jul 09 '15 at 08:43

How to fit the columns in an excel sheet

1 Answers1