- 1.添加引用:
- Aspose.Cells.dll(我们就叫工具包吧,可以从网上下载。关于它的操作我在“Aspose.Cells操作说明 中文版 下载 Aspose C# 导出Excel 实例”一文中的说。这里你暂时也可不理会它。)
- 即使没有安装office也能用噢,这是一个好强的大工具。
- 2.编写Excel操作类
- using System;
- using System.Collections.Generic;
- using System.Text;
- using Aspose.Cells;
- using System.Data;
- public class AsposeExcel
- {
- private string outFileName = "";
- private string fullFilename = "";
- private Workbook book = null;
- private Worksheet sheet = null;
- public AsposeExcel(string outfilename, string tempfilename) //导出构造数
- {
- outFileName = outfilename;
- book = new Workbook();
- // book.Open(tempfilename);这里我们暂时不用模板
- sheet = book.Worksheets[0];
- }
- public AsposeExcel(string fullfilename) //导入构造数
- {
- fullFilename = fullfilename;
- // book = new Workbook();
- // book.Open(tempfilename);
- // sheet = book.Worksheets[0];
- }
- private void AddTitle(string title, int columnCount)
- {
- sheet.Cells.Merge(0, 0, 1, columnCount);
- sheet.Cells.Merge(1, 0, 1, columnCount);
- Cell cell1 = sheet.Cells[0, 0];
- cell1.PutValue(title);
- cell1.Style.HorizontalAlignment = TextAlignmentType.Center;
- cell1.Style.Font.Name = "黑体";
- cell1.Style.Font.Size = 14;
- cell1.Style.Font.IsBold = true;
- Cell cell2 = sheet.Cells[1, 0];
- cell1.PutValue("查询时间:" + DateTime.Now.ToLocalTime());
- cell2.SetStyle(cell1.Style);
- }
- private void AddHeader(DataTable dt)
- {
- Cell cell = null;
- for (int col = 0; col < dt.Columns.Count; col++)
- {
- cell = sheet.Cells[0, col];
- cell.PutValue(dt.Columns[col].ColumnName);
- cell.Style.Font.IsBold = true;
- }
- }
- private void AddBody(DataTable dt)
- {
- for (int r = 0; r < dt.Rows.Count; r++)
- {
- for (int c = 0; c < dt.Columns.Count; c++)
- {
- sheet.Cells[r + 1, c].PutValue(dt.Rows[R][c].ToString());
- }
- }
- }
- //导出------------下一篇会用到这个方法
- public Boolean DatatableToExcel(DataTable dt)
- {
- Boolean yn = false;
- try
- {
- //sheet.Name = sheetName;
- //AddTitle(title, dt.Columns.Count);
- //AddHeader(dt);
- AddBody(dt);
- sheet.AutoFitColumns();
- //sheet.AutoFitRows();
- book.Save(outFileName);
- yn = true;
- return yn;
- }
- catch (Exception e)
- {
- return yn;
- // throw e;
- }
- }
- public DataTable ExcelToDatatalbe()//导入
- {
- Workbook book = new Workbook();
- book.Open(fullFilename);
- Worksheet sheet = book.Worksheets[0];
- Cells cells = sheet.Cells;
- //获取excel中的数据保存到一个datatable中
- DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, false);
- // dt_Import.
- return dt_Import;
- }
- }[/R]
- 3. Word导出
- //设置文件类型
- // saveFileDialog为一个对话框控件
- //如果没有人工具栏中拉,
- //可以:SaveFileDialog saveFileDialog1=new SaveFileDialog();
- saveFileDialog1.Filter = "导出Excel (*.xls)|*.xls|Word (*.doc)|*.doc";
- saveFileDialog1.FilterIndex = 1;
- saveFileDialog1.RestoreDirectory = true;
- saveFileDialog1.CreatePrompt = true;
- saveFileDialog1.Title = "导出文件保存路径";
- //saveFileDialog1.ShowDialog();
- //string strName = saveFileDialog1.FileName;
- //设置默认文件类型显示顺序
- //saveFileDialog1.FilterIndex = 2;
- //保存对话框是否记忆上次打开的目录
- saveFileDialog1.RestoreDirectory = true;
- //点了保存按钮进入
- if (saveFileDialog1.ShowDialog() == DialogResult.OK)
- {
- //获得文件路径
- string localFilePath = saveFileDialog1.FileName.ToString();
- //获取文件名,不带路径
- string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
- //获取文件路径,不带文件名
- string FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
- //给文件名前加上时间
- string newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;
- //在文件名里加字符
- //saveFileDialog1.FileName.Insert(1,"dameng");
- saveFileDialog1.FileName = FilePath + "\\" + newFileName;
- System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog1.OpenFile();//输出文件
- StreamWriter writer = new StreamWriter(fs);
- writer.Write("tttt");//这里就是你要导出到word的内容,内容是你什么你自已DIY
- writer.Flush();
- writer.Close();
- fs.Close();
- }
- 4. 导出datatable到excel
- DataTable dt = null;
- if (ds_all.Tables[0] != null)
- {
- dt = ds_all.Tables[0];
- }
- else {
- MessageBox.Show("没有数据记录", "*^_^* 温馨提示信息", MessageBoxButtons.OK);
- return;
- }
- //上面只是取datatable,你自己diy
- AsposeExcel tt = new AsposeExcel(saveFileDialog1.FileName, "");//不用模板, saveFileDialog1是什么?上面已经说过
- bool OK_NO = tt.DatatableToExcel(dt);
- if (OK_NO)
- {
- MessageBox.Show("导出成功", "*^_^* 温馨提示信息", MessageBoxButtons.OK);
- }
- else
- {
- }
- 5. Excel导入
- private void 导入ToolStripMenuItem_Click(object sender, EventArgs e)
- {
- string localFilePath = "";
- //点了保存按钮进入
- if (openFileDialog1.ShowDialog() == DialogResult.OK)// openFileDialog1不要再问我这是什么!
- {
- //获得文件路径
- localFilePath = openFileDialog1.FileName.ToString();
- }
- AsposeExcel tt = new AsposeExcel(localFilePath);
- DataTable dt;
- try
- {
- dt = tt.ExcelToDatatalbe();
- }
- catch (Exception ex)
- {
- return;
- }
- //有了datatable你自己就可以DIY啦,下面是我自己的你不用理
- if (ddlResidence.SelectedValue == "违章确认")
- {
- if (dt.Rows[0][9].ToString() != "违章确认")
- {
- return;
- }
- row = dt.Rows.Count;
- if (row <= 0) return;
- for (int i = 0; i < dt.Rows.Count; i++)
- {
- bllviola.Up_Confirmed_ByVnum(dt.Rows[i][6].ToString(), dt.Rows[i][9].ToString());
- }
- this.GridView1.DataSource = dt;
- GridView1.DataBind();
- }
C# WinForm 导出导入Excel/Doc 完整实例教程[使用Aspose.Cells.dll]
最新推荐文章于 2023-05-10 13:57:40 发布