如何修复Excel列宽 [英] How to Fixed the Excel Column Width
本文介绍了如何修复Excel列宽的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嗨朋友，
如何设置Excel列宽
我的代码下面..........
Hi Friends,
How to set the Excel column width
Below my code..........
SaveFileDialog fileTerminal = new SaveFileDialog();
fileTerminal.Filter = "xlsx files(*.xlsx)|*.xlsx|xls files(*.xls)|*.xls|All files(*.*)|*.*";
fileTerminal.FileName = lblHeader.Text.Replace(" ", "") + "_" + TodayDateinTimeZone().ToString("ddMMyyyyhhmmss");
fileTerminal.FilterIndex = 2;
fileTerminal.InitialDirectory = @"C:\";
fileTerminal.RestoreDirectory = true;
if (fileTerminal.ShowDialog() == DialogResult.OK)
{
//Microsoft.Office.Interop.Excel.Application xlApp;
//Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
//Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
//object misValue = System.Reflection.Missing.Value;
//xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//xlWorkBook = xlApp.Workbooks.Add(misValue);
//xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
string filename = fileTerminal.FileName;
using (var wrt = new StreamWriter(filename))
{
//wrt.WriteLine("\t\t\t \n");
wrt.WriteLine();
for (int i = 0; i < dtTempExcel.Columns.Count; i++)
{
wrt.Write(dtTempExcel.Columns[i].ToString().Trim() + "\t");
//dtTempExcel.Columns[i].MaxLength = 20;
}
wrt.WriteLine();
for (int j = 0; j < dtTempExcel.Rows.Count; j++)
{
if (dtTempExcel.Rows[j][0].ToString() != null)
{
wrt.Write(dtTempExcel.Rows[j][0].ToString().Trim());
for (int i = 1; i < dtTempExcel.Columns.Count; i++)
{
string Data = dtTempExcel.Rows[j][i].ToString().Replace("\n", "?").Replace("\r", "?").Replace("\t", "?").Replace("\r\n", "?").Replace(" ", "?");
string Replays = Data.Replace("?", " ");
wrt.Write("\t" + Replays.ToString());
}
wrt.WriteLine();
}
}
//xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, dtTempExcel.Columns.Count]).Font.Bold = 20;
//xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, dtTempExcel.Columns.Count]).EntireColumn.AutoFit();
}
}
Plz任何人帮助我...........我现在需要解决方案.. ..
问候，
sathya。
Plz any one help me...........i need the solution now....
regards,
sathya.
推荐答案
你可以实现它使用Excel C＃interop类
You can achieve it using Excel C# interop class
excelFile = new Excel.ApplicationClass();
Excel.Workbook workbook = excelFile.Workbooks.Add(Type.Missing);
excelFile.Visible = true;
Worksheet ws = (Worksheet)excelFile.ActiveSheet;
Microsoft.Office.Interop.Excel.Worksheet worksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(2);
ws.Activate();
public void SetColumnWidth(Worksheet ws, int col, int width)
{
((Range)ws.Cells[1, col]).EntireColumn.ColumnWidth = width;
}
最后我得到解决方案......
SaveFileDialog fileTerminal = new SaveFileDialog（）;
{
fileTerminal.Title =选择要上传的文件;
fileTerminal.FileName = lblHeader.Text .Replace（，）+_+ TodayDateinTimeZone（）。ToString（ddMMyyyyhhmmss）;
fileTerminal.Filter =xlsx files（*。xlsx）| * .xlsx | xls文件（*。xls）| * .xls |所有文件（*。*）| *。*;
fileTerminal.FilterIndex = 2;
fileTerminal.InitialDirectory = @C：\;
fileTerminal.RestoreDirectory = true;
if（fileTerminal.ShowDialog（）== DialogResult.OK）
{
int columnCount = 0;
string filename = fileTerminal.FileName;
Excel.Application xlApp = default（Excel.Application）;
Excel.Workbook xlWorkBook = default（Excel.Workbook）;
Excel.Worksheet xlWorkSheet = default（Excel.Worksheet ）;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass（）;
xlWorkBook = xlApp.Workbooks.Add（misValue）;
xlWorkSheet =（Microsoft.Office.Interop.Excel.Worksheet）xlWorkBook.Worksheets.get_Item（1）;
int i = 0;
int j = 0;
xlWorkSheet.Cells [1,5] = lblHeader.Text.ToString（ ）;
for（int y = 1; y< = dtTempExcel.Columns.Count; y ++）
{
xlWorkSheet.Cells [3，y] = dtTempExcel.Columns [y - 1] .ColumnName;
}
for（i = 0; i< = dtTempExcel.Rows.Count - 1; i ++）
{
for（j = 0; j < = dtTempExcel.Columns.Count - 1; j ++）
{
DataGridViewCell cell = dgvAgentDetails [j，i];
// xlWorkSheet.Cells [i + 4，j + 1] = dtTempExcel.Rows [j] [i] .ToString（）;
xlWorkSheet.Cells [i + 4 ，j + 1] = cell.Value;
}
}
xlWorkSheet.get_Range（xlWorkSheet.Cells [3,1]，xlWorkSheet .Cells [3，dgvAgentDetails.Columns.Count]）。Interior.Color = System.Drawing.Colo rTranslator.ToOle（System.Drawing.Color.Yellow）;
xlWorkSheet.get_Range（xlWorkSheet.Cells [1,1]，xlWorkSheet.Cells [1，dgvAgentDetails.Columns.Count]）。字体。粗体= 40;
xlWorkSheet.get_Range（xlWorkSheet.Cells [3,1]，xlWorkSheet.Cells [1，dgvAgentDetails.Columns.Count]）。Font.Bold = 20;
xlWorkSheet.get_Range（xlWorkSheet.Cells [3,1]，xlWorkSheet.Cells [1，dgvAgentDetails.Columns.Count]）。EntireColumn.AutoFit（）;
xlWorkBook.SaveAs（filename。 ToString（），Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal，misValue，misValue，misValue，misValue，Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive，misValue，misValue，misValue，misValue，misValue）;
xlWorkBook.Close（true，misValue，misValue）;
xlApp.Quit（）;
releaseObject（xlWorkSheet）;
releaseObject（xlWorkBook）;
releaseObject（xlApp）;
}
}
private void releaseObject（object obj）
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）;
obj = null;
}
catch（异常exp）
{
obj = null;
MessageBox.Show（释放对象时出现异常+ exp。 ToString（），Common.MSGBOX_TITLE，MessageBoxButtons.OK，MessageBoxIcon.Error）;
Database.LogData（GlobalVar.getAgentID.ToString（），GlobalVar.getTerminalID.ToString（），GlobalVar.getIpAddress.ToString （），
GlobalVar.getTerminalType.ToString（），X，ControlPanel， releaseObject，exp.Message.ToString（），GlobalVar.getSequenceId）;
}
终于
{
GC.Collect（）;
}
}
Finally i get Solution......
SaveFileDialog fileTerminal = new SaveFileDialog();
{
fileTerminal.Title = "Select File To Upload";
fileTerminal.FileName = lblHeader.Text.Replace(" ", "") + "_" + TodayDateinTimeZone().ToString("ddMMyyyyhhmmss");
fileTerminal.Filter = "xlsx files(*.xlsx)|*.xlsx|xls files(*.xls)|*.xls|All files(*.*)|*.*";
fileTerminal.FilterIndex = 2;
fileTerminal.InitialDirectory = @"C:\";
fileTerminal.RestoreDirectory = true;
if (fileTerminal.ShowDialog() == DialogResult.OK)
{
int columnCount = 0;
string filename = fileTerminal.FileName;
Excel.Application xlApp = default(Excel.Application);
Excel.Workbook xlWorkBook = default(Excel.Workbook);
Excel.Worksheet xlWorkSheet = default(Excel.Worksheet);
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
xlWorkSheet.Cells[1, 5] = lblHeader.Text.ToString();
for (int y = 1; y <= dtTempExcel.Columns.Count; y++)
{
xlWorkSheet.Cells[3, y] = dtTempExcel.Columns[y - 1].ColumnName;
}
for (i = 0; i <= dtTempExcel.Rows.Count - 1; i++)
{
for (j = 0; j <= dtTempExcel.Columns.Count - 1; j++)
{
DataGridViewCell cell = dgvAgentDetails[j, i];
// xlWorkSheet.Cells[i + 4, j + 1] = dtTempExcel.Rows[j][i].ToString();
xlWorkSheet.Cells[i + 4, j + 1] = cell.Value;
}
}
xlWorkSheet.get_Range(xlWorkSheet.Cells[3, 1], xlWorkSheet.Cells[3, dgvAgentDetails.Columns.Count]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, dgvAgentDetails.Columns.Count]).Font.Bold = 40;
xlWorkSheet.get_Range(xlWorkSheet.Cells[3, 1], xlWorkSheet.Cells[1, dgvAgentDetails.Columns.Count]).Font.Bold = 20;
xlWorkSheet.get_Range(xlWorkSheet.Cells[3, 1], xlWorkSheet.Cells[1, dgvAgentDetails.Columns.Count]).EntireColumn.AutoFit();
xlWorkBook.SaveAs(filename.ToString(), Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception exp)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + exp.ToString(), Common.MSGBOX_TITLE, MessageBoxButtons.OK, MessageBoxIcon.Error);
Database.LogData(GlobalVar.getAgentID.ToString(), GlobalVar.getTerminalID.ToString(), GlobalVar.getIpAddress.ToString(),
GlobalVar.getTerminalType.ToString(), "X", "ControlPanel", "releaseObject", exp.Message.ToString(), GlobalVar.getSequenceId);
}
finally
{
GC.Collect();
}
}
我会找到另一个解决方案......
Another Solution I will Find......
string tab = "";
DateTime dtToday = TodayDateinTimeZone();
SaveFileDialog fileTerminal = new SaveFileDialog();
fileTerminal.Filter = "xlsx files(*.xlsx)|*.xlsx|xls files(*.xls)|*.xls|All files(*.*)|*.*";
fileTerminal.FileName = lblTitle.Text.Replace(" ", "") + "_" + dtToday.ToString("ddMMyyyyhhmmss");
fileTerminal.FilterIndex = 2;
fileTerminal.InitialDirectory = @"C:\";
fileTerminal.RestoreDirectory = true;
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
if (fileTerminal.ShowDialog() == DialogResult.OK)
{
string filename = fileTerminal.FileName;
using (var wrt = new StreamWriter(filename))
{
wrt.Write("<HTML><HEAD>");
wrt.Write("<style>TD {font-family:Verdana; font-size: 11px;} </style>");
wrt.Write("</HEAD><BODY>");
wrt.Write("<TABLE border='1' width='100%' >");
wrt.Write("<TR ><TD align='center' style='font-family: Verdana; font-size: 13px; font-weight: bold' colspan='" + dtBookedHistory.Columns.Count + "'>Booked History</TD></TR><TR><TD colspan='" + dtBookedHistory.Columns.Count + "'></TD></TR>");
//wrt.Write("<TR></TR><TR style='height:25px'><TD> Agent ID : " + ddlAgentid.Text.ToString() + "</TD><TD> Payment Gateway : " + cmbPagmentGateway.Text.ToString() + " </TD><TD> PG Status : " + cmbPaymentStatus.Text.ToString() + "</TD><TD> Booked Status : " + cmbBookedStatus.Text.ToString() + "</TD><TD> From Date : " + dtpFromDate.Text.ToString() + "</TD><TD> To Date : " + dtpToDate.Text.ToString() + "</TD></TR><tr></tr>");
// wrt.Write("<TR></TR>");
wrt.Write(sw.ToString());
wrt.Write("<TR >");
//wrt.WriteLine("\t\t\t Booked History \n");
//wrt.WriteLine();
foreach (DataColumn datacolumn in dtBookedHistory.Columns)
{
wrt.WriteLine("<TD align='center' style='color: White; font-weight: bold;background-color:Maroon;font-size: 10px'>" + tab + datacolumn.ColumnName + "</TD>");
}
wrt.WriteLine("</TR>");
// wrt.WriteLine();
foreach (DataRow datarow in dtBookedHistory.Rows)
{
tab = "";
wrt.WriteLine("<TR >");
bool tempRowColorFlag = false;
//if (dtBookedHistory.Rows[j][0].ToString() != null)
//{
//wrt.Write(dtBookedHistory.Rows[j][0].ToString().Trim());
for (int i = 0; i < dtBookedHistory.Columns.Count; i++)
{
if (datarow[i].ToString().ToUpper().Contains("TOTAL"))
tempRowColorFlag = true;
if (tempRowColorFlag)
wrt.Write("<TD align='center' style='background-color: #c0c0c0'>");
else
wrt.Write("<TD align='center'>");
wrt.Write(tab + datarow[i].ToString().Replace("\n", "?").Replace("\r", "?").Replace("\t", "?").Replace("\r\n", "?").Replace(" ", "?").Replace("?", " "));
wrt.Write("</TD>");
}
tempRowColorFlag = false;
wrt.Write("</TR>");
// }
}
wrt.Write("</TABLE>");
wrt.Write("</BODY></HTML>");
try
{
HttpContext.Current.Response.End();
}
catch
{
}
}
}这篇关于如何修复Excel列宽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
