1 使用office组件实现 把execl中的图片导入到数据库中
/// <summary>
/// 把execl导入到数据库中
/// </summary>
/// <param name="bigFilePath">execl的路径</param>
/// <returns></returns>
public List<string> ReadExcelToDS(string bigFilePath)
{
StringBuilder chongfu = new StringBuilder();
string weikou = "";
List<string> lstResult = new List<string>();
string _strFilePath = string.Empty;
_strFilePath = bigFilePath;
try
{
#region 代码段2
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//引用Excel对象
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(_strFilePath);
excel.UserControl = true;
excel.Visible = false;
for (int i = 0; i < workbook.Worksheets.Count; i++) //循环取所有的Sheet.
{
Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Worksheets.get_Item(i + 1) as Microsoft.Office.Interop.Excel.Worksheet;//从１开始.
List<MsExcel> _excels = new List<MsExcel>();//自定义的message类
for (int row = StartRow; row <= sheet.UsedRange.Rows.Count; row++)
{
MsExcel _excel = new MsExcel();
//DRUG_A1_20到DRUG_A1_150是execl的文本字段
_excel.DRUG_A1_20 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 1])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_40 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 3])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_50 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 4])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_60 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 5])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_70 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 6])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_80 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 7])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_90 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 8])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_100 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 9])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_110 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 10])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_120 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 11])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_130 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 12])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_140 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 13])).Text.ToString().Trim('\r').Trim('\n').Trim();
_excel.DRUG_A1_150 = ((Microsoft.Office.Interop.Excel.Range)(sheet.Cells[row, 14])).Text.ToString().Trim('\r').Trim('\n').Trim();
//图片放在execl的第一列
Microsoft.Office.Interop.Excel.Shape _shape = sheet.Shapes.Item(row - StartRow + 1) as Microsoft.Office.Interop.Excel.Shape;
// 把图片拷贝到剪切板中
_shape.CopyPicture(Appearance.Button, Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlBitmap); //COPY到内存。
//用调用线程的方法取得剪切板的值 CopyToClipboard() 这个方法在下边
Thread cbThread = new Thread(() => { CopyToClipboard(ref _excel); });
cbThread.TrySetApartmentState(ApartmentState.STA);
cbThread.Start();
cbThread.Join();
string k1 = string.Empty;
string v1 = string.Empty;
}
//此方法就是把取到的数据 插入到数据库中 数据库已二进制的方式存放数据
InsertToServer(_excels, id);
}
workbook.Close(false, null, null);
excel.Quit();
#endregion
}
catch (System.Exception e)
{
lstResult.Add("-1");
lstResult.Add("导入失败!请检查execl格式是否正确！" + e.Message);
}
lstResult.Add("1");
lstResult.Add("导入成功!");
return lstResult;
}
}
/// <summary>
/// 线程调用 （图片已经拷贝到剪切板），因为图片在剪切板中示意二进制存放的 所以此方法就是把剪切板中的二进制数据取出放到MsExcel中
/// </summary>
[STAThread]
protected void CopyToClipboard( ref MsExcel _excel)
{
byte[] _imageData = null;
IDataObject iData = System.Windows.Forms.Clipboard.GetDataObject();
if (iData.GetDataPresent(DataFormats.Bitmap))
{
System.Drawing.Image _image = (Bitmap)iData.GetData(DataFormats.Bitmap); //从内存取值；
string _strImageFilePath = string.Empty;
_strImageFilePath = System.Environment.CurrentDirectory + @"\" + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".jpg";
_image.Save(_strImageFilePath); //保存。
FileInfo fi = new FileInfo(_strImageFilePath);
int imageDataLen = (int)fi.Length;
byte[] imageData = new byte[imageDataLen];
Stream imageDataStream = fi.OpenRead();
int n = imageDataStream.Read(imageData, 0, imageDataLen);
imageDataStream.Close();
using (MemoryStream _stream = new MemoryStream())
{
Bitmap bmp = new Bitmap(_image);
bmp.Save(_stream, System.Drawing.Imaging.ImageFormat.Jpeg);//将图像以指定的格式存入缓存内存流
_imageData = new byte[_stream.Length];
_stream.Position = 0;
_stream.Read(_imageData, 0, Convert.ToInt32(_imageData.Length));
_stream.Close();
}
}
Clipboard.Clear();
_excel.DRUG_A1_30 = _imageData;// DRUG_A1_30 是byte[] 类型的
}
//把带图片的数据插入到数据库中
private void InsertToServer(List<MsExcel> _excels,int id)
{
try
{
int iExecuteRow = 0;
StringBuilder sbCmdText = new StringBuilder();
StringBuilder sbCmdText12 = new StringBuilder();
foreach (MsExcel _excel in _excels)
{
//查重 如果有重复数据则删除库中的重复数据
sbCmdText12.Append(" delete from DRUG_A1 where ");
sbCmdText12.Append(" drug_a1_80='").Append(_excel.DRUG_A1_80.ToString()).Append("'");
sbCmdText12.Append(" and drug_a1_90='").Append(_excel.DRUG_A1_90.ToString()).Append("'");
sbCmdText12.Append(" and drug_a1_110='").Append(_excel.DRUG_A1_110.ToString()).Append("'");
int i = DataAccessor.ExecuteNonQuery(sbCmdText12.ToString());
sbCmdText12.Clear();
sbCmdText.Append(" INSERT INTO DRUG_A1(DRUG_A1_10,DRUG_A1_20,DRUG_A1_30,DRUG_A1_40,DRUG_A1_50,DRUG_A1_60, ");
sbCmdText.Append(" DRUG_A1_70,DRUG_A1_80,DRUG_A1_90,DRUG_A1_100,DRUG_A1_110,DRUG_A1_120,DRUG_A1_130, ");
sbCmdText.Append(" DRUG_A1_140,DRUG_A1_150,DRUG_A1_170,DRUG_A1_180) ");
sbCmdText.Append(" VALUES(@ID,@BianHao,@JieGouShi,@JieGouShuXing,@JieGouZuCheng,@FenZiShi,@FeiZiLiang,@KuBianHao,");
sbCmdText.Append(" @YuanBianHao,@RongJieXing,@KuWeiZhi,@HeChengZhe,@KuCunLiang,@RongDian,");
sbCmdText.Append(" @PingZhong,@DateTime,@TiaoMaMessage)");
IDataParameter _ID = DataAccessor.GetDataParameter("ID", DataType.Int);
IDataParameter _BianHao = DataAccessor.GetDataParameter("BianHao", DataType.String);
IDataParameter _JieGouShi = DataAccessor.GetDataParameter("JieGouShi", DataType.Binary);
IDataParameter _JieGouShuXing = DataAccessor.GetDataParameter("JieGouShuXing", DataType.String);
IDataParameter _JieGouZuCheng = DataAccessor.GetDataParameter("JieGouZuCheng", DataType.String);
IDataParameter _FenZiShi = DataAccessor.GetDataParameter("FenZiShi", DataType.String);
IDataParameter _FeiZiLiang = DataAccessor.GetDataParameter("FeiZiLiang", DataType.String);
IDataParameter _KuBianHao = DataAccessor.GetDataParameter("KuBianHao", DataType.String);
IDataParameter _YuanBianHao = DataAccessor.GetDataParameter("YuanBianHao", DataType.String);
IDataParameter _RongJieXing = DataAccessor.GetDataParameter("RongJieXing", DataType.String);
IDataParameter _KuWeiZhi = DataAccessor.GetDataParameter("KuWeiZhi", DataType.String);
IDataParameter _HeChengZhe = DataAccessor.GetDataParameter("HeChengZhe", DataType.String);
IDataParameter _KuCunLiang = DataAccessor.GetDataParameter("KuCunLiang", DataType.String);
IDataParameter _RongDian = DataAccessor.GetDataParameter("RongDian", DataType.String);
IDataParameter _PingZhong = DataAccessor.GetDataParameter("PingZhong", DataType.String);
IDataParameter _DateTime = DataAccessor.GetDataParameter("DateTime", DataType.String);
IDataParameter _TiaoMaMessage = DataAccessor.GetDataParameter("TiaoMaMessage", DataType.String);
id++;
_ID.Value = id;
_BianHao.Value = _excel.DRUG_A1_20;
_JieGouShi.Value = _excel.DRUG_A1_30;
_JieGouShuXing.Value = _excel.DRUG_A1_40;
_JieGouZuCheng.Value = _excel.DRUG_A1_50;
_FenZiShi.Value = _excel.DRUG_A1_60;
_FeiZiLiang.Value = _excel.DRUG_A1_70;
_KuBianHao.Value = _excel.DRUG_A1_80;
_YuanBianHao.Value = _excel.DRUG_A1_90;
_RongJieXing.Value = _excel.DRUG_A1_100;
_KuWeiZhi.Value = _excel.DRUG_A1_110;
_HeChengZhe.Value = _excel.DRUG_A1_120;
_KuCunLiang.Value = _excel.DRUG_A1_130;
_RongDian.Value = _excel.DRUG_A1_140;
_PingZhong.Value = _excel.DRUG_A1_150;
_TiaoMaMessage.Value = Base64Lib.Encode(_excel.DRUG_A1_80.ToString().Trim() +"^"+ _excel.DRUG_A1_110.ToString().Trim());
_DateTime.Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
IDataParameter[] parameters = new IDataParameter[] { _ID, _BianHao, _JieGouShi, _JieGouShuXing, _JieGouZuCheng, _FenZiShi, _FeiZiLiang, _KuBianHao, _YuanBianHao, _RongJieXing, _KuWeiZhi, _HeChengZhe, _KuCunLiang, _RongDian, _PingZhong, _DateTime, _TiaoMaMessage };
iExecuteRow = DataAccessor.ExecuteNonQuery(sbCmdText.ToString(), parameters);
sbCmdText.Clear();
}
}
catch (Exception ex)
{
throw ex;
}
}
需要注意的是 如果是web 项目 在调用office组件是会遇到一些权限问题 导致 发布后程序 不能使用
为了解决这个问题 我在开发web 程序时使用了 NPOI 第三方组件 这样就可以不依赖office组件导入execl
下边是 使用NPOI 的方法
/// <summary>
/// 把execl导入到数据库中
/// </summary>
/// <param name="bigFilePath">execl的路径</param>
/// <param name="path">图片的临时导入路径</param>
/// <returns></returns>
public List<string> ReadExcelToDS(string bigFilePath, string path)
{
StringBuilder chongfu = new StringBuilder();
string weikou = "";
List<string> lstResult = new List<string>();
string _strFilePath = string.Empty;
_strFilePath = bigFilePath;
int endid = 0;
int shuliang = 0;
try
{
#region 方法2引用NPOI的DLL文件
FileStream file = new FileStream(_strFilePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook = new HSSFWorkbook(file);//得到execl的所有sheet
IList pictures = workbook.GetAllPictures();//得到execl中所有图片
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);//取execl的sheet1
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
List<MsExcel> _excels = new List<MsExcel>();
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
MsExcel _excel = new MsExcel();
if (row.GetCell(0) != null)
{
_excel.DRUG_A1_20 = row.GetCell(0).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_20 = "";
}
if (row.GetCell(2) != null)
{
_excel.DRUG_A1_40 = row.GetCell(2).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_40 = "";
}
if (row.GetCell(3) != null)
{
_excel.DRUG_A1_50 = row.GetCell(3).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_50 = "";
}
if (row.GetCell(4) != null)
{
_excel.DRUG_A1_60 = row.GetCell(4).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_60 = "";
}
if (row.GetCell(5) != null)
{
_excel.DRUG_A1_70 = row.GetCell(5).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_70 = "";
}
if (row.GetCell(6) != null)
{
_excel.DRUG_A1_80 = row.GetCell(6).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_80 = "";
}
if (row.GetCell(7) != null)
{
_excel.DRUG_A1_90 = row.GetCell(7).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_90 = "";
}
if (row.GetCell(8) != null)
{
_excel.DRUG_A1_100 = row.GetCell(8).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_100 = "";
}
if (row.GetCell(9) != null)
{
_excel.DRUG_A1_110 = row.GetCell(9).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_110 = "";
}
if (row.GetCell(10) != null)
{
_excel.DRUG_A1_120 = row.GetCell(10).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_120 = "";
}
if (row.GetCell(11) != null)
{
_excel.DRUG_A1_130 = row.GetCell(11).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_130 = "";
}
if (row.GetCell(12) != null)
{
_excel.DRUG_A1_140 = row.GetCell(12).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_140 = "";
}
if (row.GetCell(13) != null)
{
_excel.DRUG_A1_150 = row.GetCell(13).ToString().Trim('\r').Trim('\n').Trim();
}
else
{
_excel.DRUG_A1_150 = "";
}
if (i <= pictures.Count)
{
//循环取每一个图片(图片是以二进制数据流的方式存放在pictures)
HSSFPictureData pic = (HSSFPictureData)pictures[i - 1];
//此方法是改一下图片二进制流的格式（以为我的图片还要以二进制流的方式下载到手机上（手机使用 sql server compact 3.5数据库） 所以需要转换一下格式）
_excel.DRUG_A1_30 = CopyToClipboard1(pic, path);
// 如果只是web程序 就用下边的方法即可
_excel.DRUG_A1_30 = pic.Data;
}
else
{
byte[] img = new byte[] { 0 };
_excel.DRUG_A1_30 = (byte[])img;
}
}
//此方法就是把取到的数据 插入到数据库中 数据库已二进制的方式存放数据
InsertToServer(_excels, id);
file.Close();
workbook = null;
sheet = null;
#endregion
}
catch (System.Exception e)
{
lstResult.Add("-1");
lstResult.Add("导入失败!请检查execl格式是否正确！"+e.Message);
}
lstResult.Add("1");
lstResult.Add("导入成功!");
return lstResult;
}
//此方法是为了让二进制流的图片 能直接插入到 sql server compact 3.5数据库中
private byte[] CopyToClipboard1(HSSFPictureData pic, string path)
{
byte[] _imageData = null;
System.Drawing.Image _image1 = System.Drawing.Image.FromStream(new MemoryStream(pic.Data));
string _strImageFilePath = string.Empty;
//文件上传路径
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
_strImageFilePath = path + @"\" + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".jpg";
_image1.Save(_strImageFilePath); //保存。
FileInfo fi = new FileInfo(_strImageFilePath);
int imageDataLen = (int)fi.Length;
byte[] imageData = new byte[imageDataLen];
Stream imageDataStream = fi.OpenRead();
int n = imageDataStream.Read(imageData, 0, imageDataLen);
imageDataStream.Close();
string ext = pic.SuggestFileExtension();
using (MemoryStream _stream = new MemoryStream())
{
Bitmap bmp = new Bitmap(_image1);
if (ext.Equals("jpeg"))
{
bmp.Save(_stream, System.Drawing.Imaging.ImageFormat.Jpeg);//将图像以指定的格式存入缓存内存流
}
else
{
bmp.Save(_stream, System.Drawing.Imaging.ImageFormat.Png);//将图像以指定的格式存入缓存内存流
}
_imageData = new byte[_stream.Length];
_stream.Position = 0;
_stream.Read(_imageData, 0, Convert.ToInt32(_imageData.Length));
_stream.Close();
}
System.IO.File.Delete(_strImageFilePath);
return _imageData;
}
//把带图片的数据插入到数据库中
private void InsertToServer(List<MsExcel> _excels,int id)
{
try
{
int iExecuteRow = 0;
StringBuilder sbCmdText = new StringBuilder();
StringBuilder sbCmdText12 = new StringBuilder();
foreach (MsExcel _excel in _excels)
{
//查重 如果有重复数据则删除库中的重复数据
sbCmdText12.Append(" delete from DRUG_A1 where ");
sbCmdText12.Append(" drug_a1_80='").Append(_excel.DRUG_A1_80.ToString()).Append("'");
sbCmdText12.Append(" and drug_a1_90='").Append(_excel.DRUG_A1_90.ToString()).Append("'");
sbCmdText12.Append(" and drug_a1_110='").Append(_excel.DRUG_A1_110.ToString()).Append("'");
int i = DataAccessor.ExecuteNonQuery(sbCmdText12.ToString());
sbCmdText12.Clear();
sbCmdText.Append(" INSERT INTO DRUG_A1(DRUG_A1_10,DRUG_A1_20,DRUG_A1_30,DRUG_A1_40,DRUG_A1_50,DRUG_A1_60, ");
sbCmdText.Append(" DRUG_A1_70,DRUG_A1_80,DRUG_A1_90,DRUG_A1_100,DRUG_A1_110,DRUG_A1_120,DRUG_A1_130, ");
sbCmdText.Append(" DRUG_A1_140,DRUG_A1_150,DRUG_A1_170,DRUG_A1_180) ");
sbCmdText.Append(" VALUES(@ID,@BianHao,@JieGouShi,@JieGouShuXing,@JieGouZuCheng,@FenZiShi,@FeiZiLiang,@KuBianHao,");
sbCmdText.Append(" @YuanBianHao,@RongJieXing,@KuWeiZhi,@HeChengZhe,@KuCunLiang,@RongDian,");
sbCmdText.Append(" @PingZhong,@DateTime,@TiaoMaMessage)");
IDataParameter _ID = DataAccessor.GetDataParameter("ID", DataType.Int);
IDataParameter _BianHao = DataAccessor.GetDataParameter("BianHao", DataType.String);
IDataParameter _JieGouShi = DataAccessor.GetDataParameter("JieGouShi", DataType.Binary);
IDataParameter _JieGouShuXing = DataAccessor.GetDataParameter("JieGouShuXing", DataType.String);
IDataParameter _JieGouZuCheng = DataAccessor.GetDataParameter("JieGouZuCheng", DataType.String);
IDataParameter _FenZiShi = DataAccessor.GetDataParameter("FenZiShi", DataType.String);
IDataParameter _FeiZiLiang = DataAccessor.GetDataParameter("FeiZiLiang", DataType.String);
IDataParameter _KuBianHao = DataAccessor.GetDataParameter("KuBianHao", DataType.String);
IDataParameter _YuanBianHao = DataAccessor.GetDataParameter("YuanBianHao", DataType.String);
IDataParameter _RongJieXing = DataAccessor.GetDataParameter("RongJieXing", DataType.String);
IDataParameter _KuWeiZhi = DataAccessor.GetDataParameter("KuWeiZhi", DataType.String);
IDataParameter _HeChengZhe = DataAccessor.GetDataParameter("HeChengZhe", DataType.String);
IDataParameter _KuCunLiang = DataAccessor.GetDataParameter("KuCunLiang", DataType.String);
IDataParameter _RongDian = DataAccessor.GetDataParameter("RongDian", DataType.String);
IDataParameter _PingZhong = DataAccessor.GetDataParameter("PingZhong", DataType.String);
IDataParameter _DateTime = DataAccessor.GetDataParameter("DateTime", DataType.String);
IDataParameter _TiaoMaMessage = DataAccessor.GetDataParameter("TiaoMaMessage", DataType.String);
id++;
_ID.Value = id;
_BianHao.Value = _excel.DRUG_A1_20;
_JieGouShi.Value = _excel.DRUG_A1_30;
_JieGouShuXing.Value = _excel.DRUG_A1_40;
_JieGouZuCheng.Value = _excel.DRUG_A1_50;
_FenZiShi.Value = _excel.DRUG_A1_60;
_FeiZiLiang.Value = _excel.DRUG_A1_70;
_KuBianHao.Value = _excel.DRUG_A1_80;
_YuanBianHao.Value = _excel.DRUG_A1_90;
_RongJieXing.Value = _excel.DRUG_A1_100;
_KuWeiZhi.Value = _excel.DRUG_A1_110;
_HeChengZhe.Value = _excel.DRUG_A1_120;
_KuCunLiang.Value = _excel.DRUG_A1_130;
_RongDian.Value = _excel.DRUG_A1_140;
_PingZhong.Value = _excel.DRUG_A1_150;
_TiaoMaMessage.Value = Base64Lib.Encode(_excel.DRUG_A1_80.ToString().Trim() +"^"+ _excel.DRUG_A1_110.ToString().Trim());
_DateTime.Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
IDataParameter[] parameters = new IDataParameter[] { _ID, _BianHao, _JieGouShi, _JieGouShuXing, _JieGouZuCheng, _FenZiShi, _FeiZiLiang, _KuBianHao, _YuanBianHao, _RongJieXing, _KuWeiZhi, _HeChengZhe, _KuCunLiang, _RongDian, _PingZhong, _DateTime, _TiaoMaMessage };
iExecuteRow = DataAccessor.ExecuteNonQuery(sbCmdText.ToString(), parameters);
sbCmdText.Clear();
}
}
catch (Exception ex)
{
throw ex;
}
}
1050
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
