NPOI与Aspose的导入导出保存
半个月没进博客园了，终于把网站做完了，想想毕业快一年的时间里，都是去学习新的计算，现在也该总结下用到的代码，慢慢整理整理，就先从最简单的导入导出开始吧
一：首先看下国人开发的NPOI的导入导出
项目的Office的操作模块图为：
，现在贴上导入导出类的代码
（1）导入类
public class ExcelForImport : IImport
{
protected static ExcelForImport _instance;
private static object lock_instance = new object();
protected ExcelForImport()
{
}
#region IImport 成员
public System.Data.DataTable Import(string filepath)
{
IWorkbook workBook = this.InitializeWorkbook(filepath);
IFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workBook);
ISheet sheet = workBook.GetSheetAt(0);
IEnumerator rows = sheet.GetEnumerator();
DataTable dt = new DataTable();
string[] headerNames = new string[sheet.GetRow(0).PhysicalNumberOfCells];
for (int j = 0; j < headerNames.Length; j++)
{
headerNames[j] = Convert.ToChar(((int)'A') + j % 26).ToString() + ((j / 26) > 0 ? (j / 26).ToString() : string.Empty); // A-Z A1-Z1 An-Zn
}
this.AddColumn(dt, headerNames);
while (rows.MoveNext())
{
IRow row = rows.Current as HSSFRow;
this.AddRow(dt, row, headerNames,evaluator);
}
return dt;
}
public System.Data.DataTable Import(string filepath, string[] headerNames)
{
DataTable dt = new DataTable();
this.AddColumn(dt, headerNames);
IWorkbook wb = InitializeWorkbook(filepath);
IFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
ISheet sht = wb.GetSheetAt(0);
IEnumerator rows = sht.GetRowEnumerator();
//默认第一行为头部列名
if (rows.MoveNext())
{
while (rows.MoveNext())
{
IRow row = rows.Current as HSSFRow;
//M by Duanqh 2012-7-27
//if (row == null) continue;
this.AddRow(dt, row, headerNames, evaluator);
}
}
return dt;
}
#endregion
//增加Row
protected void AddRow(DataTable dt, IRow row, string[] headerNames,IFormulaEvaluator evaluator)
{
System.Data.DataRow newRow = dt.NewRow();
for (int i = 0; i < headerNames.Count(); i++)
{
newRow[headerNames[i]] = GetHSSFCellValue(evaluator.EvaluateInCell(row.GetCell(i)));
}
dt.Rows.Add(newRow);
}
//不同数据类型的处理
protected object GetHSSFCellValue(ICell cell)
{
if (cell == null) return string.Empty;
object rValue = string.Empty;
switch (cell.CellType)
{
case CellType.NUMERIC:
/*
if (NPOI.HSSF.UserModel.HSSFDateUtil.IsCellDateFormatted(cell))
rValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
else
rValue = cell.NumericCellValue.ToString();
* */
rValue = cell.ToString();
break;
case CellType.STRING:
rValue = cell.StringCellValue;
break;
case CellType.BOOLEAN:
rValue = cell.BooleanCellValue;
break;
case CellType.FORMULA: //if HSSFFormulaEvaluator.EvaluateInCell(ICell) CellType.FORMULA will never happen
rValue = "=" + cell.CellFormula;
break;
case CellType.BLANK:
default:
break;
}
return rValue;
}
//增加列
protected void AddColumn(System.Data.DataTable dt, string[] headerNames)
{
foreach (string h in headerNames)
{
dt.Columns.Add(h);
}
}
//HSSFWorkbook对象
protected IWorkbook InitializeWorkbook(string path)
{
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
return new HSSFWorkbook(file);
}
}
//单例
public static ExcelForImport CreateInstance()
{
if (_instance == null)
{
lock (lock_instance)
{
if (_instance == null)
{
_instance = new ExcelForImport();
}
}
}
return _instance;
}
}
（2）导出类
public class ExcelForExport : IExport
{
protected static ExcelForExport _instance;
private static object lock_instance = new object();
protected ExcelForExport()
{
}
#region IExport 成员
public void Export(string filepath, System.Data.DataTable dt)
{
if (dt == null)
{
throw new ArgumentNullException("The argument of dt is null: ExcelForExport.Export(string filepath, System.Data.DataTable dt)");
}
string[] headers = new string[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
headers[i] = dt.Columns[i].ColumnName;
}
this.Export(filepath, dt, headers);
}
public void Export(string filepath, System.Data.DataTable dt, string[] headers)
{
IWorkbook newWB = new HSSFWorkbook();
//ISheet newSht = newWB.CreateSheet("Sheet1");
this.AddSheet(newWB, dt, headers);
this.Save(filepath, newWB);
}
#endregion
protected void Save(string filepath, IWorkbook wb)
{
using (System.IO.FileStream fs = System.IO.File.Create(filepath))
{
wb.Write(fs);
fs.Close();
}
}
protected void AddSheet(IWorkbook workBook, DataTable dt, string[] headers)
{
//头部样式
ICellStyle hStyle = workBook.CreateCellStyle();
hStyle.FillForegroundColor = HSSFColor.YELLOW.index;
hStyle.FillPattern = FillPatternType.BIG_SPOTS;
hStyle.FillBackgroundColor = HSSFColor.YELLOW.index;
hStyle.Alignment = HorizontalAlignment.CENTER;
hStyle.VerticalAlignment = VerticalAlignment.CENTER;
hStyle.BorderBottom = hStyle.BorderLeft = hStyle.BorderRight = hStyle.BorderTop = BorderStyle.MEDIUM;
hStyle.BottomBorderColor = hStyle.LeftBorderColor = hStyle.RightBorderColor = hStyle.TopBorderColor = HSSFColor.BLACK.index;
IFont hFont = workBook.CreateFont();
hFont.Boldweight = (short)FontBoldWeight.BOLD;
hStyle.SetFont(hFont);
int RecordCounts = dt.Rows.Count;
int PageSize = 65501;
int TotalPages = (RecordCounts + PageSize - 1) / PageSize;
for (int i = 1; i <= TotalPages; i++)
{
ISheet sheet = workBook.CreateSheet(string.Format("Sheet{0}", i));
this.AddHeader(sheet, hStyle, headers);
if (i == TotalPages)
{
this.FillSheet(sheet, dt, PageSize * (i - 1), RecordCounts);
}
else
{
this.FillSheet(sheet, dt, PageSize * (i - 1), PageSize * i);
}
}
}
protected void FillSheet(ISheet sheet, DataTable dt, int rowIndexStart, int rowIndexEnd)
{
for (int i = rowIndexStart; i < rowIndexEnd; i++)
{
object[] columns = dt.Rows[i].ItemArray;
this.AddRow(sheet, columns);
}
#region
/*
* 自动调整列的宽度 只支持数字和英文 不支持中文
* 中文解决方案 遍历获取列中最大宽度
* http://blog.csdn.net/jerry_cool/article/details/7000085
* */
#endregion
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
}
protected void AddRow(ISheet sht, object[] columns)
{
this.AddRow(sht, columns, sht.LastRowNum + 1);
}
protected void AddRow(ISheet sht, object[] columns, int rowindex)
{
IRow hRow = sht.CreateRow(rowindex);
hRow.Height = 100 * 4;
int cellIndex = 0;
foreach (object c in columns)
{
ICell hCell = hRow.CreateCell(cellIndex++);
hCell.SetCellValue(c.ToString());
}
}
protected void AddHeader(ISheet sht, ICellStyle hStyle, string[] headers)
{
this.AddHeader(sht, hStyle, headers, 0);
}
//增加文件头，包含样式
protected void AddHeader(ISheet sht, ICellStyle hStyle, string[] headers, int rowindex)
{
IRow hRow = sht.CreateRow(rowindex);
hRow.Height = 200 * 3;
int cellIndex = 0;
foreach (string h in headers)
{
ICell hCell = hRow.CreateCell(cellIndex++);
hCell.CellStyle = hStyle;
hCell.SetCellValue(h);
}
}
//单例
public static ExcelForExport CreateInstance()
{
if (_instance == null)
{
lock (lock_instance)
{
if (_instance == null)
{
_instance = new ExcelForExport();
}
}
}
return _instance;
}
}
（3）上面的导出都是以DataTable的形式导出，写一个转换方法，后面aspose插件的导入导出也就列举DataTable类型的，需要什么类型的都可以自己转换
public static DataTable CopyToDataTable<T>(this IEnumerable<T> list)
{
var pList = new List<PropertyInfo>();
var type = typeof(T);
var dt = new DataTable();
Array.ForEach(type.GetProperties(), p =>
{
pList.Add(p);
dt.Columns.Add(p.Name);
});
foreach (var item in list)
{
DataRow row = dt.NewRow();
pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
dt.Rows.Add(row);
}
return dt;
}
二：Aspose插件的导入导出
导入：
public static DataTable Import(String strFileName)
{
Workbook book = new Workbook();
book.Open(strFileName);
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + cells.MaxDataColumn + 1, true);
}
导出：
private static void Export<T>(IEnumerable<T> data, HttpResponse response)
{
Workbook workbook = new Workbook();
Worksheet sheet = (Worksheet)workbook.Worksheets[0];
PropertyInfo[] ps = typeof(T).GetProperties();
var colIndex = "A";
foreach (var p in ps)
{
sheet.Cells[colIndex + 1].PutValue(p.Name);
int i = 2;
foreach (var d in data)
{
sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
i++;
}
colIndex = ((char)(colIndex[0] + 1)).ToString();
}
response.Clear();
response.Buffer = true;
response.Charset = "utf-8";
response.AppendHeader("Content-Disposition", "attachment;filename=xxx.xls");
response.ContentEncoding = System.Text.Encoding.UTF8;
response.ContentType = "application/ms-excel";
response.BinaryWrite(workbook.SaveToStream().ToArray());
response.End();
}
三：项目中报表的生成，主要是存储过程的问题，其他就是调用上面的2个控件给单元格填值的操作，而且我基本都是用的书签去插值，没什么可以借鉴的，下一篇我将总结下，怎么学习写存储过程，怎么对存储过程的空间与效率取舍的认识
四：快一年了，太多对新东西的渴望，换了家公司，也进入了正轨，也需要总结下一年下来学习到的一切，该巩固下了
努力吧少年
