1.数据库 设计类型
FileContent nvarchar(100) 存储文件流
npoi 绘制 文档 workbook
HSSFWorkbook workbook = new HSSFWorkbook();
具体操作省略....
绘制好后存储至服务器某个文件夹下
string source = System.Windows.Forms.Application.StartupPath + "\\Temp";//文件路径
string FileName = "月度生产经营数据" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
string FilePath = System.Windows.Forms.Application.StartupPath + "\\Temp\\" + FileName;//文件路径
//如果路径不存在,创建路径
if (!Directory.Exists(source))
{
Directory.CreateDirectory(source); //创建一个文件路径 存储文档信息
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
#region excel 文件信息入库
FileInfor model =new FileInfor();
model.FileName = "文件名称";
model.FilePath = FileName;
model.FileSize = new System.IO.FileInfo(FilePath).Length.ToString();
model.FileType = ".xls";
model.CreateDate = now.ToString();
model.StartDate = week1.ToString("yyyy-MM-dd");
model.EndedDate = week5.ToString("yyyy-MM-dd");
byte[] stream_file = FileToStream(FilePath);
model.FileContent = stream_file; //文件转流
string sql = data.sqlMonthProducOperate(model);
textBox1.AppendText(sql);
#endregion
SQL server 入库 简易版
public string sqlMonthProducOperate(FileInfor data)
{
try
{
//接口 链接方式
//string aa = WebConfigurationManager.AppSettings["ConnectionString"];
//winfrom 链接方式
string aa = System.Configuration.ConfigurationSettings.AppSettings["connectionstr"];
using (SqlConnection con = new SqlConnection(aa))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(@" insert into [dbo].[Sys_MonthProducOperate]
([ID]
,[FileName]
,[FilePath]
,[FileType]
,[FileSize]
,[CreateDate]
,[StartDate]
,[EndedDate],FileContent) values
(NEWID(),'" + data.FileName + @"','" + data.FilePath + @"','" + data.FileType + @"','" + data.FileSize + @"'
,'" + data.CreateDate + @"','" + data.StartDate + @"','" + data.EndedDate + @"',@FileContent)", con))
{
cmd.Parameters.Add("@FileContent", SqlDbType.VarBinary).Value = data.FileContent; //此处注意写入类型
cmd.ExecuteNonQuery();
}
}
return "-- 日志 -- 成功-sql入库成功 ---" + DateTime.Now + "\r\n";
}
catch (Exception ex)
{
return "-- 日志 -- 失败-sql入库失败原因:"+ex.Message+" ---" + DateTime.Now + "\r\n";
}
}
}
public class FileInfor
{
public string FileName { get; set; }
public string FilePath { get; set; }
public string FileType { get; set; }
public string FileSize { get; set; }
public string CreateDate { get; set; }
public string StartDate { get; set; }
public string EndedDate { get; set; }
public byte[] FileContent { get; set; }
}
#region
/// <summary>
/// 文件转流
/// </summary>
/// <param name="fileName">文件路径</param>
/// <param name="isDelete">是否删除临时文件</param>
/// <returns></returns>
public static byte[] FileToStream(string fileName, bool isDelete = false)
{
//打开文件
FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read);
// 读取文件的 byte[]
byte[] bytes = new byte[fileStream.Length];
fileStream.Read(bytes, 0, bytes.Length);
fileStream.Close();
// 把 byte[] 转换成 Stream
//Stream stream = new MemoryStream(bytes);
if (isDelete)
{
File.Delete(fileName);//删除临时文件
}
return bytes;
}
#endregion