Code to convert MS excel row column data into different layout

Question

1.00/5 (2 votes)

See more:

Hi

I have .xls file with following format.

EmpNo EmpName	EmpCity
1	  abc	     city1
2	   pqr	      city2
3	   xyz	      city3

And I want to convert above .xls file data in following format and save it to some other location.

header 1	header 2
EmpNo	     1
EmpName	    abc
EmpCity	    city1
Blank	    Blank
Blank	    Blank
EmpNo	    2
EmpName 	pqr
EmpCity	    city2
Blank	    Blank
Blank	    Blank
EmpNo	    3
EmpName 	xyz
EmpCity	    city3

Here I have showed 3 records just as an example. In actual .xls may have many records. Number of records will be different each time.

Can anyone help me with Powershell code or .bat file code or any other code (C#) with which we can achieve this.

No third party tool or module installation is allowed.

Kindly help.

What I have tried:

I have tried with Powershell code but it is asking to install some Excel related module for which it was asking Admin creds etc.

As a developer it is not allowed for me.

Posted 17-Jun-24 2:18am

Member 16286847

Updated 17-Jun-24 2:22am

Richard Deeming

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2024-06-17T03:04:00

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

And to be honest you are going to have real problems without 3rd party components as .XLS files are a proprietary binary format, so are going to be a PITA to process without external help. .XLSX files are a little easier because they are ZIP folders containing XLS data files which are at least text based rather than binary, but they aren't going to be easy to work with with batch or Powershell scripts either. C# is your best bet, but ... don't expect that to be trivial stuff either!

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]

Pete O'Hanlon · Answer 2 · 2024-06-17T03:22:00

Unless you are prepared to write your own Excel library, you're going to have to use a third party component of some description. In this case, you're going to be looking for a Nuget package that you can use. Presumably you are going to have to stick with an Open Source offering, possibly something like this one GitHub - ExcelDataReader/ExcelDataReader: Lightweight and fast library written in C# for reading Microsoft Excel files[^].

Add that into your project and write your code; it's not going to be that difficult to do. Pay attention to the data types, they are going to be the things that are likeliest to trip you up when parsing your rows.

Maciej Los · Answer 3 · 2024-06-17T06:55:00

Solution 3

I'd suggest to use Excel spreadsheet library for .NET Framework/Core - EPPlus Software[^]
This library is very light and flexible. You can find tons of examples on the internet.

Posted 17-Jun-24 6:55am

Maciej Los

cmarcotte · Answer 4 · 2024-06-17T16:14:00

If you want use C#, you have several ways to explore. The best way depends on your real needs and possibilities.

You can use microsoft.office.interop.excel[^] to handle excel files and use Excel macro recorder as helper. Excel VBA code will need some easy adaptation to .net objects.

You can use .net system.data.OLEDB namespace[^] and address Excel files as databases. There are numerous examples in codproject, including exporting .net datagriview or datatable to excel

If NuGet packages are allowed, you can use OpenXML[^] sdk in NuGet package or directly in your code without package. This is more difficult.

Code to convert MS excel row column data into different layout

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0