This project has moved. For the latest updates, please go here.

regarding the UNREADABLE FILE error

Mar 2, 2012 at 9:17 AM

Hi,

I've been having this error on and off. Sometimes the excel file is working well but sometimes excel says it's corrupted. I've been reading a few days now on the discussion and I can't seem to find the solution. Others would say that to solve it is by adding Response.Clear() or something like that. But,that's not applicable for me. Since I'm developing a windows form application. So I've been studying this error and I have a hunch that this is cause of the actual data im trying to save on the excel file. Please see code below.

 

if (leDS.Tables[0].Rows.Count > 0)
{
                if (curPROJ == "VPI")
                {
                        int cntr = 1;
                        foreach (DataRow myRow in leDS.Tables[0].Rows) 
                        {
                            // the data on colum "documentReference" can be
                            // like this 
                            //I12039007774
                            //I12039007782
                            //I12039007812
                            ws.Cells[cntr + 1, 1].Value = myRow["documentReference"].ToString();
                            cntr = cntr + 1;
                        }
                        
                    }
}

i think excel is trying to read I12039007772 as a column/row reference instead of string. Now I can't tamper the data im trying to insert. Can anyone suggest what could be the possible solution here? also I might be wrong. Please help.

Thanks

Editor
Mar 2, 2012 at 10:57 AM

From FAQ 

http://epplus.codeplex.com/wikipage?title=FAQ&referringTitle=Documentation

I get an error that Excel has found unreadable content when I open my generated document?
Check your Formulas and Numberformats. Formulas and numberformats are not validated by the library so if you enter anything wrong the package will be corrupt. Use the English formula names. Formula parameters are separated by commas (,), string parameters use double quotes (").

worksheet.Cells["A1"].Formula="CONCATENATE(\"string1_\",\"test\")";

Numberformats:
Use culture independent number formats: dot (.) for decimal, comma (thousand). 
The easiest way to check a format or a formula is to create an Excel file --> Do the formatting / Add the formulas --> Save it as XLSX ---> Rename the file *.zip. Extract the file and have a look at the number formats in the \xl\styles.xml file and formulas in \xl\worksheets\sheet#.xml.

------------------------

Do you do any merge in yours cells? bad merge generate this issue too...

Mar 3, 2012 at 7:18 AM

i'm kinda confuse with the 

worksheet.Cells["A1"].Formula="CONCATENATE(\"string1_\",\"test\")";

I dont use any merge. So your saying is I should do it like this

ws.cells[cntr + 1, 1].Formula="CONCATENATE(\" + myRow["documentReference"].ToString() + "\)" ;

sorry im really not that good with excel. Thank you for your suggestion. I've been stuck here for a while now >.< and I can't really try this out since im at home and the codes are not allowed to be taken home. Do you have any suggestions?

Mar 5, 2012 at 5:51 AM

 Hi,

Im kinda desperate here. Here are the possible data that cause the error.

 

    R12037020110
    I12037024200
    C12037047570
    I12037037790
    I12037037845
    I12037037810
    QUALITY ASSURANCE TASK
    I12037056065
    I12037054535
    C12037017126
    I12037055420
    I12037055405
    I12037054505
    QUALITY ASSURANCE TASK
    I12037050310
    I12033013665
    I12037037780
    I12034035845
    I12037048460
    I12037048440
    I12037047635
    I12037016085
    C12037068820
    C12037068805
    QUALITY ASSURACE TASK
    R12038305840
    R12038305850
    R12038305852
    R12038305858
    R12038305682
    R12038305674
    R12038305656
    R12038305660
    R12038305668
    R12038305722
    R12038305708
    R12038305718
    R12038305702
    R12038305748
    R12038305758
    R12038305738
    R12038305728
    R12038305812
    R12038305806
    R12038305822
    R12038305774
    R12038305764
    R12038305784
    R12038305800
    I12037055595
    I12037055410
    I12037054405
    I12037055965
    I12037056110
    I12037056085
    I12037055440
    I12037057780
    C12034030905
    I12034036165
    I12034036075
    I12034035940
    C12037068835
    C12037081425
    R12037036800
    R12037024745
    I12037037805
    R12037024680
    R12037024690
    R12037024700
    R12037024735
    R12037024720
    R12037024715
    I12037037830
    I12037037855
    I12035000250

So anyone whats wrong with this data? I've used .trim() and also tried to remove insert a space between the first character so it won't look like a cell reference but still no luck. also tried doing this

ws.Cells[cntr + 1, 6].formula= "TEXT(" + myRow["xcClaimNum"].ToString().Trim() ")"

and still no luck. So anybody got any better ideas? Sorry guys i'm kinda desperate here. Been working on this for a while now.

Editor
Mar 5, 2012 at 4:50 PM

have you had posted all code ? I didnt see any problem.

If you change "ws.Cells[cntr + 1, 1].Value = myRow["documentReference"].ToString();" to "ws.Cells[cntr + 1, 1].Value = "aaa";", everything works fine ?


Mar 6, 2012 at 12:56 AM
Edited Mar 6, 2012 at 1:00 AM

here is my code.

