从DataGrid导出数据到Excel,用代码控制:
1:能不能设置Excel的分页,比如说Excel就显示10条数据,剩下的另起一页,标头栏也要带上。
2:数据显示在Excel的样式怎么调?比如说这排数据居中,那排靠右的。谢谢大家的解答了!
1:能不能设置Excel的分页,比如说Excel就显示10条数据,剩下的另起一页,标头栏也要带上。
2:数据显示在Excel的样式怎么调?比如说这排数据居中,那排靠右的。谢谢大家的解答了!
解决方案 »
- discuz整合问题,知道的都进来看看。
- js 调用iframe(在Gridvieiw 中加载的)中Button的onclick事件(100分 急!)
- PageRequestManager的问题,大侠们救命啊
- 我要控制页面中的"待审核人"字符串不打印
- 如何使Panel随着动态加载的ascx的尺寸而伸缩
- 视频面试!
- 高分求助Viewstate 無效,而且可能已經損毀的問題!Thanks
- 在asp.net(c#)中怎样使数据库连接超过一定时间(比如说3秒)若是连不上就停止连接,继续执行下面的代码?
- webform 下载附件问题
- LDAP连接一直出错
- 已有打开的与此命令相关联的 DataReader,必须首先将它关闭
- asp.net 运行cmd.exe 放服务器上就有问题
2using System.IO;
3using System.Data;
4using System.Reflection;
5using System.Diagnostics;
6using cfg = System.Configuration;
7//using Excel;
8
9namespace ExcelHelperTest
10{
11 /**//// <summary>
12 /// 功能说明:套用模板输出Excel,并对数据进行分页
13 /// 作 者:Lingyun_k
14 /// 创建日期:2005-7-12
15 /// </summary>
16 public class ExcelHelper
17 {
18 protected string templetFile = null;
19 protected string outputFile = null;
20 protected object missing = Missing.Value;
21
22 /**//// <summary>
23 /// 构造函数,需指定模板文件和输出文件完整路径
24 /// </summary>
25 /// <param name="templetFilePath">Excel模板文件路径</param>
26 /// <param name="outputFilePath">输出Excel文件路径</param>
27 public ExcelHelper(string templetFilePath,string outputFilePath)
28 {
29 if(templetFilePath == null)
30 throw new Exception("Excel模板文件路径不能为空!");
31
32 if(outputFilePath == null)
33 throw new Exception("输出Excel文件路径不能为空!");
34
35 if(!File.Exists(templetFilePath))
36 throw new Exception("指定路径的Excel模板文件不存在!");
37
38 this.templetFile = templetFilePath;
39 this.outputFile = outputFilePath;
40
41 }
42
44 /// 将DataTable数据写入Excel文件(套用模板并分页)
45 /// </summary>
46 /// <param name="dt">DataTable</param>
47 /// <param name="rows">每个WorkSheet写入多少行数据</param>
48 /// <param name="top">行索引</param>
49 /// <param name="left">列索引</param>
50 /// <param name="sheetPrefixName">WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2”</param>
51 public void DataTableToExcel(DataTable dt,int rows,int top,int left,string sheetPrefixName)
52 {
53 int rowCount = dt.Rows.Count; //源DataTable行数
54 int colCount = dt.Columns.Count; //源DataTable列数
55 int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数
56 DateTime beforeTime;
57 DateTime afterTime;
58
59 if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
60 sheetPrefixName = "Sheet";
61
62 //创建一个Application对象并使其可见
63 beforeTime = DateTime.Now;
64 Excel.Application app = new Excel.ApplicationClass();
65 app.Visible = true;
66 afterTime = DateTime.Now;
67
68 //打开模板文件,得到WorkBook对象
69 Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
70 missing,missing,missing,missing,missing,missing,missing);
71
72 //得到WorkSheet对象
73 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
74
75 //复制sheetCount-1个WorkSheet对象
76 for(int i=1;i<sheetCount;i++)
77 {
78 ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
79 }
80
81 将源DataTable数据写入Excel#region 将源DataTable数据写入Excel
82 for(int i=1;i<=sheetCount;i++)
83 {
84 int startRow = (i - 1) * rows; //记录起始行索引
85 int endRow = i * rows; //记录结束行索引
86
87 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
88 if(i == sheetCount)
89 endRow = rowCount;
90
91 //获取要写入数据的WorkSheet对象,并重命名
92 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
93 sheet.Name = sheetPrefixName + "-" + i.ToString();
94
95 //将dt中的数据写入WorkSheet
96 for(int j=0;j<endRow-startRow;j++)
97 {
98 for(int k=0;k<colCount;k++)
99 {
100 sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
101 }
102 }
103
104 //写文本框数据
105 Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
106 Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
107 Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
108
109 txtAuthor.Text = "KLY.NET的Blog";
110 txtDate.Text = DateTime.Now.ToShortDateString();
111 txtVersion.Text = "1.0.0.0";
112 }
113 #endregion
114
115 //输出Excel文件并退出
116 try
117 {
118 workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
119 workBook.Close(null,null,null);
120 app.Workbooks.Close();
121 app.Application.Quit();
122 app.Quit();
123
124 System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
125 System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
126 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
127
128 workSheet=null;
129 workBook=null;
130 app=null;
131
132 GC.Collect();
133 }
134 catch(Exception e)
135 {
136 throw e;
137 }
138 finally
139 {
140 Process[] myProcesses;
141 DateTime startTime;
142 myProcesses = Process.GetProcessesByName("Excel");
143
144 //得不到Excel进程ID,暂时只能判断进程启动时间
145 foreach(Process myProcess in myProcesses)
146 {
147 startTime = myProcess.StartTime;
148
149 if(startTime > beforeTime && startTime < afterTime)
150 {
151 myProcess.Kill();
152 }
153 }
154 }
155
156 }
157
158
160 /// 获取WorkSheet数量
161 /// </summary>
162 /// <param name="rowCount">记录总行数</param>
163 /// <param name="rows">每WorkSheet行数</param>
164 private int GetSheetCount(int rowCount,int rows)
165 {
166 int n = rowCount % rows; //余数
167
168 if(n == 0)
169 return rowCount / rows;
170 else
171 return Convert.ToInt32(rowCount / rows) + 1;
172 }
173
174
175 /**//// <summary>
176 /// 将二维数组数据写入Excel文件(套用模板并分页)
177 /// </summary>
178 /// <param name="arr">二维数组</param>
179 /// <param name="rows">每个WorkSheet写入多少行数据</param>
180 /// <param name="top">行索引</param>
181 /// <param name="left">列索引</param>
182 /// <param name="sheetPrefixName">WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2”</param>
183 public void ArrayToExcel(string[,] arr,int rows,int top,int left,string sheetPrefixName)
184 {
185 int rowCount = arr.GetLength(0); //二维数组行数(一维长度)
186 int colCount = arr.GetLength(1); //二维数据列数(二维长度)
187 int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数
188 DateTime beforeTime;
189 DateTime afterTime;
190
191 if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
192 sheetPrefixName = "Sheet";
193
194 //创建一个Application对象并使其可见
195 beforeTime = DateTime.Now;
196 Excel.Application app = new Excel.ApplicationClass();
197 app.Visible = true;
198 afterTime = DateTime.Now;
199
200 //打开模板文件,得到WorkBook对象
201 Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
202 missing,missing,missing,missing,missing,missing,missing);
203
204 //得到WorkSheet对象
205 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
206
207 //复制sheetCount-1个WorkSheet对象
208 for(int i=1;i<sheetCount;i++)
209 {
210 ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
211 }
212
213 将二维数组数据写入Excel#region 将二维数组数据写入Excel
214 for(int i=1;i<=sheetCount;i++)
215 {
216 int startRow = (i - 1) * rows; //记录起始行索引
217 int endRow = i * rows; //记录结束行索引
218
219 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
220 if(i == sheetCount)
221 endRow = rowCount;
222
223 //获取要写入数据的WorkSheet对象,并重命名
224 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
225 sheet.Name = sheetPrefixName + "-" + i.ToString();
226
227 //将二维数组中的数据写入WorkSheet
228 for(int j=0;j<endRow-startRow;j++)
229 {
230 for(int k=0;k<colCount;k++)
231 {
232 sheet.Cells[top + j,left + k] = arr[startRow + j,k];
233 }
234 }
235
236 Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
237 Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
238 Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
239
240 txtAuthor.Text = "KLY.NET的Blog";
241 txtDate.Text = DateTime.Now.ToShortDateString();
242 txtVersion.Text = "1.0.0.0";
243 }
244 #endregion
245
246 //输出Excel文件并退出
247 try
248 {
249 workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
250 workBook.Close(null,null,null);
251 app.Workbooks.Close();
252 app.Application.Quit();
253 app.Quit();
254
255 System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
256 System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
257 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
258
259 workSheet=null;
260 workBook=null;
261 app=null;
262
263 GC.Collect();
264 }
265 catch(Exception e)
266 {
267 throw e;
268 }
269 finally
270 {
271 Process[] myProcesses;
272 DateTime startTime;
273 myProcesses = Process.GetProcessesByName("Excel");
274
275 //得不到Excel进程ID,暂时只能判断进程启动时间
276 foreach(Process myProcess in myProcesses)
277 {
278 startTime = myProcess.StartTime;
279
280 if(startTime > beforeTime && startTime < afterTime)
281 {
282 myProcess.Kill();
283 }
284 }
285 }
286
287 }
288 }
289}
290
using System.Web;
using Excel = Microsoft.Office.Interop.Excel; public class ExcelOperate
{
private object mValue = System.Reflection.Missing.Value; public ExcelOperate()
{
//
// TODO: 在此处添加构造函数逻辑
//
} public void WriteCell(Excel._Worksheet CurSheet,object objCell, object objValue)
{
CurSheet.get_Range(objCell, mValue).Value2 = objValue; }
/// <summary>
/// 合并单元格,并在合并后的单元格中插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteAfterMerge(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, object objValue)
{
CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
CurSheet.get_Range(objStartCell, mValue).Value2 = objValue; } /// <summary>
/// 为单元格设置公式
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objCell">单元格</param>
/// <param name="strFormula">公式</param>
public void SetFormula(Excel._Worksheet CurSheet,object objCell, string strFormula)
{
CurSheet.get_Range(objCell, mValue).Formula = strFormula;
}
/// <summary>
/// 单元格自动换行
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void AutoWrapText(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell,objEndCell).WrapText=true;
} /// <summary>
/// 设置整个连续区域的字体颜色
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="clrColor">颜色</param>
public void SetColor(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, System.Drawing.Color clrColor)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
}
public void SetBorderAll(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; } /// <summary>
/// 保存文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveAs(Excel._Workbook CurBook,string strFilePath)
{
CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlShared, mValue, mValue, mValue, mValue, mValue);
} /// <summary>
/// 另存为html文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveHtml(Excel._Workbook CurBook,string strFilePath)
{
CurBook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Excel._Worksheet CurSheet,Excel._Workbook CurBook,Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null; CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch(System.Exception ex)
{
HttpContext.Current.Response.Write( "在释放Excel内存空间时发生了一个错误:"+ex);
}
finally
{
foreach(System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
System.GC.SuppressFinalize(this); }
}