首页 > 代码库 > C#保存wps和office表格.数据源为list<T>类型,
C#保存wps和office表格.数据源为list<T>类型,
想要操作wps或office表格需要引入相对应的dll,office好找,wps在扩展中找到kingsoft的一些dll.
其中通过特性描述来获取泛型的中文名.
具体实现代码如下:
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Reflection;using System.IO;using ET;using Microsoft.Office.Interop.Excel;using System.Diagnostics;using System.ComponentModel;namespace src.Common{ public class SaveExcel { public static string Save<T>(List<T> list, string name, string saveWay, string tableHead) { try { SaveWPSExcel<T>(list, name, saveWay, tableHead); return "WPS保存成功"; } catch (Exception ex) { try { SaveOfficeExcel<T>(list, name, saveWay, tableHead); return "office保存成功"; } catch (Exception ex1) { return "请安装office或WPS"; } } } /// <summary> /// 保存wps表格 /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="list">数据源列表</param> /// <param name="name">文件名</param> /// <param name="saveWay">保存路径</param> /// <param name="tableHead">表头</param> /// <returns></returns> public static string SaveWPSExcel<T>(List<T> list, string name, string saveWay, string tableHead) { string message = ""; ET.ApplicationClass xlsApp = new ET.ApplicationClass(); if (xlsApp == null) { message = "您没有安装WPS，请安装后再次使用！"; return message; } ET.Application objExcel = new ET.Application(); objExcel.Visible = false; object missing = System.Reflection.Missing.Value; workbook objBook = (workbook)objExcel.Workbooks.Add(missing); ET.Worksheet objSheet = (ET.Worksheet)objBook.Worksheets.get_Item(1); ET.Range objRange; Type t = typeof(T); string last = ToName(t.GetProperties().Count() - 1); int stateRow = 3; if (list.Count == 0) { message = "没有数据传入,无需保存"; } else { objRange = objSheet.get_Range("A1", last + "1"); objRange.Merge(true); objRange.HorizontalAlignment = ET.ETHAlign.etHAlignCenter; objRange = objSheet.get_Range("A1", missing); objRange.Value2 = tableHead; objRange.Font.Size = 20; objRange.Font.Bold = true; int titleIndex = 1; foreach (PropertyInfo p in t.GetProperties()) { DescriptionAttribute attr = (DescriptionAttribute)p.GetCustomAttributes(typeof(DescriptionAttribute), true).FirstOrDefault(); if (attr != null) { objSheet.Cells[(stateRow - 1), titleIndex++] = attr.Description; } else { objSheet.Cells[(stateRow - 1), titleIndex++] = p.Name; } //objSheet.Cells[(stateRow - 1), titleIndex++] =((DescriptionAttribute)Attribute.GetCustomAttribute(p,typeof(DescriptionAttribute))).Description; } int iRow = stateRow; foreach (T info in list) { int icel = 1; foreach (PropertyInfo p in t.GetProperties()) { objSheet.Cells[iRow, icel++] = p.GetValue(info, null).ToString(); } iRow++; //int icel = 1; //for (int iCol = 1; iCol < t.GetProperties().Count(); iCol++) //{ // objSheet.Cells[iRow, icel++] = dr[iCol].ToString(); //} //iRow++; } objRange = objSheet.get_Range("A2", last + "2"); objRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); objRange.HorizontalAlignment = ET.ETHAlign.etHAlignCenter; objRange = objSheet.get_Range("A3", last + iRow.ToString()); objRange.EntireColumn.AutoFit(); objRange.HorizontalAlignment = ET.ETHAlign.etHAlignLeft; objRange = objSheet.get_Range("A" + (iRow + 1).ToString(), last + (iRow + 1).ToString()); objRange.Merge(true); objRange.Value2 = DateTime.Now.ToString(); objRange.HorizontalAlignment = ET.ETHAlign.etHAlignRight; objRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); } objBook.Saved = true; DateTime dtime = DateTime.Now; string fileName = dtime.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx"; string baseWay = System.AppDomain.CurrentDomain.BaseDirectory; string fileWay = baseWay + saveWay + "\\" + name + fileName; objBook.SaveCopyAs(fileWay); objBook.Close(true, Type.Missing, Type.Missing); objExcel = null; xlsApp.Quit(); xlsApp = null; //KillProcess("et"); message = "已将信息保存到“" + saveWay + "”文件夹下"; return message; } /// <summary> /// 保存excel表格 /// </summary> /// <param name="table">表数据</param> /// <param name="name">保存的文件名</param> /// <param name="saveWay">保存路径</param> /// <param name="tableHead">表头内容</param> /// <param name="title">列名数组</param> /// <returns></returns> public static string SaveOfficeExcel<T>(List<T> list, string name, string saveWay, string tableHead) { string message = ""; Microsoft.Office.Interop.Excel.ApplicationClass xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlsApp == null) { message = "您没有安装Microsoft Offic Excel，请安装后再次使用！"; return message; } Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application(); objExcel.Visible = false; object missing = System.Reflection.Missing.Value; Workbook objBook = objExcel.Workbooks.Add(missing); Microsoft.Office.Interop.Excel.Worksheet objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objBook.Worksheets.get_Item(1); Microsoft.Office.Interop.Excel.Range objRange; Type t = typeof(T); string last = ToName(t.GetProperties().Count() - 1); int stateRow = 3; if (list.Count == 0) { message = "没有数据传入,无需保存"; } else { objRange = objSheet.get_Range("A1", last + "1"); objRange.Merge(0); objRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; objRange = objSheet.get_Range("A1", missing); objRange.Value2 = tableHead; objRange.Font.Size = 20; objRange.Font.Bold = true; int titleIndex = 1; foreach (PropertyInfo p in t.GetProperties()) { DescriptionAttribute attr = (DescriptionAttribute)p.GetCustomAttributes(typeof(DescriptionAttribute), true).FirstOrDefault(); if (attr != null) { objSheet.Cells[(stateRow - 1), titleIndex++] = attr.Description; } else { objSheet.Cells[(stateRow - 1), titleIndex++] = p.Name; } } int iRow = stateRow; foreach (T info in list) { int icel = 1; foreach (PropertyInfo p in t.GetProperties()) { objSheet.Cells[iRow, icel++] = p.GetValue(info, null); } iRow++; //int icel = 1; //for (int iCol = 1; iCol < t.GetProperties().Count(); iCol++) //{ // objSheet.Cells[iRow, icel++] = dr[iCol].ToString(); //} //iRow++; } objRange = objSheet.get_Range("A2", last + "2"); objRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); objRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; objRange = objSheet.get_Range("A3", last + iRow.ToString()); objRange.EntireColumn.AutoFit(); objRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; objRange = objSheet.get_Range("A" + (iRow + 1).ToString(), last + (iRow + 1).ToString()); objRange.Merge(0); objRange.Value2 = DateTime.Now.ToString(); objRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; objRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); } objBook.Saved = true; DateTime dtime = DateTime.Now; string fileName = dtime.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx"; string baseWay = System.AppDomain.CurrentDomain.BaseDirectory; string fileWay = baseWay + saveWay + "\\" + name + fileName; objBook.SaveCopyAs(fileWay); objBook.Close(true, Type.Missing, Type.Missing); objExcel = null; xlsApp.Quit(); xlsApp = null; KillProcess("Excel"); message = "已将信息保存到“" + saveWay + "”文件夹下"; return message; } /// <summary> /// 关闭excel /// </summary> /// <param name="processName"></param> private static void KillProcess(string processName) { System.Diagnostics.Process myproc = new System.Diagnostics.Process(); //得到所有打开的进程 try { foreach (Process thisproc in Process.GetProcessesByName(processName)) { if (!thisproc.CloseMainWindow()) { thisproc.Kill(); } } } catch (Exception Exc) { throw new Exception("", Exc); } } /// <summary> /// 将数字转换成字母 /// </summary> /// <param name="index"></param> /// <returns></returns> public static string ToName(int index) { if (index < 0) { throw new Exception("invalid parameter"); } List<string> chars = new List<string>(); do { if (chars.Count > 0) index--; chars.Insert(0, ((char)(index % 26 + (int)‘A‘)).ToString()); index = (int)((index - index % 26) / 26); } while (index > 0); return String.Join(string.Empty, chars.ToArray()); } }}
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。