private string CreateExcelFile(DataSet leDS,string newFileName,string projectName) 
        {
            string outputdir = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            string folderName = "Production Report_" + String.Format("{0:yyyy-MM-dd}", DateTime.Now);
            string savingPATH = Path.Combine(outputdir, folderName);
            if (!Directory.Exists(savingPATH))
            {
                //Directory.Delete(savingPATH,true);
                Directory.CreateDirectory(savingPATH);
            }
            FileInfo newFS = new FileInfo(savingPATH + "\\" + newFileName);
            if (newFS.Exists) 
            {
                newFS.Delete();
                newFS = new FileInfo(savingPATH + "\\" + newFileName);
            }
            using (ExcelPackage p = new ExcelPackage(newFS)) 
            {
                ExcelWorksheet ws = CreateSheet(p, projectName);
                CreateHeader(ws,projectName);
                plotMainData(leDS, ws,projectName);
                byte[] bin = p.GetAsByteArray();                
                File.WriteAllBytes(newFS.ToString(), bin);
                
            }

            return savingPATH;
        }
private static ExcelWorksheet CreateSheet(ExcelPackage p, string sheetName)
        {
            p.Workbook.Worksheets.Add(sheetName);
            ExcelWorksheet ws = p.Workbook.Worksheets[1];
            ws.Name = sheetName; //Setting Sheet's Name            
            return ws;
        }
private static void CreateHeader(ExcelWorksheet ws,string currentProject)
        {
            if (currentProject == "Proj1" || currentProject == "Proj2")
            {
                if (currentProject == "Proj1")
                {
                    #region Proj1 header
                    ws.Cells[1, 1].Value = "EmpPIN";
                    ws.Cells[1, 2].Value = "KeyerID";
                    ws.Cells[1, 3].Value = "TASK/QUEUE";
                    ws.Cells[1, 4].Value = "POLICY";
                    ws.Cells[1, 5].Value = "RECORDS";
                    ws.Cells[1, 6].Value = "Time[START]";
                    ws.Cells[1, 7].Value = "Time[END]";
                    ws.Cells[1, 8].Value = "HoursWorked(hrs)]";
                    ws.Cells[1, 9].Value = "ElapseTime";
                    var fill = ws.Row(1).Style.Fill;
                    ws.Row(1).Style.Font.Color.SetColor(Color.LightGreen);
                    fill.PatternType = ExcelFillStyle.Solid;
                    fill.BackgroundColor.SetColor(Color.Black);
                    ws.Row(1).Style.Font.Bold = true;
                    #endregion
                }
                else if (currentProject == "Proj2")
                {
                    #region Proj2 header
                    ws.Cells[1, 1].Value = "AssociateName";
                    ws.Cells[1, 2].Value = "EmployeePIN";
                    ws.Cells[1, 3].Value = "TranDate";
                    ws.Cells[1, 4].Value = "Project";
                    ws.Cells[1, 5].Value = "TaskType";
                    ws.Cells[1, 6].Value = "Claim No.";
                    ws.Cells[1, 7].Value = "SCCF#";
                    ws.Cells[1, 8].Value = "Route To";
                    ws.Cells[1, 9].Value = "Started";
                    ws.Cells[1, 10].Value = "Ended";
                    ws.Cells[1, 11].Value = "HoursWorked(hrs)";
                    ws.Cells[1, 12].Value = "Remarks ";
                    ws.Cells[1, 13].Value = "ElapseTime ";
                    var fill = ws.Row(1).Style.Fill;
                    ws.Row(1).Style.Font.Color.SetColor(Color.LightGreen);
                    fill.PatternType = ExcelFillStyle.Solid;
                    fill.BackgroundColor.SetColor(Color.Black);
                    ws.Row(1).Style.Font.Bold = true;
                    #endregion
                }
            }
            else
            {
                #region OtherProejcts Header
                ws.Cells[1, 1].Value = "KeyerID";
                ws.Cells[1, 2].Value = "TranDate";
                ws.Cells[1, 3].Value = "Project";
                ws.Cells[1, 4].Value = "BatchType";
                ws.Cells[1, 5].Value = "BatchName";
                ws.Cells[1, 6].Value = "#Records";
                ws.Cells[1, 7].Value = "#Pulls";
                ws.Cells[1, 8].Value = "Time[Start]";
                ws.Cells[1, 9].Value = "Time[End]";
                ws.Cells[1, 10].Value = "HoursWorked(hrs)";
                ws.Cells[1, 11].Value = "EmpPIN ";
                ws.Cells[1, 12].Value = "ElapseTime ";
                var fill = ws.Row(1).Style.Fill;
                ws.Row(1).Style.Font.Color.SetColor(Color.LightGreen);
                fill.PatternType = ExcelFillStyle.Solid;
                fill.BackgroundColor.SetColor(Color.Black);
                ws.Row(1).Style.Font.Bold = true;
                #endregion
            }
        }
