从FTP上下载文档,到本地:
public byte[] Create_CIT_Report(string SN, string MODEL)
{
string fileName = "CIT报告.xlsx";
//从FTP上把报告模板下载到项目本地路径,命名为 CIT报告.xlsx
using (var client = new SftpClient("10.10.10.10", 8004, "test", "test")) //创建连接对象
{
client.Connect(); //连接
using (FileStream file = File.OpenWrite(fileName))
{
if(MODEL == "X3")
{
client.DownloadFile("IMAS_X3/CIT/CIT报表模板_X3.xlsx", file);
}
else
{
client.DownloadFile("IMAS_X4/CIT/CIT报表模板_X4.xlsx", file);
}
}
}
DataTable Dt = IMAS_DAL_CIT.Get_CIT_Info(SN, MODEL);//获取SN的相关信息
DataTable Dt_Img = IMAS_DAL_CIT.Get_CIT_Photo(SN, MODEL);//获取图片信息
//把图片插入到 项目路径下的Excel文档:CIT报告.xlsx
using (FileStream fileStream = new FileStream(fileName, FileMode.Open))
{
fileStream.Position = 0;
IWorkbook workbook = new XSSFWorkbook(fileStream);
ISheet sheet = workbook.GetSheetAt(0);
//Dt里的数据赋值到excel对应的单元格里
if (Dt.Rows.Count > 0)
{
for (int i = 0; i < 157; i++)
{
if( i==153 || i == 154)
{
}
else
{
sheet.GetRow(i).GetCell(1).SetCellValue(Dt.Rows[0][i].ToString()); //给单元格赋值
}
}
}
using (var client = new SftpClient("10.10.10.10", 8004, "test", "test")) //创建连接对象
{
client.Connect(); //连接
if (Dt_Img.Rows.Count > 0)
{
//创建画布
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//判断图片是否存在,存在则插入到excel对应的位置
//将图片添加到工作簿中,返回值为该图片在工作表中的索引(从0开始)
//如果工作簿没有图片,那改图片为excel的第一张图片,索引为0,
//同理如果excel中已有一张图片,执行下面语句,该图片为excel第二张图片,索引为1
if (!string.IsNullOrEmpty(Dt_Img.Rows[0]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[0]["FILE_PATH"].ToString());//根据FTP路径获取到图片,转为二进制
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG); //该二进制图片的ID,也就是excel中第几张图片,默认从0开始
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 12, 4, 13);//需要放到excel的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);//把图片放到对应的我位置
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[1]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[1]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 13, 4, 14);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[2]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[2]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 14, 4, 15);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[3]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[3]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 14, 4, 15);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[4]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[4]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 20, 4, 21);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[5]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[5]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 23, 4, 24);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[6]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[6]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 23, 4, 24);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[7]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[7]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 24, 4, 31);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[8]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[8]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 31, 4, 32);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[9]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[9]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 51, 4, 52);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[10]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[10]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 52, 4, 59);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[11]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[11]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 59, 4, 79);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[12]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[12]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 80, 4, 100);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[13]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[13]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 45, 4, 46);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[14]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[14]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 46, 4, 51);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[15]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[15]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 147, 4, 148);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[16]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[16]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 153, 4, 154);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
if (!string.IsNullOrEmpty(Dt_Img.Rows[17]["FILE_PATH"].ToString()))
{
byte[] imgByte = client.ReadAllBytes(Dt_Img.Rows[17]["FILE_PATH"].ToString());
int pictureId = workbook.AddPicture(imgByte, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 3, 154, 4, 155);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureId);
}
}
client.Dispose();
}
FileStream file = new FileStream(fileName, FileMode.Create);
workbook.Write(file);
fileStream.Dispose();
file.Dispose();
workbook.Close();
};
/*处理好的excel文档存到buffer里,并删除原本的excel文档*/
byte[] buffer = System.IO.File.ReadAllBytes(fileName);
//删除文件
if (System.IO.File.Exists(fileName))
{
File.Delete(fileName);
}
return buffer; //处理好的文档返回出去
}