C#没有装office进行Excel的导出
导出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是用的类似拼接字符串的方式来实现的，以上代码仅供参考