private void plotMainData(DataSet leDS, ExcelWorksheet ws, string curPROJ) 
        {
            if (leDS.Tables[0].Rows.Count > 0)
            {
                if (curPROJ == "Proj1" || curPROJ == "Proj2")
                {
                    if (curPROJ == "Proj1")
                    {
                        #region Proj1 data
                        
                        #endregion
                    }
                    else if (curPROJ == "Proj2")
                    {
                        #region Proj2 data
                        
                        //int rowIndex = 1;
                        int cntr = 1;
                        foreach (DataRow myRow in leDS.Tables[0].Rows) 
                        {                            
                            ws.Cells[cntr + 1, 1].Value = myRow["xcKeyerID"].ToString();
                            ws.Cells[cntr + 1, 2].Value = myRow["xcEmployeePIN"].ToString();
                            ws.Cells[cntr + 1, 3].Value = Convert.ToDateTime(myRow["xcTranDate"]).ToShortDateString();
                            ws.Cells[cntr + 1, 4].Value = "Proj2";
                            ws.Cells[cntr + 1, 5].Value = myRow["xcTaskType"].ToString();
                            //ws.Cells[cntr + 1, 6].Value = myRow["xcClaimNum"].ToString();

			    //you can see here i've tried inserting A with an incremental value
//the result was ok. I was able to opened the excel file output //im still wondering why all the data i've listed above causes the //excel file to be unreadable. ws.Cells[cntr + 1, 6].Value = "A" + cntr.ToString(); ws.Cells[cntr + 1, 7].Value = myRow["xcIntakeNUM"]; ws.Cells[cntr + 1, 8].Value = myRow["xcRouteTo"]; ws.Cells[cntr + 1, 9].Value = myRow["xcTimeStarted"].ToString(); ws.Cells[cntr + 1, 10].Value = myRow["xcTimeEnded"].ToString(); ws.Cells[cntr + 1, 11].Value = ComputeWorkHours(Convert.ToDateTime(myRow["xcTimeStarted"]), Convert.ToDateTime(myRow["xcTimeEnded"])); ws.Cells[cntr + 1, 12].Value = myRow["xcRemarks"]; ws.Cells[cntr + 1, 13].Value = ComputeElapseTime(Convert.ToDateTime(myRow["xcTimeStarted"]), Convert.ToDateTime(myRow["xcTimeEnded"])); int progRep = Convert.ToInt16(Math.Ceiling((double)(cntr * 100) / leDS.Tables[0].Rows.Count)); bgWorker_one.ReportProgress(progRep); cntr = cntr + 1; } #endregion } } else { #region OtherProjects for (int rCount = 0; rCount < leDS.Tables[0].Rows.Count; rCount++) { ws.Cells[rCount + 2, 5].Style.Numberformat.Format = "49"; ws.Cells[rCount + 2, 1].Value = checkForNull(leDS.Tables[0].Rows[rCount][10]); ws.Cells[rCount + 2, 2].Value = Convert.ToDateTime(leDS.Tables[0].Rows[rCount][11].ToString()).ToShortDateString(); ws.Cells[rCount + 2, 3].Value = checkForNull(leDS.Tables[0].Rows[rCount][3]); ws.Cells[rCount + 2, 4].Value = checkForNull(leDS.Tables[0].Rows[rCount][4]); ws.Cells[rCount + 2, 5].Value = checkForNull(leDS.Tables[0].Rows[rCount][5]); ws.Cells[rCount + 2, 6].Value = checkForNull(leDS.Tables[0].Rows[rCount][6]); ws.Cells[rCount + 2, 7].Value = checkForNull(leDS.Tables[0].Rows[rCount][7]); ws.Cells[rCount + 2, 8].Value = checkForNull(leDS.Tables[0].Rows[rCount][8]); ws.Cells[rCount + 2, 9].Value = checkForNull(leDS.Tables[0].Rows[rCount][9]); ws.Cells[rCount + 2, 10].Value = ComputeWorkHours(Convert.ToDateTime(leDS.Tables[0].Rows[rCount][8].ToString()), Convert.ToDateTime(leDS.Tables[0].Rows[rCount][9].ToString())); ws.Cells[rCount + 2, 11].Value = checkForNull(leDS.Tables[0].Rows[rCount][0]); ws.Cells[rCount + 2, 12].Value = ComputeElapseTime(Convert.ToDateTime(leDS.Tables[0].Rows[rCount][8].ToString()), Convert.ToDateTime(leDS.Tables[0].Rows[rCount][9].ToString())); bgWorker_one.ReportProgress(Convert.ToInt16(Math.Ceiling((double)(rCount * 100) / leDS.Tables[0].Rows.Count))); } #endregion }

Thank you for taking your time reading and replying here. It is much appreciated.

Editor
Mar 6, 2012 at 10:54 AM
Edited Mar 6, 2012 at 3:32 PM

When I copied your sample data to notepad++, I´ve noticed a strange char inside your data, take a look at this screenshot

http://i.imgur.com/z67lh.png

Try to run your code and skip that registry in debug mode to test.

Mar 7, 2012 at 2:10 AM

Oh MY!. I COULD kiss your right now men! THANK YOU SO MUCH! you were right. I created a procedure to replace all the possible whitespaces. And now its working fine. Thank you. Really Thank you.