When reading data from a .xlsx file

string Code = "";
  if (fileUp.HasFile)
  {
    string Path = fileUp.PostedFile.FileName;
// initialize the Excel Application class
    ApplicationClass app = new ApplicationClass();

// create the workbook object by opening  the excel file.
    Workbook workBook = app.Workbooks.Open(Path, 0, true, 5, "", "", true,
                   XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

// Get The Active Worksheet Using Sheet Name Or Active Sheet
    Worksheet workSheet = (Worksheet)workBook.ActiveSheet;
    int index = 0;

// This row,column index should be changed as per your need.
// that is which cell in the excel you are interesting to read.
    object rowIndex = 2;
    object colIndex1 = 1;
    object colIndex2 = 2;
    object colIndex3 = 3;
    object colIndex4 = 4;
    object colIndex5 = 5;
    object colIndex6 = 6;
    object colIndex7 = 7;
    try
    {
      while (((Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
      {
        rowIndex = 2 + index;

//string QuestionCode = (index + 1).ToString();
        string QuestionCode = ((Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
        string QuestionText = ((Range)workSheet.Cells[rowIndex, colIndex2]).Value2.ToString();
        string CorrectAnswer = ((Range)workSheet.Cells[rowIndex, colIndex3]).Value2.ToString();
        string ChoiceA = ((Range)workSheet.Cells[rowIndex, colIndex4]).Value2.ToString();
        string ChoiceB = ((Range)workSheet.Cells[rowIndex, colIndex5]).Value2.ToString();
        string ChoiceC = ((Range)workSheet.Cells[rowIndex, colIndex6]).Value2.ToString();
        string ChoiceD = ((Range)workSheet.Cells[rowIndex, colIndex7]).Value2.ToString();
// string ChoiceE = ((Excel.Range)workSheet.Cells[rowIndex, colIndex7]).Value2.ToString();

        newQuestionElement = new XElement("Question");
        XElement optionElement = new XElement(QuestionElement.Option);
        questionType = ddlQusType.SelectedValue.ToByte();
        if (!string.IsNullOrEmpty(QuestionText))
          newQuestionElement.Add(new XElement(QuestionElement.QuestionText, QuestionText));
        else
        {

//lblMessage.Text = "Missing question in Qus No.: " + i;
          break;
        }
        newQuestionElement.Add(new XElement(QuestionElement.QuestionType, questionType));

//newQuestionElement.Add(new XElement(QuestionElement.Randomize, chbRandomizeChoice.Checked));
        newQuestionElement.Add(new XElement(QuestionElement.Answer, CorrectAnswer));
        if (ChoiceA.Trim() != string.Empty)
           optionElement.Add(new XElement("A", ChoiceA));
        if (ChoiceB.Trim() != string.Empty)
           optionElement.Add(new XElement("B", ChoiceB));
        if (ChoiceC.Trim() != string.Empty)
           optionElement.Add(new XElement("C", ChoiceC));
        if (ChoiceD.Trim() != string.Empty)
           optionElement.Add(new XElement("D", ChoiceD));
        newQuestionElement.Add(optionElement);                        

        index++;
        saveData(QuestionCode.ToString());

I use this code to extract data from a .xlsx file.

But if there are any special characters in the file, it shows it as different, for example

The set S = {1,2,33……….12} is to be partitioned into three sets
A,B,C of equal size. Thus,      `A U B U C = S,` 

The set S = {1,2,33……….12} is to be partitioned into three sets
A,B,C of equal size. Thus,      `A È B È C = S,` 
+3
source share
1 answer

Looks like an encoding problem.

I use this problem after reading Excel into a data table and then serializing the data table to a file.

Every time I read data from a serialized file, some characters will be replaced by funny A and E.

I found that the problem is with the encoding I used. Then I started storing excel data using Unicode encoding and never encountered another character problem with Excel data again.

Hope this helps ...

0
source

All Articles