Spire.DataExport for .NET is a 100% pure data .NET library suit for exporting data into MS Word, Excel, RTF, Access, PDF, XPS, HTML, XML, Text, CSV, DBF, SYLK, SQL Script, DIF, Clipboard, etc.

Thu May 30, 2019 6:01 am

Hi,

I am actually new to .NET programming.
I am trying to read an excel file which has the following conditions.

1. Header row is not the firstrow.
2. some 50 odd columns are available and the columns have decimal data and some are empty as well.

Basically, I want to read an excel headers along with the data from different customers and write to an excel file with generic header.

For example, In one excel sheet the First name might be spelled as Fname and in the other first_name and so on.
I want to read this first name from all this excel sheet and write it into a sheet with a header name First_Name.

My Basic idea is to read all column headrs from the source excel file and list in a form and show my destination headers(Since the headers are constant here) in the same form. Such that the user itself select for each destination column which source column data needs to be taken.

For example, a user can select First_Name data for destination column to be fname data from his source excel file.

Can your forum help me in getting this done.

Regards,
Ahmed

mohaseen
 
Posts: 4
Joined: Thu May 30, 2019 5:43 am

Thu May 30, 2019 7:10 am

Hello,

Thank you for contacting.
Our Spire.XLS supports retrieving data from one excel worksheet and extract to a new excel file. You can refer to the tutorial (retrieve and extract data) to achieve. If it couldn't meet your requirement, to help better investigate your issue, please provide your resource excel files as well as your desired effect of output for our reference. You could send them to us via email (support@e-iceblue.com).

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Tue Jun 04, 2019 5:48 am

Hello,

Greetings from E-iceblue.
Could you please let us know how is your issue going? Thanks in advance for your feedback and time.

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Wed Jun 19, 2019 6:48 am

The replies given in the post couldn’t fulfil my requirement.

As requested, I have sent my source excel data and the expected destination excel data in mail

thank you for your reply.

Regards,
Ahmed

mohaseen
 
Posts: 4
Joined: Thu May 30, 2019 5:43 am

Wed Jun 19, 2019 6:52 am

My source excel data is as given below.
/*
Some Junk data
My first row starts at some location below. Which is not constant always

SNO Fname Lname date Time
1 carl joe 22-Jan-89 15:00:00
2 John fred 15-Sep-90 7:00:00

One more excel file might be with the below header having the nearly the same data

SNO firstname lastname date Time
1 kovac bran 25-Jan-87 18:00:00
2 grant elliot 18-Oct-90 5:00:00
*/

My destination excel data should be as given below.
/*
My destination excel file will have the standard header.

S.NO First_Name Last_Name Date Time
1 carl joe 22-Jan-89 15:00:00
2 John fred 15-Sep-90 7:00:00
1 kovac bran 25-Jan-87 18:00:00
2 grant elliot 18-Oct-90 5:00:00
*/

Regards,
Ahmed

mohaseen
 
Posts: 4
Joined: Thu May 30, 2019 5:43 am

Wed Jun 19, 2019 11:31 am

Hello,

Thanks for your feedback.
According to your sample files, please refer to the following sample code to get corresponding data from source excel file and add them into destination excel file which has the headers. If there is any question, just feel free to write back.
Code: Select all
//load source file
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"Source_excel.xlsx");
Worksheet sheet = workbook.Worksheets[0];
//get the NumberFormulas
string dateNumberFormula = sheet.Range["E6:E7"].Style.NumberFormat;
string timeNumberFormula = sheet.Range["F6:F7"].Style.NumberFormat;
//find "SNO" string
CellRange[] ranges = sheet.FindAllString("SNO", false, false);
//define lists to save the data
List<List<string>> lists = new List<List<string>>();
List<string> values1 = new List<string>();
List<string> values2 = new List<string>();
List<string> values3 = new List<string>();
List<string> values4 = new List<string>();
foreach (CellRange range in ranges)
{
    int row = range.Row;
    for (int column = 1; column <= sheet.AllocatedRange.Columns.Count(); column++)
    {
        for (int i = 1; i < 3; i++)
        {
            CellRange xCell1 = sheet.Range[row + i, column];
            string value1 = xCell1.DisplayedText;
            if (value1 != null && value1 != "")
            {
                if (row+i == 6)
                {
                    values1.Add(value1);
                }
                if (row + i == 7)
                {
                    values2.Add(value1);
                }
                if (row + i == 13)
                {
                    values3.Add(value1);
                }
                if (row + i == 14)
                {
                    values4.Add(value1);
                }
            }
        }
    }
}
lists.Add(values1);
lists.Add(values2);
lists.Add(values3);
lists.Add(values4);
//load the Destination file
Workbook Destination_workbook = new Workbook();
Destination_workbook.LoadFromFile(@"Destination.xlsx");
Worksheet sheet1 = Destination_workbook.Worksheets[0];
//set NumberFormulas as source file
sheet1.Range["D5:D8"].Style.NumberFormat = dateNumberFormula;
sheet1.Range["E5:E8"].Style.NumberFormat = timeNumberFormula;
//find "S.NO" string
CellRange[] cellRanges = sheet1.FindAllString("S.NO", false, false);
int j = 1;
//add the data
foreach (CellRange range in cellRanges)
{
    int row = range.Row;
    for ( int m=0; m<lists.Count();m++)
    {
        for (int column = 1; column <= sheet1.AllocatedRange.Columns.Count(); column++)
        {
           sheet1.Range[row + j, column].Value = lists[m][column - 1];
        }
    j++;
    }
}
Destination_workbook.SaveToFile("final.xlsx", ExcelVersion.Version2013);

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Thu Jun 20, 2019 5:06 am

Hi Lisa,

Thank you for your response.
But you are missing my constraints given in the post.

1. Header row is not the firstrow.
2. some 50 odd columns are available and the columns have decimal data and some are empty as well.
3. we don't have standard column headers in the source excel sheet. It may vary based on different customers.

However my number of source columns and their names may differ from each source excel sheet that I would like to handle(as depicted the “firstname” in the example), but my destination column name is constant(First_Name).

Still if you are not clear with the requirement, you can contact me on my mobile number, which I have sent in mail. I can clarify on call.

Please reply for any clarifications.

Regards,
Ahmed

mohaseen
 
Posts: 4
Joined: Thu May 30, 2019 5:43 am

Thu Jun 20, 2019 7:27 am

Hello

Thanks for your feedback.
Since the process to operate Excel file with our Spire.XLS is purely programmatic without any UI. Reading and getting the corresponding data have to base on the specific range data of source excel file. As for your situation, sorry there is no better way to achieve your requirement. But our Spire.Spreadsheet (at present the latest version is Spire.Spreadsheet Pack(hot fix) Version:3.6.4) supports the UI operations. You can run the uploaded simple Demo to check if it could fulfill your demand. If there is any question, just feel free to contact us.

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Return to Spire.DataExport