C#没有装office进行Excel的导出

现在的位置: 首页 > 综合 > 正文

C#没有装office进行Excel的导出

2012年03月15日 ⁄ 综合 ⁄ 共 14015字 ⁄ 字号小中大 ⁄ 评论关闭

导出Excel的格式

//标题的定义
            ExcelHead[] excel ={
             new ExcelHead() { Title = "时间", Field = "Time", TypeCell = TypeEnum.String },
             new ExcelHead() { Title = "编号", Field = "Number", TypeCell = TypeEnum.String },
             new ExcelHead() { Title = "计划", Field = "Plan", TypeCell = TypeEnum.String },
            new ExcelHead() { Title = "完成情况", Field = "Completion", TypeCell = TypeEnum.String },
            new ExcelHead() { Title = "是否完成", Field = "IsComplete", TypeCell = TypeEnum.String },
            new ExcelHead() { Title = "星期", Field = "Week", TypeCell = TypeEnum.String }
         };
            ExcelSheet sheet = new ExcelSheet("工作日志", excel);
            ExcelSet set = new ExcelSet();//创建excel
            set.add(sheet);//工作簿名称，必填
            set.Name = "dsa";//excel名称，必填
            foreach (WorkLogMode item in listWorkMode)
            {
                ExcelRow row = new ExcelRow();
                ExcelCell cellTime = new ExcelCell("Time", item.Time);
                ExcelCell cellNumber = new ExcelCell("Number", item.Number);
                ExcelCell cellPlan = new ExcelCell("Plan", item.Plan);
                ExcelCell cellCompletion = new ExcelCell("Completion", item.Completion);
                ExcelCell cellIsComplete = new ExcelCell("IsComplete", item.IsComplete);
                int iWeek = (int)Convert.ToDateTime(item.Time).DayOfWeek;
                //星期几
                string week = DateHandle.Week(iWeek);
                ExcelCell cellWeek = new ExcelCell("Week", week);
                row.add(cellTime);
                row.add(cellNumber);
                row.add(cellPlan);
                row.add(cellCompletion);
                row.add(cellIsComplete);
                row.add(cellWeek);
                sheet.add(row);
            }
            set.Save(path);

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
/// <summary>
///Excel的单元格
/// </summary>
public class ExcelCell : IExcelCell
{
    public string Key { set; get; }
    public string Value { set; get; }
    public ExcelCell(string key,string value)
    {
        Key = key;
        Value = value; 
    }
    public ExcelCell(string key, string value, string styleID)
    {
        Key = key;
        Value = value;
        StyleID = styleID;
    }
    public ExcelCell(string key, string value, TypeEnum typeCell, string styleID)
    {
        Key = key;
        Value = value;
        TypeCell = typeCell;
        StyleID = styleID;
    }
    public ExcelCell(string key, string value, TypeEnum typeCell)
    {
        Key = key;
        Value = value;
        TypeCell = typeCell;
    }
    public ExcelCell(string key, string value, TypeEnum typeCell, Dictionary<string, string> displayContent)
    {
        Key = key;
        Value = value;
        TypeCell = typeCell;
        DisplayContent = displayContent;
    }
    public StringBuilder getCell()
    {
        StringBuilder builderCell=new StringBuilder();
        if (StyleID==null)//查看有无样式
        {
            builderCell.Append("<Cell>");
        }
        else
        {
            builderCell.Append("<Cell ss:StyleID=\"" + StyleID + "\">");
        }
        if (TypeCell.ToString()=="")
        {
            TypeCell = TypeEnum.String;
        }
        string value = Value;
        if (DisplayContent!=null)
        {
            foreach (KeyValuePair<string, string> key1 in DisplayContent)
            {
                if (key1.Key.ToUpper()==Value.ToUpper())
                {
                    value=key1.Value;
                }
            }
        }
        builderCell.Append(string.Format("<Data ss:Type=\"{0}\">{1}</Data>", TypeCell, value));
      
        
        builderCell.Append("</Cell>");
        return builderCell;
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;

/// <summary>
///ExcelData 的摘要说明
/// </summary>
public class ExcelHead:IExcelCell
{
    /// <summary>
    /// 标题
    /// </summary>
    public string Title { set; get; }
    /// <summary>
    /// 字段
    /// </summary>
    public string Field { set; get; } 

    public ExcelHead(string title, string field, TypeEnum type)
    {
        
        Title = title;
        field = Field;
        TypeCell = type;
    }
    public ExcelHead(string title, string field, TypeEnum type, Dictionary<string, string> displayContent)
    {
        Title = title;
        field = Field;
        TypeCell = type;
        DisplayContent = displayContent;
    }
    public ExcelHead(string title, string field)
    {
        Title = title;
        field = Field;
        TypeCell = TypeEnum.String;
    }
    public ExcelHead(string title, string field, Dictionary<string, string> displayContent)
    {
        Title = title;
        field = Field;
        TypeCell = TypeEnum.String;
        DisplayContent = displayContent;
    }
    public ExcelHead() { }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;

/// <summary>
///ExcelRow 的摘要说明
/// </summary>
public class ExcelRow
{
    private List<ExcelCell> listCell;
    public ExcelRow()
    {
        listCell = new List<ExcelCell>();
    }
    public void add(ExcelCell cell)
    {
        listCell.Add(cell); 
    }
    public int count()
    {
        return listCell.Count;
    }
    public StringBuilder getRow(ExcelHead[] head)
    {
        StringBuilder builderRow = new StringBuilder();
        builderRow.Append(" <Row ss:AutoFitHeight=\"0\">");
        for (int i = 0; i < head.Length; i++)
        {
            for (int j = 0; j < listCell.Count; j++)
            {
                if (head[i].Field == listCell[j].Key)
                {
                    ExcelCell cell = listCell[j];
                    cell.DisplayContent = head[i].DisplayContent;
                    builderRow.Append(cell.getCell());
                }
            }
        }
        builderRow.Append("</Row>");
        return builderRow;
    }
    public ExcelCell[] cells
    {
        get
        {
            ExcelCell[] cell = new ExcelCell[listCell.Count];
            for (int i = 0; i < listCell.Count; i++)
            {
                cell[i] = listCell[i];
            }
            return cell;
        }
    }
    public ExcelCell this[string StrCell]
    {
        get
        {
            ExcelCell cell = listCell.Find(c => c.Key == StrCell);
            return cell;
        }
    }
    public ExcelCell this[int intCell]
    {
        get
        {
            ExcelCell cell = listCell[intCell];
            return cell;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Runtime.Serialization;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Diagnostics;

/// <summary>
///ExcelSet 的摘要说明
/// </summary>
public class ExcelSet
{
    public string Name { set; get; }
    private StringBuilder OutFileContent = new StringBuilder();
    private List<ExcelSheet> listSheet;
    public ExcelSet()
    {
        listSheet = new List<ExcelSheet>();
    }
    public void add(ExcelSheet sheet)
    {
        listSheet.Add(sheet);
    }
    public void Save(string path)
    {
        OutFileContent = AddHeadFile(OutFileContent);
        for (int i = 0; i < listSheet.Count; i++)
        {
            OutFileContent.Append(listSheet[i].getExcelSheet());
        }
        OutFileContent = AddEndFile(OutFileContent);
        IFormatter formatter = new BinaryFormatter();
        //创建一个文件流
        FileStream stream = new FileStream(path,
            FileMode.OpenOrCreate, FileAccess.Write, FileShare.None);
        StreamWriter mySw = new StreamWriter(stream);
        string a= stream.Name;
        
        mySw.Write(OutFileContent);
        
        mySw.Close(); 
        //formatter.Serialize(stream, OutFileContent);
        stream.Dispose();
        stream.Close();
    }
    //public void show()
    //{
    //    OutFileContent = AddHeadFile(OutFileContent);
    //    for (int i = 0; i < listSheet.Count; i++)
    //    {
    //        OutFileContent.Append(listSheet[i].getExcelSheet());
    //    }
    //    OutFileContent = AddEndFile(OutFileContent);
    //    HttpContext.Current.Response.Clear();
    //    HttpContext.Current.Response.Buffer = true;
    //    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
    //    string httpType = HttpContext.Current.Request.Browser.Browser;
    //    if (httpType == "IE")
    //    {
    //        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" +
    //        System.Web.HttpUtility.UrlEncode(Name, System.Text.Encoding.UTF8) + ".xls");
    //    }
    //    else if (httpType == "Firefox")
    //    {
    //        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + Name + ".xls");
    //    }
    //    HttpContext.Current.Response.Charset = "GB2312";
    //    HttpContext.Current.Response.Write(OutFileContent.ToString());
    //    HttpContext.Current.Response.End();
    //}
    /// <summary>
    ///excel表头
    /// </summary>
    /// <param name="OutFileContent"></param>
    /// <returns></returns>
    private static StringBuilder AddHeadFile(StringBuilder OutFileContent)
    {
        OutFileContent.Append("<?xml version=\"1.0\"?>\r\n");
        OutFileContent.Append("<?mso-application progid=\"Excel.Sheet\"?>\r\n");
        OutFileContent.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
        OutFileContent.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n");
        OutFileContent.Append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n");
        OutFileContent.Append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
        OutFileContent.Append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">\r\n");
        OutFileContent.Append(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n");
        OutFileContent.Append("  <Author>panss</Author>\r\n");
        OutFileContent.Append("  <LastAuthor>Оґ¶ЁТе</LastAuthor>\r\n");
        OutFileContent.Append("  <Created>2004-12-31T03:40:31Z</Created>\r\n");
        OutFileContent.Append("  <Company>Prcedu</Company>\r\n");
        OutFileContent.Append("  <Version>12.00</Version>\r\n");
        OutFileContent.Append(" </DocumentProperties>\r\n");
        OutFileContent.Append(" <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n");
        OutFileContent.Append("  <DownloadComponents/>\r\n");
        //OutFileContent.Append("  <LocationOfComponents HRef=\"file:///F:\\Tools\\OfficeXP\\OfficeXP\\\"/>\r\n");
        OutFileContent.Append(" </OfficeDocumentSettings>\r\n");
        OutFileContent.Append(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n");
        OutFileContent.Append("  <WindowHeight>9000</WindowHeight>\r\n");
        OutFileContent.Append("  <WindowWidth>10620</WindowWidth>\r\n");
        OutFileContent.Append("  <WindowTopX>480</WindowTopX>\r\n");
        OutFileContent.Append("  <WindowTopY>45</WindowTopY>\r\n");
        OutFileContent.Append("  <ProtectStructure>False</ProtectStructure>\r\n");
        OutFileContent.Append("  <ProtectWindows>False</ProtectWindows>\r\n");
        OutFileContent.Append(" </ExcelWorkbook>\r\n");
        OutFileContent.Append(" <Styles>\r\n");
        OutFileContent.Append("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n");
        OutFileContent.Append("   <Alignment ss:Vertical=\"Center\" />\r\n");
        OutFileContent.Append("   <Borders/>\r\n");
        OutFileContent.Append("   <Font ss:FontName=\"ЛОМе\" x:CharSet=\"134\" ss:Size=\"12\"/>\r\n");
        OutFileContent.Append("   <Interior/>\r\n");
        OutFileContent.Append("   <NumberFormat/>\r\n");
        OutFileContent.Append("   <Protection/>\r\n");
        OutFileContent.Append("  </Style>\r\n");
        OutFileContent.Append("  <Style ss:ID=\"s62\">\r\n");
        OutFileContent.Append("   <Alignment ss:Vertical=\"Center\" ss:Horizontal=\"Center\" ss:WrapText=\"1\"/>\r\n");
        OutFileContent.Append("   <Font ss:FontName=\"ЛОМе\" x:CharSet=\"134\" ss:Size=\"9\"/>\r\n");
        OutFileContent.Append("  </Style>\r\n");
        OutFileContent.Append("  <Style ss:ID=\"s74\">\r\n");
        OutFileContent.Append("   <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\r\n");
        OutFileContent.Append("   <Borders>\r\n");
        OutFileContent.Append("  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n");
        OutFileContent.Append("  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n");
        OutFileContent.Append("  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n");
        OutFileContent.Append("  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n");
        OutFileContent.Append("  </Borders>\r\n");
        OutFileContent.Append("   <Font ss:FontName=\"ЛОМе\" x:CharSet=\"134\" ss:Size=\"12\" ss:Bold=\"1\"/>\r\n");
        OutFileContent.Append("   <Interior ss:Color=\"#BFBFBF\" ss:Pattern=\"Solid\"/>\r\n");
        OutFileContent.Append("  </Style>\r\n");
        OutFileContent.Append(" </Styles>\r\n");
        return OutFileContent;
    }
    /// <summary>
    /// excel表尾
    /// </summary>
    /// <param name="OutFileContent"></param>
    /// <returns></returns>
    private static StringBuilder AddEndFile(StringBuilder OutFileContent)
    {
        OutFileContent.Append("</Workbook>\r\n");
        return OutFileContent;
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
/// <summary>
///ExcelSeet 的摘要说明
/// </summary>
public class ExcelSheet
{
    /// <summary>
    /// 标题
    /// </summary>
    public ExcelHead[] Head;
    public string Name { set; get; }
    /// <summary>
    ///是否添加表格样式
    /// </summary> 
    public bool IsTableStyle { set; get; }
    public ExcelSheet(string name, ExcelHead[] head)
    {
        Head = head;
        Name = name;
    }
    public ExcelSheet(string name, ExcelHead[] head, DataTable dt)
    {
        Head = head;
        Name = name;
        //写内容
        foreach (DataRow row in dt.Rows)
        {
            ExcelRow excelRow = new ExcelRow();
            for (int i = 0; i < Head.Length; i++)
            {
                if (Head[i].TypeCell.Equals(""))
                {
                    Head[i].TypeCell = TypeEnum.String;
                }
                ExcelCell cell = new ExcelCell(Head[i].Field, row[Head[i].Field].ToString(), Head[i].TypeCell);
                excelRow.add(cell);
            }
            listRow.Add(excelRow);
        }
    }
    public ExcelSheet(DataTable dt) 
    {
        foreach (DataRow row in dt.Rows)
        {
            ExcelRow excelRow = new ExcelRow();
            for (int i = 0; i < Head.Length; i++)
            {
                if (Head[i].TypeCell.Equals(""))
                {
                    Head[i].TypeCell = TypeEnum.String;
                }
                ExcelCell cell = new ExcelCell(Head[i].Field, row[Head[i].Field].ToString(), Head[i].TypeCell);
                excelRow.add(cell);
            }
            listRow.Add(excelRow);
        }
    }
    private List<ExcelRow> listRow = new List<ExcelRow>();

    private StringBuilder OutFileContent = new StringBuilder();
    public void add(ExcelRow row) {
        ExcelRow excelRow = new ExcelRow();
        for (int i = 0; i < row.cells.Count(); i++)
        {
            ExcelCell cell = row.cells[i];
            excelRow.add(cell);
        }

        listRow.Add(excelRow);
    }
    public StringBuilder getExcelSheet()
    {
        OutFileContent = AddHeadFile(OutFileContent);
        OutFileContent.Append("<Row ss:AutoFitHeight=\"0\">");
        for (int i = 0; i < Head.Length; i++)
        {
            OutFileContent.Append("<Cell><Data ss:Type=\"String\">" + Head[i].Title + "</Data></Cell>");
        }
        OutFileContent.Append("</Row>");
        for (int i = 0; i < listRow.Count; i++)
        {
            OutFileContent.Append(listRow[i].getRow(Head));
        }
        OutFileContent = AddEndFile(OutFileContent);
        return OutFileContent;
    }
    public ExcelRow[] rows
    {
        get
        {
            ExcelRow[] row = new ExcelRow[listRow.Count];
            for (int i = 0; i < listRow.Count; i++)
            {
                row[i] = listRow[i];
            }
            return row;
        }
    }
    private StringBuilder AddHeadFile(StringBuilder OutFileContent)
    {
        if (Name==null)
        {
            Name = "Sheet";
        }
        OutFileContent.Append(" <Worksheet ss:Name=\"" + Name + "\">\r\n");
        OutFileContent.Append("  <Table ss:ExpandedColumnCount=\"255\" x:FullColumns=\"1\" \r\n");
        OutFileContent.Append("x:FullRows=\"1\" ss:StyleID=\"s62\" ss:DefaultColumnWidth=\"75\" ss:DefaultRowHeight=\"20.25\">\r\n");
        OutFileContent.Append("<Column ss:StyleID=\"s62\" ss:AutoFitWidth=\"0\" ss:Width=\"112.5\"/>\r\n");
        return OutFileContent;
    }
    private StringBuilder AddEndFile(StringBuilder OutFileContent)
    {
        OutFileContent.Append("</Table>\r\n");
        OutFileContent.Append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n");
        OutFileContent.Append("<Unsynced/>\r\n");
        OutFileContent.Append("<Print>\r\n");
        OutFileContent.Append("    <ValidPrinterInfo/>\r\n");
        OutFileContent.Append("    <PaperSizeIndex>9</PaperSizeIndex>\r\n");
        OutFileContent.Append("    <HorizontalResolution>600</HorizontalResolution>\r\n");
        OutFileContent.Append("    <VerticalResolution>0</VerticalResolution>\r\n");
        OutFileContent.Append("</Print>\r\n");
        OutFileContent.Append("<Selected/>\r\n");
        OutFileContent.Append("<Panes>\r\n");
        OutFileContent.Append("    <Pane>\r\n");
        OutFileContent.Append("    <Number>3</Number>\r\n");
        OutFileContent.Append("    <RangeSelection>R1:R65536</RangeSelection>\r\n");
        OutFileContent.Append("    </Pane>\r\n");
        OutFileContent.Append("</Panes>\r\n");
        OutFileContent.Append("<ProtectObjects>False</ProtectObjects>\r\n");
        OutFileContent.Append("<ProtectScenarios>False</ProtectScenarios>\r\n");
        OutFileContent.Append("</WorksheetOptions>\r\n");
        OutFileContent.Append("</Worksheet>\r\n");
        return OutFileContent;
    }
}

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

    public class IExcelCell
    {
      /// <summary>
      /// 样式的ID
      /// </summary>
        public string StyleID { set; get; }
        /// <summary>
        /// 单元格的格式
        /// </summary>
        public TypeEnum TypeCell { set; get; }
        /// <summary>
        /// 进行判断来输出所对应的内容
        /// </summary>
        public Dictionary<string, string> DisplayContent { set; get; }
    }
    public enum TypeEnum
    {
        String,
        Number
    }

导出Excel是用的类似拼接字符串的方式来实现的，以上代码仅供参考

【上篇】Asp.Net里的Cookies
【下篇】Android学习之获得手机联系人基本信息

作者: stung

该日志由 stung 于12年前发表在综合分类下，最后更新于 2012年03月15日.
转载请注明: C#没有装office进行Excel的导出 | 学步园 +复制链接

抱歉!评论已关闭.

学步园

C#没有装office进行Excel的导出

作者: stung

书签

最新文章New

本站推荐

返回首页