You must Sign In to post a response.
Category: Windows 8
#768520
.txt file will never directly convert to .xls, you need to read the contents from .txt file and write them I excel file
see below snippet, you need to use EXCEL interop object for it
Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]
see below snippet, you need to use EXCEL interop object for it
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
try
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Full Name";
oSheet.Cells[1, 4] = "Salary";
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "D1").Font.Bold = true;
oSheet.get_Range("A1", "D1").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
// Create an array to multiple values at once.
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[4, 1] = "Johnson";
//Fill A2:B6 with an array of values (First and Last Names).
oSheet.get_Range("A2", "B6").Value2 = saNames;
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";
//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.get_Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
//AutoFit columns A:D.
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs("c:\\test\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]
#768524
Hi,
First you have to convert txt file into any other format like string array or datarow and then read that datarow and write it into excel sheet like below.
Hope this helps you...
--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.
N@veen
Blog : http://naveens-dotnet.blogspot.in/
First you have to convert txt file into any other format like string array or datarow and then read that datarow and write it into excel sheet like below.
public static void WriteExcel(DataRow drRow, string outputFilePath, string sheetName)
{
try
{
#region Write Headers to Excel file
objApp.Visible = true;
objApp.DisplayAlerts = false;
if (!string.IsNullOrEmpty(outputFilePath) && !File.Exists(outputFilePath))
{
//create workbook with sheet
#region create excel sheet
objWrkbook = objApp.Workbooks.Add(1);
objWrkSheet = (Excel.Worksheet)objWrkbook.ActiveSheet;
objWrkSheet.Name = "Sheet1";
#endregion
}
else
{
//open workbook with sheet
#region open excel sheet
objWrkbook = objApp.Workbooks.Open(outputFilePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
#endregion
}
objSheets = (Excel.Sheets)objWrkbook.Worksheets;
//open the specific sheet in the workbook
objWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(sheetName);
int colcnt = objWrkSheet.UsedRange.Columns.Count;
int rowcnt = objWrkSheet.UsedRange.Rows.Count;
// write columns to the excel sheet
#region create headers to excel
if (colcnt == 1 && rowcnt == 1)
{
for (int i = 0; i < drRow.Table.Columns.Count; i++)
{
objWrkSheet.Cells[1, i + 1] = drRow.Table.Columns[i].ColumnName;
}
colcnt = drRow.Table.Columns.Count;
}
#endregion
//write rows to the excel sheet
#region insert rows to excel
for (int i = 1; i <= colcnt; i++)
{
objWrkSheet.Cells[rowcnt + 1, i] = drRow[i - 1];
}
#endregion
//save the workbook
#region save workbook
objWrkbook.SaveAs(outputFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
#endregion
#endregion
}
catch (Exception ex)
{
throw;
}
finally
{
//clear objects
#region clear workbook objects
if (objWrkSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkSheet);
if (objWrkbook != null)
objWrkbook.Close(true, outputFilePath, true);
if (objApp != null)
objApp.Quit();
#endregion
}
}
Hope this helps you...
--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.
N@veen
Blog : http://naveens-dotnet.blogspot.in/
#768527
Hi,
You need to read data from the txt file first and then write the data into Excel. If you don't want to install MS Excel, you can use 3rd party libraries, such as this one: https://www.nuget.org/packages/FreeSpire.XLS/.
Add a reference to Spire.Xls.dll and then use below code:
using System.IO;
using Spire.Xls;
namespace Convert_TXT_to_Excel
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
string filepath = "test.txt";
string text = File.ReadAllText(filepath);
string[] cells = text.Split(' ');
for (int i = 0; i < cells.Length; i++)
{
//insert into the first column
sheet.Range[i+1,1].Text = cells[i];
}
workbook.SaveToFile("Result.xlsx",FileFormat.Version2010);
}
}
}
You need to read data from the txt file first and then write the data into Excel. If you don't want to install MS Excel, you can use 3rd party libraries, such as this one: https://www.nuget.org/packages/FreeSpire.XLS/.
Add a reference to Spire.Xls.dll and then use below code:
using System.IO;
using Spire.Xls;
namespace Convert_TXT_to_Excel
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
string filepath = "test.txt";
string text = File.ReadAllText(filepath);
string[] cells = text.Split(' ');
for (int i = 0; i < cells.Length; i++)
{
//insert into the first column
sheet.Range[i+1,1].Text = cells[i];
}
workbook.SaveToFile("Result.xlsx",FileFormat.Version2010);
}
}
}
#768542
Here's a sample cose snippet of how you could convert a txt file to excel by using in c# 2012
Reference : http://www.geekinterview.com/talk/19527-convert-the-text-file-excel-format.html
https://www.codeproject.com/questions/651343/converting-text-file-to-excel-file-using-csharp
static void Main(string[] args)
{
string line;
using (StreamReader reader = new StreamReader(@"yourFilePath.txt"))
{
while ((line = reader.ReadLine()) != null)
{
using (StreamWriter writer = new StreamWriter(@"yourFilePath.csv", true))
{
writer.WriteLine(line.Replace(",",";"));
}
}
}
}
Reference : http://www.geekinterview.com/talk/19527-convert-the-text-file-excel-format.html
https://www.codeproject.com/questions/651343/converting-text-file-to-excel-file-using-csharp
Return to Return to Discussion Forum