excel - 使用 StreamWriter 更改 excel 列宽
问题描述
我需要这段代码的帮助。
我正在编写代码以使用 将数据表数据导出到 excel 工作表StreamWriter
,一切都很好,但我想更改 excel 中每一列的 excel 列宽度,我只能一次更改所有列的宽度。
这是列宽的代码:
.WriteLine(" <Column ss:Width=""100""/>")
有没有办法改变列(0)宽度= 20和列(1)宽度= 18等等?还是让它自动适应??
谢谢你。
这是我的代码:
Imports System.IO
Public Class ExportToExcelClr
Public Sub ExportToExcel(datatable As DataTable, GV As DataGridView, filename As String, Optional OpenDialog As Boolean = False, Optional SameColumnsWidth As Boolean = True)
Dim FlNm As String = ""
Dim sv As New SaveFileDialog
sv.FileName = filename
sv.Filter = "|*.xls"
If sv.ShowDialog <> DialogResult.OK Then Exit Sub
FlNm = sv.FileName
Dim fs As New StreamWriter(FlNm, False)
With fs
.WriteLine("<?xml version=""1.0""?>")
.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
.WriteLine("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"">")
.WriteLine(" <Styles>")
.WriteLine(" <Style ss:ID=""hdr"">")
.WriteLine(" <Alignment ss:Horizontal=""Center""/>")
.WriteLine(" <Borders>")
.WriteLine(" <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" </Borders>")
.WriteLine(" <Font ss:FontName=""Droid Arabic Kufi"" ss:Size=""8"" ss:Bold=""1""/>") 'SET FONT
.WriteLine(" </Style>")
.WriteLine(" <Style ss:ID=""ksg"">")
.WriteLine(" <Alignment ss:Vertical=""Bottom""/>")
.WriteLine(" <Borders/>")
.WriteLine(" <Font ss:FontName=""Arial""/>") 'SET FONT
.WriteLine(" </Style>")
.WriteLine(" <Style ss:ID=""isi"">")
.WriteLine(" <Borders>")
.WriteLine(" <Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine(" </Borders>")
.WriteLine(" <Font ss:FontName=""Arial"" ss:Size=""11""/>")
.WriteLine(" </Style>")
.WriteLine(" </Styles>")
.WriteLine(" <Worksheet ss:Name=""sheet1"">")
.WriteLine(" <Table>")
'If SameColumnsWidth Then
'For i = 0 To GV.ColumnCount - 1
.WriteLine(" <Column ss:Width=""100""/>")
'Next
'End If
.WriteLine(" <Row ss:StyleID=""ksg"">")
For i As Integer = 0 To GV.ColumnCount - 1
Application.DoEvents()
.WriteLine(" <Cell ss:StyleID=""hdr"">")
.WriteLine(" <Data ss:Type=""String"">{0}</Data>", GV.Columns.Item(i).HeaderText)
.WriteLine(" </Cell>")
Next
.WriteLine(" </Row>")
For intRow As Integer = 0 To datatable.Rows.Count - 1
Application.DoEvents()
.WriteLine(" <Row ss:StyleID=""ksg"" ss:utoFitHeight =""0"">")
For intCol As Integer = 0 To datatable.Columns.Count - 1
Application.DoEvents()
.WriteLine(" <Cell ss:StyleID=""isi"">")
.WriteLine(" <Data ss:Type=""String"">{0}</Data>", GV.Item(intCol, intRow).Value.ToString)
.WriteLine(" </Cell>")
Next
.WriteLine(" </Row>")
Next
.WriteLine(" </Table>")
.WriteLine(" </Worksheet>")
.WriteLine("</Workbook>")
.Close()
If OpenDialog Then
Dim msg1
msg1 = MsgBox("تم الاستخراج ... هل تريد فتح الملف ؟", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Export")
If msg1 = vbNo Then Exit Sub
Process.Start(FlNm)
End If
End With
End Sub
Sub DataGridView_COLORE_FOR_ME(ByVal DataGridViewXT As Object)
Try
'-------------------------------------------------------------------------------------------
DataGridViewXT.DefaultCellStyle.BackColor = Color.FromArgb(255, 255, 255)
DataGridViewXT.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(255, 255, 192)
'-------------------------------------------------------------------------------------------
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.MsgBoxRight + MsgBoxStyle.MsgBoxRtlReading, "Error")
End Try
End Sub
End Class
解决方案
如果此属性 (AutoFitWidth) 指定为 True ("1"),则意味着该列应仅针对数字和日期值自动调整大小。我们不会自动调整文本值。
如果 ss:Width 和 ss:AutoFitWidth 都存在,则行为如下:
ss:AutoFitWidth="1" and ss:Width is unspecified: Autofit the column width to fit the content.
ss:AutoFitWidth="1" and ss:Width is specified: Set the column to the specified width and only autofit if the size of the content is larger than the specified width.
ss:AutoFitWidth="0" and ss:Width is unspecified: Use the default column width.
ss:AutoFitWidth="0" and ss:Width is specified: Use the specified width.
取自这里: https ://www.codeproject.com/Questions/789892/How-to-set-autosize-the-cell-of-excell-sheet-when
因此,您的线路将从:
.WriteLine(" <Column ss:Width=""100""/>")
要么
.WriteLine(" <Column ss:AutoFitWidth=""1"" ss:Width=""20""/>")
或者
.WriteLine(" <Column ss:AutoFitWidth=""1""/>")
这些列在输出文件中按顺序排列,因此您可以将它们放入:
.WriteLine(" <Column ss:Width=""8""/>")
.WriteLine(" <Column ss:Width=""16""/>")
.WriteLine(" <Column ss:Width=""24""/>")
.WriteLine(" <Column ss:Width=""11""/>")
或者
For i As Integer = 0 To GV.ColumnCount - 1
.WriteLine(" <Column ss:AutoFitWidth=""1""/>")
Next
我会首先在 Excel 中创建您想要的文件。将其保存为 XML 并在 Google Chrome 或 IE 中查看 XML。这将向您展示 xml 的外观。
推荐阅读
- java - 根据 ENUM 中定义的顺序对 Map 进行排序
- python - LSTM中目标数据的形状应该是什么,用于预测序列中的数字
- ruby-on-rails - 在方法中访问路由助手
- c++ - 如果'A'有一个'A*'类型的成员变量'x',而'B'继承自'A',如何在'B'内部将'x'重新定义为'B*'?
- c# - 为运行时语言更改 WPF 加载所有可用的附属程序集
- android - Android Studio 的 AVD 中的模拟器无法正确启动
- google-compute-engine - 带有 OpenVPN 服务器的 Google Cloud Engine
- sql-server - 创建 SQL 表时如何给出系统日期?
- javascript - 登录页面时如何从网页获取 HTML 数据并使用 .NET 调用下一页链接的单击事件
- reactjs - Gatsby Redux combineReducers 函数