using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
namespace LC.UI.Controllers
{
using LC.Entity;
using LC.BLL;
public class Excel
{
/// <summary>
/// 导入
/// </summary>
/// <param name="excelFile"></param>
/// <returns></returns>
public static int ExcelAdd(HttpPostedFileBase excelFile)
{
var result = 0;
if (excelFile != null && !string.IsNullOrEmpty(excelFile.FileName))
{
IWorkbook wk = null;
var subName = excelFile.FileName.Substring(excelFile.FileName.LastIndexOf('.'), excelFile.FileName.Length - excelFile.FileName.LastIndexOf('.'));
var PicLst = new List<string>();
if (subName.Equals(".xls"))
{
//把xls文件中的数据写入wk中
wk = new HSSFWorkbook(excelFile.InputStream);
PicLst = wk.HSSFSaveAllPicture();
}
else if (subName.Equals(".xlsx"))
{
//把xlsx文件中的数据写入wk中
wk = new XSSFWorkbook(excelFile.InputStream);
PicLst = wk.XSSFSaveAllPicture();
}
ISheet sheet = wk.GetSheetAt(0);
IRow row = sheet.GetRow(1); //读取当前行数据
for (int k = 1; k <= sheet.LastRowNum; k++)
{
row = sheet.GetRow(k); //读取当前行数据
//string strsql = "insert into testtable (name,sex,img,age) values(";
VIPUsers info = new VIPUsers();
if (row != null)
{
//LastCellNum 是当前行的总列数
for (int j = 0; j < row.LastCellNum; j++)
{
//读取该行的第j列数据
if (j == 0)
{
//continue;
}
try
{
//string value = row.GetCell(j).ToString();
//strsql += "'" + value + "',";
info.Id = 0;
info.VIPNum= row.GetCell(1).ToString();
info.TId = Convert.ToInt32(row.GetCell(2).ToString());
info.Pwd = row.GetCell(3).ToString();
info.Qx = Convert.ToInt32(row.GetCell(4).ToString());
info.Name = row.GetCell(5).ToString();
info.Sex = Convert.ToInt32(row.GetCell(6).ToString());
var aa = row.GetCell(7).ToString();
info.CreateDate = Convert.ToDateTime(row.GetCell(7).ToString());
}
catch
{
//strsql += "'" + PicLst[k - 1] + "',";
}
}
//strsql = strsql.Substring(0, strsql.Length - 1) + ")";
result += new VIPUsersBLL().Add(info);
}
}
}
return result;
}
/// <summary>
/// 把实体
/// </summary>
/// <param name="t">要生成Excel的数据</param>
/// <param name="SavePath">保存的文件名</param>
/// <param name="ImgTitle">包含图片的列名</param>
public static string SaveExcel(DataTable dt, string SaveName, string ImgTitle)
{
dt.Columns["Id"].ColumnName = "序号";
dt.Columns["Name"].ColumnName = "姓名";
dt.Columns["Sex"].ColumnName = "性别";
dt.Columns["Age"].ColumnName = "年龄";
dt.Columns["Img"].ColumnName = "头像";
string Path = AppDomain.CurrentDomain.BaseDirectory + ("/Upload/Excel/");
string FileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
if (Directory.Exists(Path) == false)
{
Directory.CreateDirectory(Path);
}
XSSFWorkbook sf = new XSSFWorkbook();
var table = sf.CreateSheet("Sheet1");
for (int i = 0; i <= dt.Rows.Count; i++)
{
var row = table.CreateRow(i);
for (int k = 0; k < dt.Columns.Count; k++)
{
var cell = row.CreateCell(k);
if (i == 0)
{
cell.SetCellValue(dt.Columns[k].ColumnName);
}
else
{
if (dt.Columns[k].ColumnName == ImgTitle)
{
string FilePath = AppDomain.CurrentDomain.BaseDirectory.TrimEnd('\') + (dt.Rows[i - 1][ImgTitle]);
if (File.Exists(FilePath) == true)
{
byte[] bytes = System.IO.File.ReadAllBytes(FilePath);
Image jpg = Image.FromStream(new MemoryStream(bytes));//把图片流转为图片对象
row.Height = Convert.ToInt16(jpg.Height * 20);//设置单元格行的高度
table.SetColumnWidth(k, jpg.Width * 30);//设置单元格的宽度
int pictureIdx = -1;
if (FilePath.Contains("jpg"))
{
pictureIdx = sf.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_JPEG);
}
else if (FilePath.Contains("png"))
{
pictureIdx = sf.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
}
else if (FilePath.Contains("gif"))
{
pictureIdx = sf.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_GIF);
}
else if (FilePath.Contains("bmp"))
{
pictureIdx = sf.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_BMP);
}
var patriarch = table.CreateDrawingPatriarch();
var anchor = new XSSFClientAnchor(0, 0, 0, 0, k, i, k, i);
var pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
}
}
else
{
cell.SetCellValue(dt.Rows[i - 1][k].ToString());
}
}
}
}
//打开xls文件，如没有则创建，如存在则在创建是不要打开该文件
using (var fs = File.OpenWrite(Path + FileName))
{
sf.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return "导出成功#/Upload/Excel/" + FileName;
}
}
public static class NpoiExtend
{
public static List<string> HSSFSaveAllPicture(this IWorkbook wb)
{
List<string> ls = new List<string>();
string FilePath = AppDomain.CurrentDomain.BaseDirectory + ("/Upload/IMG/");
string FileName = DateTime.Now.ToString("yyyyMMddHHmmssfff");
if (Directory.Exists(FilePath) == false)
{
Directory.CreateDirectory(FilePath);
}
HSSFWorkbook wk = (HSSFWorkbook)wb;
IList pictures = wk.GetAllPictures();
for (int i = 0; i < pictures.Count; i++)
{
HSSFPictureData pic = (HSSFPictureData)pictures[i];
string ext = pic.SuggestFileExtension();//获取扩展名
string path = string.Empty;
if (ext.Equals("jpg"))
{
Image jpg = Image.FromStream(new MemoryStream(pic.Data));//从pic.Data数据流创建图片
path = Path.Combine(FilePath, string.Format(FileName + "_{0}.jpg", i++));
jpg.Save(path);//保存
}
else if (ext.Equals("png"))
{
Image png = Image.FromStream(new MemoryStream(pic.Data));
path = Path.Combine(FilePath, string.Format(FileName + "_{0}.png", i++));
png.Save(path);
}
if (!string.IsNullOrEmpty(path))
ls.Add(@"UploadIMG" + FileName + "_" + i + "." + ext);
}
return ls;
}
public static List<string> XSSFSaveAllPicture(this IWorkbook wb)
{
List<string> ls = new List<string>();
string FilePath = AppDomain.CurrentDomain.BaseDirectory + (@"UploadIMG");
string FileName = DateTime.Now.ToString("yyyyMMddHHmmssfff");
if (Directory.Exists(FilePath) == false)
{
Directory.CreateDirectory(FilePath);
}
XSSFWorkbook wk = (XSSFWorkbook)wb;
IList pictures = wk.GetAllPictures();
for (int i = 0; i < pictures.Count; i++)
{
XSSFPictureData pic = (XSSFPictureData)pictures[i];
//if (pic.Data.Length == 19504) //跳过不需要保存的图片，其中pic.data有图片长度
// continue;
string ext = pic.SuggestFileExtension();//获取扩展名
string path = string.Empty;
if (ext.Equals("jpg"))
{
Image jpg = Image.FromStream(new MemoryStream(pic.Data));//从pic.Data数据流创建图片
path = Path.Combine(FilePath, string.Format(FileName + "_{0}.jpg", i));
jpg.Save(path);//保存
}
else if (ext.Equals("png"))
{
Image png = Image.FromStream(new MemoryStream(pic.Data));
path = Path.Combine(FilePath, string.Format(FileName + "_{0}.png", i));
png.Save(path);
}
if (!string.IsNullOrEmpty(path))
ls.Add(@"UploadIMG" + FileName + "_" + i + "." + ext);
}
return ls;
}
}
}