using Aspose.Cells;
using Microsoft.AspNetCore.Hosting;
using Microsoft.VisualBasic;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System.Data;
namespace JobCommon
{
public class ExcelHelper
{
private readonly IHostingEnvironment _hostingEnvironment;
public ExcelHelper(IHostingEnvironment hostingEnvironment)
{
_hostingEnvironment = hostingEnvironment;
}
/// <summary>
/// Datable导出成Excel
/// </summary>
/// <param name="dt">导出存放文件夹路径</param>
/// <param name="Route">文件夹路径</param>
/// <param name="file">导出路径(包括文件名与扩展名),注：年月日时分秒+文件名</param>
public static void TableToExcel(DataTable dt, string Route, string file)
{
try
{
//string[] fileArr = file.Split(new char[] { '.' });
//file = fileArr[0] + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + fileArr[1];
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//设置列宽
//sheet.SetColumnWidth(0, 15 * 256);
//sheet.SetColumnWidth(1, 15 * 256);
//sheet.SetColumnWidth(2, 15 * 256);
//sheet.SetColumnWidth(3, 15 * 256);
//sheet.SetColumnWidth(4, 15 * 256);
//sheet.SetColumnWidth(5, 25 * 256);
//sheet.SetColumnWidth(6, 25 * 256);
//sheet.SetColumnWidth(7, 30 * 256);
//sheet.SetColumnWidth(8, 15 * 256);
//sheet.SetColumnWidth(9, 15 * 256);
//sheet.SetColumnWidth(10, 15 * 256);
//sheet.SetColumnWidth(12, 15 * 256);
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// sheet.SetColumnWidth(i, 20 * 256);
//}
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count - 1; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
ICellStyle style = workbook.CreateCellStyle();
ICellStyle style2 = workbook.CreateCellStyle();
string val = "";
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count - 1; j++)
{
ICell cell = row1.CreateCell(j);
val = dt.Rows[i][j].ToString();
cell.SetCellValue(val);
}
}
//合并单元格
CellRangeAddress rowCellRangeAddress = new CellRangeAddress(1, 5, 0, 8);
sheet.AddMergedRegion(rowCellRangeAddress);
//设置列宽
sheet.SetColumnWidth(4, 20 * 256);
//设置行高
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
IRow row2 = sheet.GetRow(2);
row2.HeightInPoints = 120;
row2.GetCell(2).CellStyle = cellStyle;
setPic((HSSFWorkbook)workbook, patriarch, "/FileManage/AA/20201102/2bf26b91-8c2b-4bcb-88f1-87c8ec001c96.jpg", sheet, 2, 4);
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream, true);
var buf = stream.ToArray();
//如找不到文件夹则创建
if (!Directory.Exists(Route))
{
Directory.CreateDirectory(Route);
}
//保存为Excel文件
using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
fs.Close();
fs.Dispose();
}
//下载部分 测试无效
//HttpResponse response = HttpContext.Current.Response;
//response.Clear();
//response.ClearHeaders();
//response.ClearContent();
//response.Buffer = true;
//response.AddHeader("content-disposition", string.Format("attachment; FileName={0}", file));
//response.Charset = "GB2312";
//response.ContentEncoding = Encoding.GetEncoding("GB2312");
//response.ContentType = MimeMapping.GetMimeMapping(file);
//response.WriteFile(Route + file);
//response.Flush();
//response.Close();
//HttpResponseBase response = httpContext.Response;
//response.Clear();
//response.Buffer = false;
//response.AddHeader("Accept-Ranges", "bytes");
//response.AddHeader("Content-Type", GetContentTypeByFileName(fileName));
//response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(fileName)));
//response.AddHeader("Content-Length", buffer.Length.ToString());
//response.OutputStream.Write(buffer, 0, buffer.Length);
//response.Flush();
//response.End();
}
catch (Exception ex)
{
//LoggerHelper._.Error(ex.ToString());
}
}
/// <summary>
/// Datable导出成Excel
/// </summary>
/// <param name="dt">导出存放文件夹路径</param>
/// <param name="Route">文件夹路径</param>
/// <param name="file">导出路径(包括文件名与扩展名),注：年月日时分秒+文件名</param>
public static void DailyTableToExcel(DataTable dt, DataTable dtWorkToday, DataTable dtTomorrowPlan, string Route, string file)
{
try
{
//string[] fileArr = file.Split(new char[] { '.' });
//file = fileArr[0] + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + fileArr[1];
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return; }
ISheet sheet;
DataRow[] dataRow;
//新建样式
ICellStyle style = workbook.CreateCellStyle();
//字体颜色
//创建字体
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
//给字体加粗
ffont.IsBold = true;
style.Alignment = HorizontalAlignment.Center;
//给样式添加字体
style.SetFont(ffont);
ICellStyle styleContent = workbook.CreateCellStyle();
styleContent.VerticalAlignment = VerticalAlignment.Center;
styleContent.WrapText = true;
//ICellStyle styleBorder = workbook.CreateCellStyle();
//styleBorder.BorderBottom = BorderStyle.Medium;
//styleBorder.BorderLeft = BorderStyle.Medium;
//styleBorder.BorderRight = BorderStyle.Medium;
//styleBorder.BorderTop = BorderStyle.Medium;
//styleBorder.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
//styleBorder.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
//styleBorder.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
//styleBorder.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
object WechatId, DailyID, UserName, Position, ImgPath, CreateDate;
object WorkItem, Problem, ResolvedState, PlanDescription;
string[] ImgPathArr = new string[0];
string SheetName = "", ImgPath2 = "";
int RowsIndex = 0;
int SheetIndex = 2;
for (int i = 0; i < dt.Rows.Count; i++)
{
RowsIndex = 0;
WechatId = dt.Rows[i]["WechatId"];
DailyID = dt.Rows[i]["DailyID"];
UserName = dt.Rows[i]["UserName"];
Position = dt.Rows[i]["Position"];
ImgPath = dt.Rows[i]["ImgPath"];
if (ImgPath != null)
{
if (ImgPath.ToString() != "")
{
ImgPathArr = ImgPath.ToString().Split(new char[] { '|' });
}
}
CreateDate = dt.Rows[i]["CreateDate"];
CreateDate = DateTime.Parse(CreateDate.ToString()).ToString("yyyyMMdd");
if (workbook.GetSheet(CreateDate.ToString()) != null)
{
SheetName = CreateDate.ToString() + "-" + SheetIndex;
SheetIndex++;
}
else
{
SheetName = CreateDate.ToString();
SheetIndex = 2;
}
sheet = workbook.CreateSheet(SheetName);
IRow row = sheet.CreateRow(RowsIndex);
RowsIndex++;
ICell cell = row.CreateCell(0);
cell.SetCellValue("" + UserName + "（" + Position + "） " + CreateDate + "日报");
cell.CellStyle = style;//设置样式
sheet.SetColumnWidth(0, 50 * 256);
////合并单元格
CellRangeAddress rowCellRangeAddress = new CellRangeAddress(0, 0, 0, 2);
sheet.AddMergedRegion(rowCellRangeAddress);
row = sheet.CreateRow(RowsIndex);
RowsIndex++;
cell = row.CreateCell(0);
cell.SetCellValue("今日工作");
cell.CellStyle = style;//设置样式
cell = row.CreateCell(1);
cell.SetCellValue("问题点");
cell.CellStyle = style;//设置样式
cell = row.CreateCell(2);
cell.SetCellValue("解决状态");
cell.CellStyle = style;//设置样式
sheet.SetColumnWidth(0, 50 * 256);
sheet.SetColumnWidth(1, 50 * 256);
dataRow = dtWorkToday.Select("DailyID='" + DailyID + "'");
//今日工作
for (int j = 0; j < dataRow.Length; j++)
{
WorkItem = dataRow[j]["WorkItem"];
Problem = dataRow[j]["Problem"];
ResolvedState = dataRow[j]["ResolvedState"];
//数据 循环填充内容部分
row = sheet.CreateRow(RowsIndex);
row.HeightInPoints = 60;
RowsIndex++;
cell = row.CreateCell(0);
cell.CellStyle = styleContent;
cell.SetCellValue(WorkItem.ToString());
cell = row.CreateCell(1);
cell.CellStyle = styleContent;
cell.SetCellValue(Problem.ToString());
cell = row.CreateCell(2);
cell.CellStyle = styleContent;
cell.SetCellValue(ResolvedState.ToString() + "%");
row = sheet.CreateRow(RowsIndex);//空行
//合并单元格
rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
RowsIndex++;
sheet.AddMergedRegion(rowCellRangeAddress);
}
row = sheet.CreateRow(RowsIndex);
cell = row.CreateCell(0);
cell.SetCellValue("明日计划");
cell.CellStyle = style;//设置样式
sheet.SetColumnWidth(0, 50 * 256);
//合并单元格
rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
RowsIndex++;
sheet.AddMergedRegion(rowCellRangeAddress);
dataRow = dtTomorrowPlan.Select("DailyID='" + DailyID + "'");
//明日计划
for (int k = 0; k < dataRow.Length; k++)
{
PlanDescription = dataRow[k]["PlanDescription"];
//数据 循环填充内容部分
row = sheet.CreateRow(RowsIndex);
row.HeightInPoints = 60;
cell = row.CreateCell(0);
cell.CellStyle = styleContent;
cell.SetCellValue(PlanDescription.ToString());
//合并单元格
rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
RowsIndex++;
sheet.AddMergedRegion(rowCellRangeAddress);
row = sheet.CreateRow(RowsIndex);//空行
//合并单元格
rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
RowsIndex++;
sheet.AddMergedRegion(rowCellRangeAddress);
}
row = sheet.CreateRow(RowsIndex);
cell = row.CreateCell(0);
cell.SetCellValue("图片列表");
cell.CellStyle = style;//设置样式
sheet.SetColumnWidth(0, 50 * 256);
//合并单元格
rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
RowsIndex++;
sheet.AddMergedRegion(rowCellRangeAddress);
int rowline = RowsIndex, col = 0;
//图片列表
for (int z = 0; z < ImgPathArr.Length; z++)
{
col = z;
if (col > 1 && col % 2 == 0)
{
col = 0;
rowline += 10;
}
if (col > 1 && col % 2 == 1)
{
col = 1;
}
ImgPath2 = ImgPathArr[z];
ImgPath2 = ImgPath2.Substring(ImgPath2.IndexOf("FileManage") - 1);
//插入图片
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
setPic((HSSFWorkbook)workbook, patriarch, ImgPath2, sheet, rowline, col);
//setPic((HSSFWorkbook)workbook, patriarch, "/FileManage/AA/20201102/2bf26b91-8c2b-4bcb-88f1-87c8ec001c96.jpg", sheet, rowline, col);
}
}
//新建工作表
//ISheet Sheet1 = workbook.CreateSheet("Sheet1");
//CreateSheet(workbook, "20201106");
//表头
//IRow row = sheet.CreateRow(0);
//for (int i = 0; i < dt.Columns.Count - 1; i++)
//{
// ICell cell = row.CreateCell(i);
// cell.SetCellValue(dt.Columns[i].ColumnName);
//}
//ICellStyle style = workbook.CreateCellStyle();
//ICellStyle style2 = workbook.CreateCellStyle();
//string val = "";
////数据
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// IRow row1 = sheet.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count - 1; j++)
// {
// ICell cell = row1.CreateCell(j);
// val = dt.Rows[i][j].ToString();
// cell.SetCellValue(val);
// }
//}
////合并单元格
//CellRangeAddress rowCellRangeAddress = new CellRangeAddress(1, 5, 0, 8);
//sheet.AddMergedRegion(rowCellRangeAddress);
////设置列宽
//sheet.SetColumnWidth(4, 20 * 256);
////设置行高
//HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//IRow row2 = sheet.GetRow(2);
//row2.HeightInPoints = 120;
//row2.GetCell(2).CellStyle = cellStyle;
////插入图片
//HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//setPic((HSSFWorkbook)workbook, patriarch, "/FileManage/AA/20201102/2bf26b91-8c2b-4bcb-88f1-87c8ec001c96.jpg", sheet, 2, 4);
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream, true);
var buf = stream.ToArray();
//如找不到文件夹则创建
if (!Directory.Exists(Route))
{
Directory.CreateDirectory(Route);
}
//保存为Excel文件
using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
fs.Close();
fs.Dispose();
}
}
catch (Exception ex)
{
//LoggerHelper._.Error(ex.ToString());
}
}
/// <summary>
/// 新建Sheet
/// </summary>
/// <param name="workbook"></param>
/// <param name="SheetName"></param>
public static void CreateSheet(IWorkbook workbook, string SheetName)
{
ISheet sheet = workbook.CreateSheet(SheetName);
//新建样式
ICellStyle style = workbook.CreateCellStyle();
//字体颜色
//创建字体
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
//给字体加粗
ffont.IsBold = true;
style.Alignment = HorizontalAlignment.Center;
//给样式添加字体
style.SetFont(ffont);
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("李儒鸿（.net工程师）");
cell.CellStyle = style;//设置样式
sheet.SetColumnWidth(0, 50 * 256);
////合并单元格
CellRangeAddress rowCellRangeAddress = new CellRangeAddress(0, 0, 0, 2);
sheet.AddMergedRegion(rowCellRangeAddress);
row = sheet.CreateRow(1);
cell = row.CreateCell(0);
cell.SetCellValue("今日工作");
cell.CellStyle = style;//设置样式
cell = row.CreateCell(1);
cell.SetCellValue("问题点");
cell.CellStyle = style;//设置样式
cell = row.CreateCell(2);
cell.SetCellValue("解决状态");
cell.CellStyle = style;//设置样式
sheet.SetColumnWidth(0, 50 * 256);
sheet.SetColumnWidth(1, 50 * 256);
//数据 循环填充内容部分
row = sheet.CreateRow(2);
cell = row.CreateCell(0);
cell.SetCellValue("今日工作内容001");
cell = row.CreateCell(1);
cell.SetCellValue("问题点001");
cell = row.CreateCell(2);
cell.SetCellValue("100%");
row = sheet.CreateRow(3);//空行
//合并单元格
rowCellRangeAddress = new CellRangeAddress(3, 3, 0, 2);
sheet.AddMergedRegion(rowCellRangeAddress);
row = sheet.CreateRow(4);
cell = row.CreateCell(0);
cell.SetCellValue("明日计划");
cell.CellStyle = style;//设置样式
sheet.SetColumnWidth(0, 50 * 256);
//合并单元格
rowCellRangeAddress = new CellRangeAddress(4, 4, 0, 2);
sheet.AddMergedRegion(rowCellRangeAddress);
//数据 循环填充内容部分
row = sheet.CreateRow(5);
cell = row.CreateCell(0);
cell.SetCellValue("明日计划内容001");
//合并单元格
rowCellRangeAddress = new CellRangeAddress(5, 5, 0, 2);
sheet.AddMergedRegion(rowCellRangeAddress);
row = sheet.CreateRow(6);//空行
//合并单元格
rowCellRangeAddress = new CellRangeAddress(6, 6, 0, 2);
sheet.AddMergedRegion(rowCellRangeAddress);
row = sheet.CreateRow(7);
cell = row.CreateCell(0);
cell.SetCellValue("图片列表");
cell.CellStyle = style;//设置样式
sheet.SetColumnWidth(0, 50 * 256);
//合并单元格
rowCellRangeAddress = new CellRangeAddress(7, 7, 0, 2);
sheet.AddMergedRegion(rowCellRangeAddress);
//数据 循环填充内容部分
row = sheet.CreateRow(8);
cell = row.CreateCell(0);
//合并单元格
rowCellRangeAddress = new CellRangeAddress(8, 8, 0, 2);
sheet.AddMergedRegion(rowCellRangeAddress);
}
/// <summary>
/// Datable导出成Excel 设置样式示例
/// </summary>
/// <param name="dt">导出存放文件夹路径</param>
/// <param name="Route">文件夹路径</param>
/// <param name="file">导出路径(包括文件名与扩展名),注：年月日时分秒+文件名</param>
public static void TableToExcelStyle(DataTable dt, string Route, string file)
{
//string[] fileArr = file.Split(new char[] { '.' });
//file = fileArr[0] + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + fileArr[1];
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//设置列宽
//sheet.SetColumnWidth(0, 15 * 256);
//sheet.SetColumnWidth(1, 15 * 256);
//sheet.SetColumnWidth(2, 15 * 256);
//sheet.SetColumnWidth(3, 15 * 256);
//sheet.SetColumnWidth(4, 15 * 256);
//sheet.SetColumnWidth(5, 25 * 256);
//sheet.SetColumnWidth(6, 25 * 256);
//sheet.SetColumnWidth(7, 30 * 256);
//sheet.SetColumnWidth(8, 15 * 256);
//sheet.SetColumnWidth(9, 15 * 256);
//sheet.SetColumnWidth(10, 15 * 256);
//sheet.SetColumnWidth(12, 15 * 256);
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// sheet.SetColumnWidth(i, 20 * 256);
//}
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count - 1; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
ICellStyle style = workbook.CreateCellStyle();
ICellStyle style2 = workbook.CreateCellStyle();
string val = "";
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count - 1; j++)
{
ICell cell = row1.CreateCell(j);
val = dt.Rows[i][j].ToString();
cell.SetCellValue(val);
if (val == "通过")
{
//设置单元格颜色
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
style.FillPattern = FillPattern.SolidForeground;
//字体颜色
//创建字体
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
//给字体设置颜色
ffont.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
//给样式添加字体
style.SetFont(ffont);
cell.CellStyle = style;//设置
}
if (val == "测试1报废")
{
//设置单元格颜色
style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Orange.Index;
style2.FillPattern = FillPattern.SolidForeground;
//字体颜色
//创建字体
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
//给字体设置颜色
ffont.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
//给样式添加字体
style2.SetFont(ffont);
cell.CellStyle = style2;//设置
}
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream, true);
var buf = stream.ToArray();
//如找不到文件夹则创建
if (!Directory.Exists(Route))
{
Directory.CreateDirectory(Route);
}
//保存为Excel文件
using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
fs.Close();
fs.Dispose();
}
}
/// <summary>
/// 复制文件
/// </summary>
/// <param name="sourceFilePath"></param>
/// <param name="newFilePath"></param>
public static void CopyTextToNewWorkbook(string sourceFilePath, string newFilePath)
{
/*
* 可读取excel的公式数据和引用外部数据
DataTable dt = new DataTable();
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(sourceFilePath);
Worksheet ws = wk.Worksheets[0];
int a = ws.Cells.Rows.Count;
int b = ws.Cells.Columns.Count;
dt = ws.Cells.ExportDataTable(0, 0, 9, 9);
dt = ws.Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow + 1, ws.Cells.MaxDataColumn + 1, true);
dt = ws.Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow + 1, ws.Cells.MaxDataColumn + 1);
*/
// 加载现有的工作簿
using (FileStream fileStream = new FileStream(sourceFilePath, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
// 创建新的工作簿
XSSFWorkbook newWorkbook = new XSSFWorkbook();
// 复制每个工作表
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
ISheet newSheet = newWorkbook.CreateSheet(sheet.SheetName);
// 复制行
for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow row = sheet.GetRow(rowNum);
IRow newRow = newSheet.CreateRow(rowNum);
// 复制单元格
if (row != null)
{
for (int cellNum = 0; cellNum < row.LastCellNum; cellNum++)
{
ICell cell = row.GetCell(cellNum);
ICell newCell = newRow.CreateCell(cellNum);
if (cell != null)
{
//newCell.CellStyle = cell.CellStyle;
//newCell.SetCellType(cell.CellType);
// 根据单元格类型复制数据
switch (cell.CellType)
{
case CellType.String:
newCell.SetCellValue(cell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(cell.NumericCellValue);
break;
// ...其他类型
default:
break;
}
}
}
}
}
}
// 保存新工作簿到文件
using (FileStream newFileStream = new FileStream(newFilePath, FileMode.Create, FileAccess.Write))
{
newWorkbook.Write(newFileStream);
}
}
}
/// <summary>
/// Excel导入成Datable
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);// GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
{
obj = obj.ToString().Trim();
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = sheet.GetRow(i).GetCell(j);// GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);// GetValueType(sheet.GetRow(i).GetCell(j));
dr[j] = dr[j].ToString().Trim();
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// Excel导入成Datable
/// 此方法可读取excel的公式数据和引用外部数据
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
public static DataTable ExportDataTable(string file)
{
DataTable dt = new DataTable();
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(file);
Worksheet ws = wk.Worksheets[0];
int row = ws.Cells.MaxDataRow + 1;
int col = ws.Cells.MaxDataColumn + 1;
dt = ws.Cells.ExportDataTable(0, 0, row, col, true);
return dt;
}
/// <summary>
/// Excel导入成多个Datable 批量
/// 此方法可读取excel的公式数据和引用外部数据
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
public static List<DataTable> ExportDataTableList(string file)
{
List<DataTable> tables = new List<DataTable>();
DataTable dt = new DataTable();
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(file);
int Count = wk.Worksheets.Count;
for (int i = 0; i < Count; i++)
{
Worksheet ws = wk.Worksheets[i];
int row = ws.Cells.MaxDataRow + 1;
int col = ws.Cells.MaxDataColumn + 1;
dt = ws.Cells.ExportDataTable(0, 0, row, col, true);
tables.Add(dt);
}
return tables;
}
/// <summary>
/// Excel导入成Datable
/// 此方法可读取excel的公式数据和引用外部数据
/// 此方法会将单元格内容全部转为字符串类型
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
public static DataTable ExportDataTableAsString(string file)
{
DataTable dt = new DataTable();
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(file);
Worksheet ws = wk.Worksheets[0];
int row = ws.Cells.MaxDataRow + 1;
int col = ws.Cells.MaxDataColumn + 1;
//dt = ws.Cells.ExportDataTable(0, 0, row, col, true);
//这个方法会将单元格内容全部转为字符串类型，以防有的列既有数字又有字符会报错
dt = ws.Cells.ExportDataTableAsString(0, 0, row, col, true);
return dt;
}
/// <summary>
/// Excel导入成多个Datable 批量
/// 此方法可读取excel的公式数据和引用外部数据
/// 此方法会将单元格内容全部转为字符串类型
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
public static List<DataTable> ExportDataTableAsStringList(string file)
{
List<DataTable> tables = new List<DataTable>();
DataTable dt = new DataTable();
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(file);
int Count = wk.Worksheets.Count;
for (int i = 0; i < Count; i++)
{
Worksheet ws = wk.Worksheets[i];
int row = ws.Cells.MaxDataRow + 1;
int col = ws.Cells.MaxDataColumn + 1;
//dt = ws.Cells.ExportDataTable(0, 0, row, col, true);
//这个方法会将单元格内容全部转为字符串类型，以防有的列既有数字又有字符会报错
dt = ws.Cells.ExportDataTableAsString(0, 0, row, col, true);
tables.Add(dt);
}
return tables;
}
public static void TemplateExcle()
{
FileStream file = new FileStream(@"template/book1.xls", FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
sheet1.GetRow(1).GetCell(1).SetCellValue(200200);
sheet1.GetRow(2).GetCell(1).SetCellValue(300);
sheet1.GetRow(3).GetCell(1).SetCellValue(500050);
sheet1.GetRow(4).GetCell(1).SetCellValue(8000);
sheet1.GetRow(5).GetCell(1).SetCellValue(110);
sheet1.GetRow(6).GetCell(1).SetCellValue(100);
sheet1.GetRow(7).GetCell(1).SetCellValue(200);
sheet1.GetRow(8).GetCell(1).SetCellValue(210);
sheet1.GetRow(9).GetCell(1).SetCellValue(2300);
sheet1.GetRow(10).GetCell(1).SetCellValue(240);
sheet1.GetRow(11).GetCell(1).SetCellValue(180123);
sheet1.GetRow(12).GetCell(1).SetCellValue(150);
//Force excel to recalculate all the formula while open
sheet1.ForceFormulaRecalculation = true;
file = new FileStream(@"test.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.DateCellValue;
//case CellType.Numeric: //NUMERIC:
// return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
/// <summary>
/// 插入图片
/// </summary>
/// <param name="workbook">工作表格</param>
/// <param name="patriarch">图片路径</param>
/// <param name="path"></param>
/// <param name="sheet">表格</param>
/// <param name="rowline">第几行</param>
/// <param name="col">第几列</param>
private static void setPic(HSSFWorkbook workbook, HSSFPatriarch patriarch, string path, ISheet sheet, int rowline, int col)
{
//string contentRootPath = _hostingEnvironment.ContentRootPath;
path = "";// System.Web.HttpContext.Current.Server.MapPath(path);
if (string.IsNullOrEmpty(path))
{
return;
}
if (!System.IO.File.Exists(path))
{
return;
}
byte[] bytes = System.IO.File.ReadAllBytes(path);
//MemoryStream ms = new MemoryStream(bytes);
//Image Img = Bitmap.FromStream(ms, true);
//double ImageOriginalWidth = Img.Width;//原始图片的长度
//double ImageOriginalHeight = Img.Height;//原始图片的宽度
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
//关于HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)的参数
//dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
//dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
//dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
//dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
//col1：起始单元格列序号，从0开始计算；
//row1：起始单元格行序号，从0开始计算，如例子中col1 = 0,row1 = 0就表示起始单元格为A1；
//col2：终止单元格列序号，从0开始计算；
//row2：终止单元格行序号，从0开始计算，如例子中col2 = 2,row2 = 2就表示起始单元格为C3；
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, rowline, col + 1, rowline + 1);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//anchor.AnchorType = AnchorType.MoveDontResize;
//ImageOriginalWidth = ImageOriginalWidth / 50;
//ImageOriginalHeight = ImageOriginalHeight / 50;
pict.Resize(1, 10);
}
}
}
