/// <summary> /// 生成xlsx /// </summary> /// <param name="dvLine">数据视图</param> /// <param name="sheetName">sheetName</param> /// <param name="englishName">要导出的视图对应列名称</param> /// <param name="chinseName">要导出的数据对应列在excel里显示的中文名称</param> /// <param name="sumColNames">要求合的列名称</param> /// <param name="totNameIndex">显示"合计:"的列的序号，比如对a-c列合并显示"合计:"传3(c)</param> public ExcelPackage CreateXlsx(DataView dvLine, string sheetName, string[] englishName, string[] chinseName, List<string> sumColNames, int totNameIndex) { if (dvLine.Count <= 0) return null; ExcelPackage pck = new ExcelPackage(); var ws = pck.Workbook.Worksheets.Add(sheetName); ExcelWorksheet worksheet = ws as ExcelWorksheet; int rowIndex = 1; int colIndex = 1; ExcelRange rg = null; //标题 rg = worksheet.Cells[rowIndex, colIndex, rowIndex, colIndex + chinseName.Length]; rg.Value = sheetName; rg.Merge = true; rg.Style.Font.Bold = true; rg.Style.Font.Size = 14; rowIndex++; //列标题 rg = worksheet.Cells[rowIndex, colIndex++]; rg.Value = "序号"; rg.Style.Font.Bold = true; rg.Style.Font.Size = 13; for (int i = 0; i < chinseName.Length; i++) { rg = worksheet.Cells[rowIndex, colIndex++]; rg.Value = chinseName[i]; rg.Style.Font.Bold = true; rg.Style.Font.Size = 13; } //填充数据 rowIndex++; Dictionary<string, Decimal> dic = new Dictionary<string, Decimal>(); //存放求和列的合计值 //求和初始值设为0 foreach (string col in sumColNames) { dic.Add(col, 0); } int rowNum = 1; for (int i = 0; i < dvLine.Count; i++) //循环数据表 { colIndex = 1; //序号 rg = worksheet.Cells[rowIndex, colIndex++]; rg.Value = rowNum++; rg.Style.Font.Size = 12; //数据 for (int j = 0; j < englishName.Length; j++) //对视图里指定的列填充数据 { rg = worksheet.Cells[rowIndex, colIndex]; string data = (dvLine[i][englishName[j]] != null) ? dvLine[i][englishName[j]].ToString() : ""; //如果是需要求和的列，将值转换为数字型 if (sumColNames.Contains(englishName[j])) { Decimal rtnV = 0; Decimal.TryParse(data, out rtnV); rg.Value = rtnV; dic[englishName[j]] += rtnV;//未和 //循环到DV的最后一行时，填充合计值 if (i == dvLine.Count - 1) { rg = worksheet.Cells[rowIndex + 1, colIndex]; rg.Value = dic[englishName[j]]; } } else { rg.Value = data; } rg.Style.Font.Size = 12; colIndex++; } rowIndex++; } rg = worksheet.Cells[rowIndex, totNameIndex]; rg.Value = "合计:"; worksheet.Cells.AutoFitColumns(); worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //居中 worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //居中 return pck; }
/// <summary> /// 保存为xlsx文件 /// </summary> /// <param name="dvLine">数据视图</param> /// <param name="fileName">文件名称不带后缀名</param> /// <param name="englishName">要导出的视图对应列名称</param> /// <param name="chinseName">要导出的数据对应列在excel里显示的中文名称</param> /// <param name="sumColNames">要求合的列名称</param> /// <param name="totNameIndex">显示"合计:"的列的序号，比对a-c列合并显示"合计:"传3(c)</param> /// <param name="filePath">导出文件路径,D:1</param> public void SaveAsXlsx(DataView dvLine, string fileName, string[] englishName, string[] chinseName, List<string> sumColNames, int totNameIndex, string filePath) { ExcelPackage pck = CreateXlsx(dvLine, fileName, englishName, chinseName, sumColNames, totNameIndex); if (!System.IO.Directory.Exists(filePath)) { System.IO.Directory.CreateDirectory(filePath); } using (System.IO.FileStream file = new System.IO.FileStream(filePath + fileName + ".xlsx", System.IO.FileMode.Create)) { byte[] byData = pck.GetAsByteArray(); file.Seek(0, System.IO.SeekOrigin.Begin); file.Write(byData, 0, byData.Length); file.Close(); } pck.Dispose(); //web页面的导出方法 /* System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xlsx"); System.Web.HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray()); System.Web.HttpContext.Current.Response.Flush(); System.Web.HttpContext.Current.Response.Close();*/ }
/// <summary>
/// 读取xlsx数据到DataTable
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetIndex">要读取的excel文件里sheet索引</param>
/// <returns></returns>
public DataTable Read(string fileName, int sheetIndex)
{
DataTable dt = null;
if (File.Exists(fileName))
{
try
{
FileInfo excel = new FileInfo(fileName);
ExcelPackage package = new ExcelPackage(excel);
int sheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页
if (sheetCount >= sheetIndex)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetIndex];//选定指定页
int maxColumnNum = worksheet.Dimension.End.Column;//最大列
int minColumnNum = worksheet.Dimension.Start.Column;//最小列
int maxRowNum = worksheet.Dimension.End.Row;//最小行
int minRowNum = worksheet.Dimension.Start.Row;//最大行
dt = new DataTable();
for (int i = minColumnNum; i <= maxColumnNum; i++)
{
DataColumn dc = new DataColumn(i.ToString(), typeof(string));
dt.Columns.Add(dc);
}
for (int i = minRowNum; i <= maxRowNum; i++)
{
DataRow dr = dt.NewRow();
for (int j = minColumnNum; j <= maxColumnNum; j++)
{
ExcelRange range = worksheet.Cells[i, j];
if (range != null && range.Value != null)
{
dr[j.ToString()] = range.Value.ToString();
}
}
dt.Rows.Add(dr);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return dt;
}
epplus下载 http://epplus.codeplex.com/