Adjust width of column in excel sheet using OpenXML Library in ASP.Net

Last Reply on Nov 03, 2016 03:19 AM By AnandM

Posted on Nov 02, 2016 05:01 AM

I am trying to copy the data in excel sheet but it does not show properly it is show like ####### but i want 17-09-2016 like this.kindly suggest me what code i am write to export the excel in proper format.  

Code:-

var rngTable2 = ws.Range("A:G"); 
var rngHeaders2 = rngTable2.Range("F4:G4"); 
rngHeaders2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.General; 
rngHeaders2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Bottom;

 

 

nt companyId = Convert.ToInt32((ddlCompanyName.SelectedValue));
string companyName = ddlCompanyName.SelectedItem.Text;
int index = 5;
int index1 = 5;
int index2 = 5;
int index3 = 5;
int index4 = 5;
int index5 = 5;
int index6 = 5;

var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Company and Vessel Details");

//First Names
ws.Cell("A1").Value = "Company Name ";
ws.Cell("B1").Value = "Total Tokens";
ws.Cell("C1").Value = "Approve Tokens";
ws.Cell("D1").SetValue("Remaining Tokens"); // Another way to set the value

for (int i = 0; i < gvCompany.Rows.Count; i++)
{
string sid = gvCompany.Rows[i].Cells[3].Text;
ws.Cell("A2").Value = sid;
string cmpl = gvCompany.Rows[i].Cells[4].Text;
ws.Cell("B2").Value = cmpl;
string kpit = gvCompany.Rows[i].Cells[5].Text;
ws.Cell("C2").Value = kpit;
string tcs = gvCompany.Rows[i].Cells[6].Text;
ws.Cell("D2").Value = tcs;
}


ws.Columns().AdjustToContents();

 

var rngTable = ws.Range("A:G");
var rngHeaders = rngTable.Range("A4:G4"); // The address is relative to rngTable (NOT the worksheet)
rngHeaders.SetAutoFilter();
rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
rngHeaders.Style.Alignment.Vertical = XLAlignmentVerticalValues.Bottom;
rngHeaders.Style.Font.Bold = true;
rngHeaders.Style.Font.FontColor = XLColor.White;
rngHeaders.Style.Fill.BackgroundColor = XLColor.SteelBlue;

Columns columns = new Columns();

columns.Append(new Column() { Min = 1, Max = 3, Width = 20, CustomWidth = true });
columns.Append(new Column() { Min = 4, Max = 4, Width = 30, CustomWidth = true });


var rngTable1 = ws.Range("A1:D1");
var rngHeaders1 = rngTable1.Range("A1:D1"); // The address is relative to rngTable (NOT the worksheet)
//rngHeaders1.SetAutoFilter();
rngHeaders1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
rngHeaders1.Style.Font.Bold = true;
rngHeaders1.Style.Font.FontColor = XLColor.White;
rngHeaders1.Style.Fill.BackgroundColor = XLColor.SteelBlue;
ws.Cell("A4").Value = "Vessel Name";
ws.Cell("B4").Value = " Ship Email ID";
ws.Cell("C4").Value = "Total Tokens (300)";
ws.Cell("D4").Value = "Assigned Tokens (99)";
ws.Cell("E4").Value = "Remaining Tokens (201)";
ws.Cell("F4").Value = "Package Start Date(dd/mm/yyyy)";
ws.Cell("G4").Value = "Last Update Date(dd/mm/yyyy)";

string query = string.Empty;
DataTable dt = new DataTable();
query = "select vesselID, vesselName,ShipEmailID, CompanyMasterId , ISNULL(AllowToken, 0 ) as 'AllowToken', ISNULL(AssignTokenToEmp, 0 ) as 'Total Assign Token',( ISNULL(AllowToken, 0 ) - ISNULL(AssignTokenToEmp, 0 )) as 'RemainingToken' from Vessel where CompanyMasterID =" + companyId;

dt = SqlHelper.ReturnDataTable(query);
divcrew.Visible = true;
gvvessel.AllowPaging = false;
gvvessel.DataSource = dt;
gvvessel.DataBind();

for (int j = 0; j < gvvessel.Rows.Count; j++)
{

Label lblvess = (Label)gvvessel.Rows[j].FindControl("lblvesselName");
string mylblvess = lblvess.Text;
ws.Cell("A" + index.ToString()).Value = mylblvess;
index++;

Label lblShipEmail = (Label)gvvessel.Rows[j].FindControl("lblShipEmailID");
string mylblShipEmail = lblShipEmail.Text;
ws.Cell("B" + index1.ToString()).Value = mylblShipEmail;
index1++;


Label lblAllowTo = (Label)gvvessel.Rows[j].FindControl("lblAllowToken");
string mylblAllowTo = lblAllowTo.Text;
ws.Cell("C" + index2.ToString()).Value = mylblAllowTo;
index2++;

Label lblAssignToken = (Label)gvvessel.Rows[j].FindControl("lblAssignToken");
string mylAssignToken = lblAssignToken.Text;
ws.Cell("D" + index3.ToString()).Value = mylAssignToken;
index3++;

Label lblRemainingT = (Label)gvvessel.Rows[j].FindControl("lblRemainingToken");
string mylRemainingT = lblRemainingT.Text;
ws.Cell("E" + index4.ToString()).Value = mylRemainingT;
index4++;

Label lblpkgdate = (Label)gvvessel.Rows[j].FindControl("lblpackagedate");
string myVal1 = lblpkgdate.Text;

ws.Cell("F" + index5.ToString()).Value = myVal1;

index5++;

//Where date is come from my myVal1;

 

Label lbllastupd = (Label)gvvessel.Rows[j].FindControl("lbllastupdate");
string myVal2 = lbllastupd.Text;
ws.Cell("G" + index6.ToString()).Value = myVal2;
index6++;
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + companyName + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);

Response.Flush();
// Response.End();
Response.SuppressContent = true;

}
}

 

Posted on Nov 03, 2016 03:19 AM

Hi santosh86,

Please add below code in your header section date column

ws.Cell("F4").WorksheetColumn().AdjustToContents();

in above AdjustToContents you set Width of Column like below

ws.Cell("F4").WorksheetColumn().AdjustToContents(10, 50);