在做通用導入導出的時候，最關鍵的應該就是實體導出導入的順序了，但是編譯器在編譯的時候又無法自定義屬性編譯的順序，所以需要一個自定義的特性標簽來指定實體類導出的順序，然後通過自定義的比較器將屬性排序
因為wcf中無法對實體類的自定義特性進行描述，所以獲取不到我們自定義的OrderAttribute，雖然DataMemberAttribute中的Order屬性是描述屬性序列化的順序，但是因為沒有對序列化排序沒有特殊的要求，於是就用它代替了，起初發射之後總是倒數兩個Order屬性的值是正常的，其他的都為-1，後來發現生成的順序也是按Order生成的，於是就沒有深究了（如果有深入研究的朋友 希望指點一下）。
class Program
{
static void Main(string[] args)
{
//過濾掉沒有打排序標簽的屬性
List<PropertyInfo> pis = typeof(People).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
//自定義比較器排序
pis.Sort(new OrderComparator());
Console.ReadKey();
}
}
//自定義排序特性
public class OrderAttribute : Attribute
{
public OrderAttribute(int order)
{
this.PropertyOrder = order;
}
public int PropertyOrder { get; set; }
}
//實體類
public class People
{
public int ID { get; set; }
[Order(1)]
public string Name { get; set; }
[Order(3)]
public int Age { get; set; }
[Order(2)]
public bool Gender { get; set; }
[Order(5)]
public double Height { get; set; }
[Order(4)]
public double Weight { get; set; }
}
//自定義屬性比較器
public class OrderComparator : IComparer<PropertyInfo>
{
public int Compare(PropertyInfo x, PropertyInfo y)
{
OrderAttribute xOrderAttribute = x.GetCustomAttributes(typeof(OrderAttribute), false).FirstOrDefault() as OrderAttribute;
OrderAttribute yOrderAttribute = y.GetCustomAttributes(typeof(OrderAttribute), false).FirstOrDefault() as OrderAttribute;
return xOrderAttribute.PropertyOrder - yOrderAttribute.PropertyOrder;
}
}
目前使用過操作excel的方式有NPOI和，微軟提供的Microsoft.Office.Interop.Excel性能太牛X了，所以就不敢貼出來了
一、使用NPOI
NPOI導出：
/// <summary>
/// 保存到硬盤
/// </summary>
/// <param name="path">保存路徑@"c:\book1.xls"</param>
/// <param name="data">數據源</param>
/// <param name="columnsName">excel列名</param>
public void SaveToFile<T>(List<T> data, string path, List<string> excelColumnsTitle)
{
if (Path.GetExtension(path).Equals(".xls"))
{
//excel2003
SaveToFile2003<T>(data, path, excelColumnsTitle);
}
else if (Path.GetExtension(path).Equals(".xlsx"))
{
//excel2007
SaveToFile2007<T>(data, path, excelColumnsTitle);
}
else
{
throw new Exception("請傳入正確的excel路徑");
}
}
SaveToFile2003
/// <summary>
/// excel2003導出
/// </summary>
private void SaveToFile2003<T>(List<T> data, string path, List<string> excelColumnsTitle)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet"); //添加一個sheet
//給sheet1添加第一行的頭部標題
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
for (int i = 0; i < excelColumnsTitle.Count; i++)
{
row1.CreateCell(i).SetCellValue(excelColumnsTitle[i]);
}
//過濾屬性
List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
pis.Sort(new OrderComparator());
//金額格式
NPOI.SS.UserModel.ICellStyle cellStyleDecimal = book.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat formatDecimal = book.CreateDataFormat();
cellStyleDecimal.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); //單元格格式為“0.00”來表示,"￥#,##0"美元顯示,"0.00%"百分比顯示
//日期格式
NPOI.SS.UserModel.ICellStyle cellStyleDateTime = book.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat formatDateTime = book.CreateDataFormat();
cellStyleDateTime.DataFormat = formatDateTime.GetFormat("yyyy-m");
//將數據逐步寫入sheet1各個行
for (int i = 0; i < data.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(i + 1);
for (int j = 0; j < pis.Count; j++)
{
NPOI.SS.UserModel.ICell cell = rowtemp.CreateCell(j);
if (pis[j].PropertyType.IsAssignableFrom(typeof(string)))
{
cell.SetCellValue(pis[j].GetValue(data[i], null).ToString());
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(int)))
{
cell.SetCellValue(Convert.ToInt32(pis[j].GetValue(data[i], null)));
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(decimal)))
{
cell.CellStyle = cellStyleDecimal;
cell.SetCellValue(Convert.ToDouble(pis[j].GetValue(data[i], null)));
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(DateTime)))
{
cell.CellStyle = cellStyleDateTime;
cell.SetCellValue(Convert.ToDateTime(pis[j].GetValue(data[i], null)));
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(double)))
{
cell.CellStyle = cellStyleDecimal;
cell.SetCellValue(Convert.ToDouble(pis[j].GetValue(data[i], null)));
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(bool)))
{
cell.SetCellValue(Convert.ToBoolean(pis[j].GetValue(data[i], null)));
}
}
}
// 寫入到客戶端
using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write))
{
book.Write(fs);
}
}
SaveToFile2007
/// <summary>
/// excel2007導出
/// </summary>
private void SaveToFile2007<T>(List<T> data, string path, List<string> excelColumnsTitle)
{
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet"); //添加一個sheet
//給sheet1添加第一行的頭部標題
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
for (int i = 0; i < excelColumnsTitle.Count; i++)
{
row1.CreateCell(i).SetCellValue(excelColumnsTitle[i]);
}
//過濾屬性
List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
pis.Sort(new OrderComparator());
//金額格式
NPOI.SS.UserModel.ICellStyle cellStyleDecimal = book.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat formatDecimal = book.CreateDataFormat();
cellStyleDecimal.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); //單元格格式為“0.00”來表示,"￥#,##0"美元顯示,"0.00%"百分比顯示
//日期格式
NPOI.SS.UserModel.ICellStyle cellStyleDateTime = book.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat formatDateTime = book.CreateDataFormat();
cellStyleDateTime.DataFormat = formatDateTime.GetFormat("yyyy-m");
//將數據逐步寫入sheet1各個行
for (int i = 0; i < data.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(i + 1);
for (int j = 0; j < pis.Count; j++)
{
NPOI.SS.UserModel.ICell cell = rowtemp.CreateCell(j);
if (pis[j].PropertyType.IsAssignableFrom(typeof(string)))
{
cell.SetCellValue(pis[j].GetValue(data[i], null).ToString());
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(int)))
{
cell.SetCellValue(Convert.ToInt32(pis[j].GetValue(data[i], null)));
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(decimal)))
{
cell.CellStyle = cellStyleDecimal;
cell.SetCellValue(Convert.ToDouble(pis[j].GetValue(data[i], null)));
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(DateTime)))
{
cell.CellStyle = cellStyleDateTime;
cell.SetCellValue(Convert.ToDateTime(pis[j].GetValue(data[i], null)));
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(double)))
{
cell.CellStyle = cellStyleDecimal;
cell.SetCellValue(Convert.ToDouble(pis[j].GetValue(data[i], null)));
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(bool)))
{
cell.SetCellValue(Convert.ToBoolean(pis[j].GetValue(data[i], null)));
}
}
}
// 寫入到客戶端
using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write))
{
book.Write(fs);
}
}
NPOI導入：
/// <summary>
/// 返回List數據
/// </summary>
/// <typeparam name="T">實體類</typeparam>
/// <param name="path">excel文件路徑</param>
/// <returns></returns>
public List<T> ImportExcelToList<T>(string path)
{
if (Path.GetExtension(path).Equals(".xls"))
{
//excel2003
return ImportExcelToList2003<T>(path);
}
else if (Path.GetExtension(path).Equals(".xlsx"))
{
//excel2007
return ImportExcelToList2007<T>(path);
}
else
{
throw new Exception("請傳入正確的excel路徑");
}
}
ImportExcelToList2003
/// <summary>
/// excel2003導入
/// </summary>
private List<T> ImportExcelToList2003<T>(string path)
{
List<T> list = new List<T>();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new FileStream(path, FileMode.Open, FileAccess.Read));
NPOI.HSSF.UserModel.HSSFSheet sheet = book.GetSheet("Sheet") as NPOI.HSSF.UserModel.HSSFSheet;
if (sheet != null)
{
List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
pis.Sort(new OrderComparator());
//導入數據
for (int i = 1; i <= sheet.LastRowNum; i++) //獲得所有行數
{
T model = Activator.CreateInstance<T>();
NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //讀取當前行數據
if (row != null)
{
try
{
for (int j = 0; j < row.Cells.Count; j++)
{
if (pis[j].PropertyType.IsAssignableFrom(typeof(string)))
{
pis[j].SetValue(model, row.GetCell(j).StringCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(int)))
{
pis[j].SetValue(model, (int)row.GetCell(j).NumericCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(decimal)))
{
pis[j].SetValue(model, (decimal)row.GetCell(j).NumericCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(DateTime)))
{
pis[j].SetValue(model, row.GetCell(j).DateCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(double)))
{
pis[j].SetValue(model, row.GetCell(j).NumericCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(bool)))
{
pis[j].SetValue(model, row.GetCell(j).BooleanCellValue, null);
}
}
list.Add(model);
}
catch { }
}
}
}
return list;
}
ImportExcelToList2007
/// <summary>
/// excel2007導入
/// </summary>
private List<T> ImportExcelToList2007<T>(string path)
{
List<T> list = new List<T>();
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook(new FileStream(path, FileMode.Open, FileAccess.Read));
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0);
if (sheet != null)
{
List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
pis.Sort(new OrderComparator());
//導入數據
for (int i = 1; i <= sheet.LastRowNum; i++) //獲得所有行數
{
T model = Activator.CreateInstance<T>();
NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //讀取當前行數據
if (row != null)
{
try
{
for (int j = 0; j < row.Cells.Count; j++)
{
if (pis[j].PropertyType.IsAssignableFrom(typeof(string)))
{
pis[j].SetValue(model, row.GetCell(j).StringCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(int)))
{
pis[j].SetValue(model, (int)row.GetCell(j).NumericCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(decimal)))
{
pis[j].SetValue(model, (decimal)row.GetCell(j).NumericCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(DateTime)))
{
pis[j].SetValue(model, row.GetCell(j).DateCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(double)))
{
pis[j].SetValue(model, row.GetCell(j).NumericCellValue, null);
}
else if (pis[j].PropertyType.IsAssignableFrom(typeof(bool)))
{
pis[j].SetValue(model, row.GetCell(j).BooleanCellValue, null);
}
}
list.Add(model);
}
catch { }
}
}
}
return list;
}
二、使用Acey.ExcelX，以前的時候性能是憂於NPOI的，而且支持excel03和07，相較NPOI有時候還能省去cell類型的判斷，只是是第三方收費的，所以導出後會有廣告
Acey.ExcelX導出
/// <summary>
/// 保存到硬盤
/// </summary>
/// <param name="path">保存路徑@"c:\book1.xls"</param>
/// <param name="data">數據源</param>
/// <param name="columnsName">excel列名</param>
public void SaveToFile<T>(List<T> data, string path, List<string> excelColumnsTitle)
{
IWorkbook workbook = ExcelxApplication.CreateWorkbook();
IWorksheet worksheet = workbook.Worksheets[0];
for (int i = 0; i < excelColumnsTitle.Count; i++)
{
worksheet.Cells[0, i].Value = excelColumnsTitle[i];
}
List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
pis.Sort(new OrderComparator());
for (int i = 0; i < data.Count; i++)
{
for (int j = 0; j < pis.Count; j++)
{
worksheet.Cells[i + 1, j].Value = pis[j].GetValue(data[i], null);
}
}
workbook.SaveAs(path);//保存到硬盤
}
Acey.ExcelX導入
/// <summary>
/// 返回List數據
/// </summary>
/// <typeparam name="T">實體類</typeparam>
/// <param name="path">excel文件路徑</param>
/// <returns></returns>
public List<T> ImportExcelToList<T>(string path)
{
List<T> list = new List<T>();
Type type = typeof(T);
//創建Workbook對象通過打開指定的Excel文檔。
IWorkbook workbook = ExcelxApplication.Open(path);
//獲取以0為基數的Worksheet對象。
IWorksheet worksheet = workbook.Worksheets[0];
//獲取工作表中最大數據行。
int maxRow = worksheet.MaxDataRow;
//獲取工作表中最大數據列。
int maxCol = worksheet.MaxDataColumn;
//創建指定區域的對象。
IRange range = worksheet.Cells.CreateRange(0, 0, maxRow, maxCol);
//將該區域對象的數據導出到DataTable對象中。
DataTable table = range.ExportDataTable();
//返回該DataTable對象。
for (int row = 1; row <= maxRow; row++)
{
T model = Activator.CreateInstance<T>();
List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
pis.Sort(new OrderComparator());
for (int col = 0; col <= maxCol; col++)
{
ICell cell = worksheet.Cells[row, col];
if (pis[col].PropertyType.IsAssignableFrom(typeof(string)))
{
pis[col].SetValue(model, Convert.ToString(cell.Value), null);
}
else if (pis[col].PropertyType.IsAssignableFrom(typeof(int)))
{
pis[col].SetValue(model, Convert.ToInt32(cell.Value), null);
}
else if (pis[col].PropertyType.IsAssignableFrom(typeof(decimal)))
{
pis[col].SetValue(model, Convert.ToDecimal(cell.Value), null);
}
else if (pis[col].PropertyType.IsAssignableFrom(typeof(DateTime)))
{
pis[col].SetValue(model, Convert.ToDateTime(cell.Value), null);
}
else if (pis[col].PropertyType.IsAssignableFrom(typeof(double)))
{
pis[col].SetValue(model, Convert.ToDouble(cell.Value), null);
}
else if (pis[col].PropertyType.IsAssignableFrom(typeof(bool)))
{
pis[col].SetValue(model, Convert.ToBoolean(cell.Value), null);
}
}
list.Add(model);
}
return list;
}
Acey.ExcelX輸出到web
/// <summary>
///輸出到網頁
///</summary>
///<param name="context">HttpContext</param>
///<param name="fileName">"book1.xls"</param>
///<param name="data">數據源</param>
public static void RenderToBrowser(DataTable data, HttpContext context, string fileName)
{
IWorkbook workbook = ProcessWorkBook(data);
workbook.SaveToHttpResponse(context.Response, fileName, false);//輸出到網頁
}
這是我之前寫的用反射的將數據導入到excel中的類，具體實現，代碼裡有注釋。不知道樓主持久層用的什麼東東？如果是ibaits，建議使用ibatis的rowhandler，導出部分的實現，和下面這個類也很類似，樓主自己改改吧，這樣性能會高一些，尤其是在你處理的要寫入文件的數據，比較多的情況下。
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import cn.emag.framework.Util.LogName;
/**
* 導出成excel文件工具類
*/
public class Export2ExcelUtil
{
private static Logger log = Logger.getLogger(LogName.ERROR_LOG);
private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
/**
* 生成excel文件存入服務器
* @param importdata 待導入excle文件的內容
* @param header excel”表頭“部分內容
* @param attr 與”表頭“順序對應的importdata中的成員變量名，首字母大寫
* @param fileName 導入到目標文件中，完整路徑
*/
public static void export2exc(List<Object> importdata,String[] header,String[] attr, String fileName)
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFShee......余下全文>>
File New Data直接選就可以了
