Click here to Skip to main content
15,920,896 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Sir,

Good day, can you give me a sample code on how could I use the excel autofit in C#? How could I update my current code below? your help really appreciated. (urgent) Thanks and God bless.

private void Convert_TextFile()
{
    // Open the text file in Excel.
    m_objExcel = new Excel.Application();
    m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
    m_objBooks.OpenText(m_strSampleFolder + "test-data.csv",
    //m_objBooks.OpenText(m_strSampleFolder + "mean_act_DL_VISMIN_201102.txt",
        Excel.XlPlatform.xlWindows, 1,
        Excel.XlTextParsingType.xlDelimited,
        Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
        false, true, false, false, false, false, m_objOpt, m_objOpt,
        m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

    m_objBook = m_objExcel.ActiveWorkbook;
    
    // Save the text file in the typical workbook format and quit Excel.
    m_objBook.SaveAs(m_strSampleFolder + "test-data.xls",
    //m_objBook.SaveAs(m_strSampleFolder + "mean_act_DL_VISMIN_201102.xls",
        Excel.XlFileFormat.xlWorkbookNormal,
        m_objOpt, m_objOpt, m_objOpt, m_objOpt,
        Excel.XlSaveAsAccessMode.xlNoChange,
        m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

    m_objBook.Close(false, m_objOpt, m_objOpt);
    m_objExcel.Quit();
    TODO: Code on opening excel in autofit format (entire data)
}
Posted
Updated 15-Feb-11 15:55pm
v3

To adjust the column width, you can do the following:
Excel.Range xlEntireColumn = null;
Excel.Range xlRange = null;

while (reader.Read()) {
for (int i=0;i<numcols;i++) { 
    xlsheet.ActiveSheet.Cells[row,i+1] = reader.GetValue(i).ToString();

    xlRange = xlSheet.ActiveSheet.Cells[row, i+1];
    xlEntireColumn = xlRange.EntireColumn;
    xlEntireColumn.AutoFit();
}


Hope this might help you.
 
Share this answer
 
Comments
Silver Lightning 16-Feb-11 0:10am    
Thanks sir Ramalinga, but where in the program could I put that code? Here's the complete program code below, just guide me a little sir. thank you and God bless
===================================

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

namespace IROCtrans
{
public partial class frmIroc : Form
{
public frmIroc()
{
InitializeComponent();
}

//-----------------
// Excel object references.
private Excel.Application m_objExcel = null;
private Excel.Workbooks m_objBooks = null;
private Excel._Workbook m_objBook = null;
private Excel.Sheets m_objSheets = null;
private Excel._Worksheet m_objSheet = null;
private Excel.Range m_objRange = null;
private Excel.Font m_objFont = null;
private Excel.QueryTables m_objQryTables = null;
private Excel._QueryTable m_objQryTable = null;

//- ----------------

// Frequenty-used variable for optional arguments.
private object m_objOpt = System.Reflection.Missing.Value;

// Paths used by the sample code for accessing and storing data.
private object m_strSampleFolder = "D:\\Sonix\\TryCode\\";

private void frmIroc_Load(object sender, EventArgs e)
{
cmbSelect.DropDownStyle = ComboBoxStyle.DropDownList;
cmbSelect.Items.AddRange(new object[]{
"Convert Text File to Excel File",
"Exit"});
cmbSelect.SelectedIndex = 0;
cmdConvert.Text = "&Access";
}

private void cmdConvert_Click(object sender, EventArgs e)
{
switch (cmbSelect.SelectedIndex)
{
case 0:
Convert_TextFile();
break;
case 1:
Exit_Application();
break;
}

//Clean-up
m_objFont = null;
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBooks = null;
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
private void Exit_Application()
{
cmdConvert.Text = "";
cmdConvert.Text = "Exiting...";
System.Threading.Thread.Sleep(3000);

this.Close();
}

private void Convert_TextFile()
{
// Open the text file in Excel.
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBooks.OpenText(m_strSampleFolder + "test-data.csv",
Excel.XlPlatform.xlWindows, 1,
Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
false, true, false, false, false, false, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

m_objBook = m_objExcel.ActiveWorkbook;

// Save the text file in the typical workbook format and quit Excel.
m_objBook.SaveAs(m_strSampleFolder + "test-data.xls",
Excel.XlFileFormat.xlWorkbookNormal,
m_objOpt, m_objOpt, m_objOpt, m_objOpt,
Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();


Excel.Range xlEntireColumn = null;
Excel.Range xlRange = null;

}
}
}
Silver Lightning 16-Feb-11 2:41am    
could anyone help? thanks
As I have told you before I do not do very much with Excel (to give you some idea, the version on my machine is Office '95), however, I have found this[^] thread which seems to include code to set Excel's Autofit feature for a column.
 
Share this answer
 
Hi,

Refer this link...
I hope its really helpful to you.

Export to Excel in Winforms[^]

Cheers :)
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900