Ext.net Excel 文件的导入导出
园子里面有很多的关于导入导出的源码，我也是从这里面看见的，我使用的是一个开源的MYXLS，这个里面也用到了NPOI。
首先来说导出，我不知道是不是ext.net内部做了什么拦截的设置，在脚本的那个地方总是会出现一个什么5300还是什么的错误，后台代码实际上已经执行完成了，希望哪位童鞋能帮我解决一下，我的解决办法是这样的：
public static void ExportDataToExcel(DataTable dt, List<string> lstCellTitle,string strFileName)
{
XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
Cells cells = sheet.Cells;
int iCurrentCell = 0;
foreach (string col in lstCellTitle)
{
iCurrentCell++;
Cell cell = cells.Add(1, iCurrentCell, col);//cells.Add(1, col.Ordinal + 1, col.ColumnName);
cell.Font.FontFamily = FontFamilies.Roman; //字Á?体¬?
cell.Font.Bold = true; }
#region
XF dateStyle = xls.NewXF();
dateStyle.Format = "yyyy-mm-dd";
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int rowIndex = i + 2;
int colIndex = j + 1;
string drValue = dt.Rows[i][j].ToString();
switch (dt.Rows[i][j].GetType().ToString())
{
case "System.String":
cells.Add(rowIndex, colIndex, drValue);
break;
case "System.DateTime":
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cells.Add(rowIndex, colIndex, dateV, dateStyle);
break;
case "System.Boolean":
bool boolV = false;
bool.TryParse(drValue, out boolV);
cells.Add(rowIndex, colIndex, boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cells.Add(rowIndex, colIndex, intV);
break;
case "System.Decimal":
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cells.Add(rowIndex, colIndex, doubV);
break;
case "System.DBNull":
cells.Add(rowIndex, colIndex, null);
break;
default:
cells.Add(rowIndex, colIndex, null);
break;
}
}
}
#endregion
xls.FileName = strFileName + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
xls.Save(new UpLoadFilePath().ExportData);
HttpContext.Current.Response.Redirect(string.Format("{0}DownLoadExcel.aspx?FileName={1}&Flag={2}& Other={3}", HttpContext.Current.Request.ApplicationPath, xls.FileName, FileTypeEnum.ExcelFile,string.Empty));
}
在最后的这个地方可以看到我调用了一个aspx的页面，这个页面的后台代码如下：
protected void Page_Load(object sender, EventArgs e)
{
string strFileName = Request.QueryString["FileName"];
string strFlag = Request.QueryString["Flag"];
string strOther = Request.QueryString["Other"];
string strFilePath = string.Empty;
if ((FileTypeEnum)Enum.Parse(typeof(FileTypeEnum), strFlag) == FileTypeEnum.ExcelFile)
{
strFilePath = new UpLoadFilePath().ExportData;
}
else
{
PropertyInfo p = typeof(UpLoadFilePath).GetProperty(strOther);
strFilePath = p.GetValue(new UpLoadFilePath(), null).ToString();
}
DownLoadExistsFile(strFilePath, strFileName);
}
private bool CheckFileExists(string strFilePath)
{
if (System.IO.File.Exists(strFilePath))
{
return false;
}
else
{
return true;
}
}
private void DownLoadExistsFile(string _Path, string _FileName)
{
FileInfo file = null;
file = new FileInfo(_Path + _FileName);
if (file == null || CheckFileExists(file.FullName))
{
Response.Write("<strong style='color:Red'>你?要下载的文件不存在请联系管理员</strong>");
}
else
{
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
Response.AddHeader("Content-Length", file.Length.ToString());
if (Path.GetExtension(_Path + _FileName).ToLower().StartsWith(".doc"))
{
Response.ContentType = "application/msword";
}
else if (Path.GetExtension(_Path + _FileName).ToLower().StartsWith(".xls"))
{
Response.ContentType = "application/vnd.ms-excel";
}
else
{
Response.ContentType = "application/octet-stream";
}
Response.WriteFile(file.FullName);
Response.End();
}
}
其实和写在前一个地方是一样的 但是就是会出现一个脚本的错误，杯具的事情为什么总是发生在我的身上。
导入Excel的东西就没有这么一些麻烦事了直接用到就可以了
public static List<T> GetExcelInfo<T>(string filepath, T temp, List<string> property)
where T : new()
{
HSSFWorkbook hssfworkbookExport = null;
using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read))
{
hssfworkbookExport = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbookExport.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
List<T> reInfo = new List<T>();
rows.MoveNext();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
for (int i = 0; i < property.Count; i++)
{
PropertyInfo pro = temp.GetType().GetProperty(property.ElementAt(i));
if (row.GetCell(i) != null)
{
if (row.GetCell(i).IsMergedCell)
{
ICell mergedcell = GetMergedRegionValue(sheet, row.RowNum, i);
var dd = Convert.ChangeType(mergedcell.ToString(), pro.PropertyType);
pro.SetValue(temp, Convert.ChangeType(mergedcell.ToString(), pro.PropertyType), null);
}
else
{
var dd = Convert.ChangeType(row.GetCell(i).ToString(), pro.PropertyType);
pro.SetValue(temp, Convert.ChangeType(row.GetCell(i).ToString(), pro.PropertyType), null);
}
}
}
reInfo.Add(temp);
temp = new T();
}
return reInfo;
}
private static ICell GetMergedRegionValue(HSSFSheet sheet, int row, int column)
{
int sheetMergeCount = sheet.NumMergedRegions;
for (int i = 0; i < sheetMergeCount; i++)
{
CellRangeAddress ca = sheet.GetMergedRegion(i);
int firstColumn = ca.FirstColumn;
int lastColumn = ca.LastColumn;
int firstRow = ca.FirstRow;
int lastRow = ca.LastRow;
if (row >= firstRow && row <= lastRow)
{
if (column >= firstColumn && column <= lastColumn)
{
IRow fRow = sheet.GetRow(firstRow);
ICell fCell = fRow.GetCell(firstColumn);
return fCell;
}
}
}
return null;
}
#endregion
在这里我还用上了获取合并单元格的数据不知道各位童鞋能有更加好的建议。
浙公网安备 33010602011771号