有关格式的问题,可以先打开excel ,录制宏,生成VB代码,再转换成C#语言,
1 设置字体大小
oSheet.get_Range("A" + (nFillRow).ToString(), strCol + "1").Font.Size =18;
2 根据列的大小(int型 )得到对应的列名
取得列名
// 通常字段数不会太多,所以到 26*5 目前已经够了。
private string GetColName(int iColNum)
{
string strColNames = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,"
+"AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ,"
+"BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BY,BZ,"
+"CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO,CP,CQ,CR,CS,CT,CU,CV,CW,CX,CY,CZ,"
+"DA,DB,DC,DD,DE,DF,DG,DH,DI,DJ,DK,DL,DM,DN,DO,DP,DQ,DR,DS,DT,DU,DV,DW,DX,DY,DZ";
string []ss=strColNames .Split(new char []{','});
return ss[iColNum - 1];
}
3、根据每一列的内容,设置合适的列宽(列宽比每一列的最大值大一点点就可以了)
for(int i=0;i<nRow ;i++)
for(int j=0;j<nCol ;j++)
iWeights[i, j] = ss[i, j].Length;//得到各单元格的长度
iColoumn[0] = 0;
for (int m = 0; m < nCol; m++)
{
for (int k = 0; k < nRow ; k++)
if (iWeights[k , m] > iColoumn[m] )
iColoumn[m] = iWeights[k , m];//得到各列最大值,
}
if (bSetColoumnWeight == true)
{
for (int c = 0; c < nCol; c++)
{
string strColoumn = GetColName(c + 1);
oSheet.get_Range(strColoumn + "1", strColoumn + "2").ColumnWidth = iColoumn[c] + 3; //设置每列宽度比它本身的数字大3个位置噢
}
}
4、给选中的范围加边框 、样式
oRange = oSheet.get_Range("A1", "e6");
oRange.Borders.Weight = 2;
oRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
// range.Interior.Pattern = Excel.XlPattern.xlPatternCrissCross; 加样式
5、内容居中对齐
StringRange1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// StringRange1.HorizontalAlignment = HorizontalAlignment.Center; //开始用此句,不行啊
1using System;
2using System.Collections.Generic;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Text;
7using System.Windows.Forms;
8using System.Threading;
9using System.Diagnostics;
10using SuperMapLib;
11using System.Reflection;
12using System.Collections;
13
14using Excel = Microsoft.Office.Interop.Excel;
15using ExcelApplication = Microsoft.Office.Interop.Excel.Application;
16using XlBordersIndex = Microsoft.Office.Interop.Excel.XlBordersIndex;
17namespace soFillExcel
18{
19 /**//// <summary>
20 ///
21 /// </summary>
22 public partial class frmExcel : Form
23 {
24
25 soRecordset objRecordset;// = Common.objRecordSet;
26 soFieldInfos objFieldInfos=new soFieldInfos();
27 int nRows, nCols;//,i,j;
28 bool bSave;
29 string[ ,] strs;
30
31
32 //Excel.Application oExcel= new Excel.Application();
33 ExcelApplication oExcel = new Excel.Application();
34 Excel.Workbook oBook ;
35 Excel.Worksheet oSheet;
36 Excel.Range oRange;
37 Excel.Range range;
38 string strPath="";//初始时默认打开的文件路径名
39 string strSavePath="";
40 int nFillRow, nFillCol;
41 /**//// <summary>
42 ///
43 /// </summary>
44 /// <param name="objRs"></param>
45 public frmExcel(soRecordset objRs)
46 {
47 objRecordset = objRs;
48 InitializeComponent();
49 }
50 /**//// <summary>
51 ///
52 /// </summary>
53 /// <param name="ss"></param>
54 public frmExcel(string [,]ss)
55 {
56 strs = ss;
57 InitializeComponent();
58 }
59 private void frmExcel_Load(object sender, EventArgs e)
60 {
61
62 }
63
64 private void btnPrint_Click(object sender, EventArgs e)
65 {
66 this.axOA1.Print();
67 }
68
69
70 private void btnSaveAsFile_Click(object sender, EventArgs e)
71 {
72 this.saveFileDialog1.Filter = "Excel( *.xls)|*.xls";
73 this.saveFileDialog1.ShowDialog();
74 this.axOA1.Save(this.saveFileDialog1.FileName);
75
76
77 }
78
79 private void btnSaveFile_Click(object sender, EventArgs e)
80 {
81 save();
82 bSave = true;
83 }
84 private void save()
85 {
86 try
87 {
88
89 DateTime now = DateTime.Now;
90 string strTime = now.Year.ToString() + "-" + now.Month.ToString() + "-" + now.Day.ToString() + "-" + now.Hour.ToString() + "-" + now.Minute.ToString() + "-" + now.Second.ToString();
91 strPath =strSavePath +strTime + ".xls";
92 this.axOA1.Save(strPath);
93 MessageBox.Show("保存成功!");
94 }
95 catch
96 {
97
98 }
99 finally
100 {
101 }
102 }
103 private void frmExcel_FormClosed(object sender, FormClosedEventArgs e)
104 {
105
106 try
107 {
108 this.Hide();
109
110 oBook = null;
111 oSheet = null;
112 oRange = null;
113 Common.bfrmShowed = false;
114 if (oExcel != null)
115 {
116 oExcel.Quit();
117 oExcel = null;
118 }
119 }
120 finally
121 {
122 this.Dispose();
123 GC.Collect();
124 GC.WaitForPendingFinalizers();
125
126 }
127 }
128
129 private void btnOpenFile_Click(object sender, EventArgs e)
130 {
131 try
132 {
133
134 if (bSave==false)
135 {
136 DialogResult ds= MessageBox.Show("当前文档尚未保存,点击确定保存!", "系统提示", MessageBoxButtons.OKCancel);
137 if (ds == DialogResult.OK)
138 {
139 save();
140 bSave = true;
141 }
142 else return;
143 }
144 this.openFileDialog1.Filter = "Excel( *.xls)|*.xls";
145 this.openFileDialog1 .ShowDialog();
146 this.axOA1.Open(this .openFileDialog1.FileName);
147
148 }
149 catch
150 {
151
152 }
153 finally
154 {
155 }
156 }
157
158 private void btnClose_Click(object sender, EventArgs e)
159 {
160 this.Hide();
161 this.axOA1.Close();
162
163 this.Close();
164 }
165
166
167
168
169 /**//// <summary>
170 /// 数据集填充excel
171 /// </summary>
172 /// <param name="objRs">数据集</param>
173 /// <param name="nType"></param>
174 /// <param name="strStart"></param>
175 /// <param name="strEnd"></param>
176 /// <param name="bAutoSetColoumn">自动设置列宽</param>
177 public void frmLoaded(soRecordset objRs, int nType, string strStart, string strEnd,bool bAutoSetColoumn)
178 {
179 int [,] iWeights;//用于得到每个单元格的宽度
180 int[] iColoumn;//得到每列最大宽度
181 try
182 {
183 if (Common.bfrmShowed == true)
184 this.axOA1.Close();
185 switch (nType)
186 {
187 case 1: //普通表
188 nFillRow = 2;
189 nFillCol = 1;
190 strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\sample.xls";
191 strSavePath = System.Windows.Forms.Application.StartupPath + "\\其它表\\";
192 break;
193 case 2: //统计表
194 nFillRow = 14;
195 nFillCol = 1;
196 strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\Template.xls";
197 strSavePath = System.Windows.Forms.Application.StartupPath + "\\字段统计表\\";
198 break;
199 case 3:
200 nFillRow = 9;
201 nFillCol = 1;
202 strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\SampleGjx.xls";
203 strSavePath = System.Windows.Forms.Application.StartupPath + "\\检修表\\";
204 break;
205 case 4:
206 nFillRow = 11;
207 nFillCol = 1;
208 strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\SampleGwx.xls";
209 strSavePath = System.Windows.Forms.Application.StartupPath + "\\维修表\\";
210 break;
211 default:
212 break;
213 }
214
215 Common.bfrmShowed = true;
216 axOA1.Open(strPath);
217 oBook = (Excel.Workbook)axOA1.GetIDispatch();
218 oExcel = oBook.Application;
219 oSheet = (Excel.Worksheet)oBook.Worksheets[1];
220 if (objRs == null)
221 {
222 MessageBox.Show("选择集为空,请先选择对象!");
223 return;
224 }
225 nRows = objRs.RecordCount;
226 nCols = objRs.FieldCount;
227 iWeights=new int [nRows,nCols+1];
228 iColoumn = new int[nCols ];
229 string strCol = GetColName(nCols); //调用自定义函数得到列名
230 //加粗边框
231 Excel.Range StringRange = (Excel.Range)oSheet.Cells[nFillRow, nFillCol];
232 StringRange = StringRange.get_Resize(nRows, nCols);
233 StringRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
234 StringRange.Borders.Weight = 2;
235 StringRange.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
236
237 string[,] strValues = new string[nRows, nCols];
238 string a = "";
239 string[] s = new string[nCols];
240 for (int j = 0; j < objRs.FieldCount; j++)
241 s[j] = objRs.GetFieldInfo(j + 1).Name.Trim();
242 objRs.MoveFirst();
243 for (int i = 0; i < nRows; i++)
244 {
245 for (int j = 0; j < nCols; j++)
246 {
247
248 iWeights[i,j] = 0;
249 a = s[j];
250 strValues[i, j] = objRs.GetFieldValue(a).ToString();
251 iWeights[i,j] = strValues[i, j].Length;//得到各单元格的长度
252
253 }
254 if (objRs.IsEOF() != true)
255 {
256
257 objRs.MoveNext();
258 }
259 }
260 iColoumn[0] = 0;
261
262 for (int m = 0; m < nCols; m++)
263 {
264 for (int k = 0; k < nRows-1; k++)
265 if (iWeights[k + 1, m] >= iWeights[k, m])
266 iColoumn[m] = iWeights[k + 1, m];
267 }
268 if (bAutoSetColoumn == true)
269 {
270 for (int c = 0; c < nCols; c++)
271 {
272 string strColoumn = GetColName(c + 1);
273 oSheet.get_Range(strColoumn + "1", strColoumn + "2").ColumnWidth = iColoumn[c] + 3;
274
275 }
276 }
277 Excel.Range StringRange1 = (Excel.Range)oSheet.Cells[nFillRow, nFillCol];
278
279 StringRange1 = StringRange.get_Resize(nRows, nCols);
280 StringRange1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
281 StringRange1.Value2 = strValues;
282 oSheet.get_Range("A" + (nFillRow - 1).ToString(), strCol + "1").Merge(false);
283 oSheet.get_Range("A" + (nFillRow - 1).ToString(), strCol + "1").Font.Size = 15;
284 oSheet.get_Range("A" + (nRows + nFillRow), strCol.ToString() + (nRows + nFillRow).ToString()).Merge(false);
285 oSheet.Cells[nFillRow - 1, nFillCol] = strStart;
286 oSheet.Cells[nFillRow + nRows, nFillCol] = strEnd;
287
288 }
289 catch (System.Exception strExcel)
290 {
291 MessageBox.Show(strExcel.ToString());
292 }
293 finally
294 {
295 }
296
297 }
298
299 public void frmLoaded(string[,] ss, int nType, string strStart, string strEnd,bool bSetColoumnWeight)
300 {
301 int[,] iWeights;//用于得到每个单元格的宽度
302 int[] iColoumn;//得到每列最大宽度
303 try
304 {
305 if (Common.bfrmShowed == true)
306 this.axOA1.Close();
307
308 switch (nType)
309 {
310 case 1: //普通表
311 nFillRow = 2;
312 nFillCol = 1;
313 strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\sample.xls";
314 strSavePath = System.Windows.Forms.Application.StartupPath + "\\其它表\\";
315 break;
316 case 2: //统计表
317 nFillRow = 14;
318 nFillCol = 1;
319 strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\Template.xls";
320 strSavePath = System.Windows.Forms.Application.StartupPath + "\\字段统计表\\";
321 break;
322 case 3:
323 nFillRow = 9;
324 nFillCol = 1;
325 strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\SampleGjx.xls";
326 strSavePath = System.Windows.Forms.Application.StartupPath + "\\检修表\\";
327 break;
328 case 4:
329 nFillRow = 11;
330 nFillCol = 1;
331 strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\SampleGwx.xls";
332 strSavePath = System.Windows.Forms.Application.StartupPath + "\\维修表\\";
333 break;
334 default:
335 break;
336 }
337
338 if (ss == null)
339 {
340 MessageBox.Show("选择集为空,请重新选择对象!");
341 return;
342 }
343 Common.bfrmShowed = true;
344 axOA1.Open(strPath);
345 oBook = (Excel.Workbook)axOA1.GetIDispatch();
346 oExcel = oBook.Application;
347 oSheet = (Excel.Worksheet)oBook.Worksheets[1];
348
349 int nRow = ss.GetLength(0);
350 int nCol = ss.GetLength(1);
351 iWeights = new int[nRow, nCol];
352 iColoumn = new int[nCol];
353
354 string strCol = GetColName(nCol); //调用自定义函数得到列名
355 //合并第一行,到有多少列
356 oSheet.get_Range("A" + (nFillRow - 1).ToString(), strCol + "1").Merge(false);//设置从第填充行的前一行 到列合并 比如合并("A1","E1")
357 //oSheet.get_Range("A" + (nFillRow).ToString(), strCol + "1").Borders.Weight = 15;
358 // oSheet.get_Range("A" + (nFillRow).ToString(), strCol + "1").Cells.RowHeight = 5;
359 oSheet.get_Range("A" + (nFillRow - 1).ToString(), strCol + "1").Font.Size = 15;
360 //加粗边框
361 Excel.Range StringRange = (Excel.Range)oSheet.Cells[nFillRow, nFillCol];//得到要开始加粗的单元格 从第二行每一列开始
362 StringRange = StringRange.get_Resize(ss.GetLength(0), ss.GetLength(1)); //得到要加粗的区域 记录条数,列的范围
363 StringRange.Borders.Weight = 2;
364 StringRange.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
365
366
367 //填充单元格的内容
368 Excel.Range StringRange1 = (Excel.Range)oSheet.Cells[nFillRow, nFillCol];
369
370 StringRange1 = StringRange1.get_Resize(ss.GetLength(0), ss.GetLength(1));
371
372 StringRange1.Value2 = ss;
373 //合并最后一行 到指定列
374 oSheet.get_Range("A" + (nRow + nFillRow), strCol.ToString() + (nRow + nFillRow).ToString()).Merge(false);
375 //设置第一行和最后一行的内容
376 oSheet.Cells[nFillRow - 1, nFillCol] = strStart;
377 oSheet.Cells[nFillRow + nRow, nFillCol] = strEnd;
378
379 for(int i=0;i<nRow ;i++)
380 for(int j=0;j<nCol ;j++)
381 iWeights[i, j] = ss[i, j].Length;//得到各单元格的长度
382 iColoumn[0] = 0;
383
384 for (int m = 0; m < nCol; m++)
385 {
386 for (int k = 0; k < nRow - 1; k++)
387 if (iWeights[k + 1, m] >= iWeights[k, m])
388 iColoumn[m] = iWeights[k + 1, m];
389 }
390 if (bSetColoumnWeight == true)
391 {
392 for (int c = 0; c < nCol; c++)
393 {
394 string strColoumn = GetColName(c + 1);
395 oSheet.get_Range(strColoumn + "1", strColoumn + "2").ColumnWidth = iColoumn[c] + 3;
396
397 }
398 }
399
400 }
401 catch (System.Exception strExcel)
402 {
403 MessageBox.Show(strExcel.ToString());
404 }
405 finally
406 {
407 }
408
409 }
410
411/**///// 取得列名
412//Private Function GetColName(ByVal intNum As Integer) As String
413// Dim strColNames As String
414// Dim strReturn() As String
415 // 通常字段数不会太多,所以到 26*5 目前已经够了。
416 private string GetColName(int iColNum)
417 {
418 string strColNames = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,"
419 +"AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ,"
420 +"BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BY,BZ,"
421 +"CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO,CP,CQ,CR,CS,CT,CU,CV,CW,CX,CY,CZ,"
422 +"DA,DB,DC,DD,DE,DF,DG,DH,DI,DJ,DK,DL,DM,DN,DO,DP,DQ,DR,DS,DT,DU,DV,DW,DX,DY,DZ";
423 //strReturn = Split(strColNames, ",", 130, 0);
424 // string strReturn= strColNames .Split(
425 string []ss=strColNames .Split(new char []{','});
426 return ss[iColNum - 1];
427
428 }
429
430
431 }
432}