C#导出数据到Excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using MOIE = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Threading;

namespace HotelManagement
{
    public class CheckInformationOperation
    {
        public bool Export(DataGridView dgv, string filePath, string fileName)
        {
            //实例化对象
            MOIE.Application excel = new MOIE.Application();
            //添加工作表
            excel.SheetsInNewWorkbook = 1;
            //将表添加到excel对象中
            excel.Workbooks.Add();
            //获取当前工作表
            MOIE.Worksheet sheet = (MOIE.Worksheet)excel.ActiveWorkbook.Worksheets[1];

            sheet.Name = "客户登记信息表";

            excel.Cells[1, 1] = "客户姓名";
            excel.Cells[1, 2] = "身份证号";
            excel.Cells[1, 3] = "入住日期";
            excel.Cells[1, 4] = "定金";
            excel.Cells[1, 5] = "状态";
            //对表头的操作
            //MOIE.Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 5]);
            MOIE.Range range = excel.Range[excel.Cells[1, 1], excel.Cells[1, 5]];
            range.Borders.LineStyle = MOIE.XlLineStyle.xlContinuous;
            range.Font.Bold = true;

            //添加数据
            int i = 2;
            foreach (DataGridViewRow row in dgv.Rows)
            {
                excel.Cells[i, 1] = row.Cells["CustomerName"].FormattedValue;
                excel.Cells[i, 2] = row.Cells["IdentityCard"].FormattedValue;              
                excel.Cells[i, 3] = row.Cells["CheckInDate"].FormattedValue;
                excel.Cells[i, 4] = row.Cells["Deposit"].FormattedValue;
                excel.Cells[i, 5] = row.Cells["CheckInStatusName"].FormattedValue;
                i++;
            }
            //设置每一列的数据格式
            excel.Range[excel.Cells[2, 2], excel.Cells[i, 2]].NumberFormatLocal = "0";//字符
            excel.Range["D2", Type.Missing].NumberFormatLocal = "#,##0.00";//货币
            excel.Range["C2", Type.Missing].NumberFormatLocal = "yyyy-MM-dd hh:mm";//日期

            //excel.Visible = true;
            //Thread.Sleep(3000);
            //excel.Visible = false;

            //判断文件名是否已经存在,若存在,删除文件后保存
            if (File.Exists(filePath + "/" + fileName))
            {
                File.Delete(filePath + "/" + fileName);
            }
            //保存
            //excel.ActiveWorkbook.SaveAs(filePath + "/" + fileName, MOIE.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, MOIE.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8:Excel97-2003格式
            //Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12:Excel2007格式
            //excel.ActiveWorkbook.SaveAs(filePath + "/" + fileName, MOIE.XlFileFormat.xlExcel12, Type.Missing,
            //       Type.Missing, Type.Missing, Type.Missing,
            //       MOIE.XlSaveAsAccessMode.xlNoChange, Type.Missing,
            //       Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            excel.ActiveWorkbook.SaveAs(filePath + "/" + fileName, MOIE.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, MOIE.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            
            //退出
            excel.Quit();

            //杀死进程
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            excel = null;

            return true;
        }
    }
}

知识共享许可协议
《C#导出数据到Excel》常伟华 创作。
采用 知识共享 署名-相同方式共享 3.0 中国大陆 许可协议进行许可。
相邻依据:发表时间
  • 多说评论
  • 签名
  • 新浪微博
  • 默认评论
  • Tab Header 5

0 条评论 / 点击此处发表评论

Tab Content 5

开发技术


开发平台和工具

sitemap     149.87ms