C#读取和导出到Excel简单实例

最新推荐文章于 2024-06-11 17:27:55 发布

Andy__Huang

最新推荐文章于 2024-06-11 17:27:55 发布

阅读量4.5k

点赞数

分类专栏： .NET程序文章标签：导出Excel

本文链接：https://blog.csdn.net/hdhai9451/article/details/47356235

版权

一、导出Excel

public enum ReportTemplateMode
{
    WebOrder,      
    WebOrderDetails,
    WebSend,
    WebSendDetails,
}


public class TemplateFileUtility
{
    public static string CreateTemplateFile(ReportTemplateMode model)
    {
        string filePath = HttpContext.Current.Server.MapPath("~/Template");
        string templateFile = string.Empty;
        string createFile = string.Empty;
        switch (model)
        {
            case ReportTemplateMode.WebOrder:
            case ReportTemplateMode.WebOrderDetails:
            case ReportTemplateMode.WebSend:
            case ReportTemplateMode.WebSendDetails:
                createFile = Guid.NewGuid() + ".xls";
                templateFile = Path.Combine(filePath, model.ToString() + ".xls");
                break;
            default:
                createFile = Guid.NewGuid() + ".xls";
                templateFile = Path.Combine(filePath, model.ToString() + ".xls");
                break;
        }

        createFile = Path.Combine(GetTempFile(), createFile);
        if (!File.Exists(templateFile))
        {
            throw new Exception("Find Not TemplateFile Path: " + templateFile);
        }
        File.Copy(templateFile, createFile, true);
        File.SetAttributes(createFile, FileAttributes.Normal);

        return createFile;
    }
    public static string GetTempFile()
    {
        string tempFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Temp");
        if (!System.IO.Directory.Exists(tempFile))
        {
            System.IO.Directory.CreateDirectory(tempFile);
        }
        return tempFile;
    }

}



//点击"导出"按钮
protected void btn_Export_OnClick(object sender, EventArgs e)
{
    GC.Collect();

    string targetExcelFileName = TemplateFileUtility.CreateTemplateFile(ReportTemplateMode.WebOrder);
    OrderSearchFilter filter = GetFilter();
    DataTable dtbl = BizOrder.ExportOrderListByFilter(filter);

    //Excel模型，用来操作Excel文件
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook excelWb = null;
    Microsoft.Office.Interop.Excel.Worksheet excelWs = null;
    Microsoft.Office.Interop.Excel.Range cellR = null;
    try
    {
        //打开Excel工作簿文件                
        excelWb = excel.Workbooks.Open(targetExcelFileName, System.Type.Missing, false, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
        excelWs = (Microsoft.Office.Interop.Excel.Worksheet)(excelWb.Sheets.get_Item(1));   //选择工作簿中第一个工作表

        //查询条件
        excelWs.Cells[2, 3] = this.txt_Keywords.Value;
        excelWs.Cells[3, 3] = GetSearchDate();
        excelWs.Cells[2, 12] = this.ddl_OrderState.SelectedIndex == 0 ? string.Empty : this.ddl_OrderState.SelectedItem.Text;