//ЗАПИСЬ КАРТИНКИ В ЯЧЕЙКУ
public void AddPicture(string path, string cellAdress)
{
SelectCells(cellAdress, cellAdress);
_p = _xlWorkSheet.Pictures(_misValue) as Excel.Pictures;
_pic = _p.Insert(path, _misValue);
_pic.Left = Convert.ToDouble(_range.Left);
_pic.Top = Convert.ToDouble(_range.Top);
}
/// <summary>
/// 插入图片
/// </summary>
/// <param name="sheet"> 工作表 </param>
/// <param name="imageFilePath"> 图片的绝对路径 </param>
/// <param name="rowNumber"> 单元格行号 </param>
/// <param name="columnNumber"> 单元格列号 </param>
/// <returns></returns>
public Xls.Picture AddImage(Xls.Worksheet sheet, string imageFilePath, int
rowNumber, int columnNumber)
{
Xls.Range range = this.GetRange(sheet, rowNumber, columnNumber,
rowNumber, columnNumber);
range.Select();
Xls.Pictures pics = sheet.Pictures(_missing) as Xls.Pictures;
Xls.Picture pic = pics.Insert(imageFilePath, _missing);
pic.Left = (double)range.Left;
pic.Top = (double)range.Top;
return(pic);
}
/// <summary>
/// Chn 插入图片
/// </summary>
/// <param name="workSheet"></param>
/// <param name="picturePath"></param>
/// <param name="picLeft"></param>
/// <param name="picTop"></param>
/// <returns></returns>
public static Excel.Picture InsertPictureByPicture(this Excel.Worksheet workSheet, string picturePath, float picLeft, float picTop)
{
Size picSize = ImageHelper.GetDimensions(picturePath);
int picWidth = picSize.Width;
int picHeight = picSize.Height;
Excel.Pictures pics = (Excel.Pictures)workSheet.Pictures(Type.Missing);
Excel.Picture pic = pics.Insert(picturePath, Type.Missing);
pic.Left = (double)picLeft;
pic.Top = (double)picTop;
pic.Width = (double)picWidth;
pic.Height = (double)picHeight;
return(pic);
}
public static void WriteImage(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string personPath, float PictuteWidth, float PictureHeight)
{
if (_excelDoc != null)
{
try
{
MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex];
MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position);
_excelRge.Select();
if (PictuteWidth < 1 || PictureHeight < 1)
{
MSExcel.Pictures pics = (MSExcel.Pictures)_excelSht.Pictures(Missing.Value);
pics.Insert(personPath, Missing.Value);
}
else
{
float PicLeft = Convert.ToSingle(_excelRge.Left);
float PicTop = Convert.ToSingle(_excelRge.Top);
_excelSht.Shapes.AddPicture(personPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);
}
return;
}
catch (Exception ex)
{
Log.LogHelper.AddLog(@"异常130", ex.Message, true);
Log.LogHelper.AddLog(@"异常131", " " + ex.TargetSite.ToString(), true);
return;
}
}
else
{
Log.LogHelper.AddLog(@"异常32", @"文件没有正常打开，无法读取数据", true);
return;
}
}
private void Extract()
{
Excel.Application application = new Excel.Application {
Visible = false
};
Excel.Workbook workbook = application.Workbooks.Open(textBoxFilePath.Text);
Excel.Worksheet worksheet = workbook.Sheets[1];
Excel.Pictures pics = worksheet.Pictures(Missing.Value) as Excel.Pictures;
if (pics != null)
{
progressBar.Maximum = pics.Count;
for (var i = 1; i <= pics.Count; i++)
{
progressBar.Value = i;
try
{
pics.Item(i).CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap);
var image = Clipboard.GetImage();
string imageName = worksheet.Cells[pics.Item(i).TopLeftCell.Row, 3].Formula;
image?.Save(Path.Combine(textBoxImagePath.Text, imageName + ".bmp"));
}
catch (Exception)
{
// ignored
}
}
}
workbook.Close();
application.Quit();
MessageBox.Show(@"Image Extraction completed.", Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
Process.Start(textBoxImagePath.Text);
}
public static List <Product> writeProducts(List <Product> products)
{
List <Image> images = new List <Image>();
Excel.Pictures pics = xlws.Pictures(Missing.Value) as Excel.Pictures;
for (int x = 1; x <= pics.Count; x++)
{
pics.Item(x).CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap);
}
foreach (Excel.Range row in xlws.Rows)
{
if (row.Cells[1] != null)
{
products.Add(new Product(
row.Cells[1].ToString(),
int.Parse(row.Cells[2].ToString()),
int.Parse(row.Cells[3].ToString()),
row.Cells[5].ToString(),
null,
row.Cells[7].ToString()
));
}
}
}
public void PrintOrder(string path, System.Data.DataTable datatable, int LorR)
{
//Excel模板文件
string strFilePath = path;
if (!File.Exists(strFilePath))
{
throw new Exception("Excel条码模版不存在，无法导出");
}
//定义
Microsoft.Office.Interop.Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的电脑未安装Excel");
}
xlApp.Visible = false;
xlApp.UserControl = true;
xlApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(strFilePath); //目标文件
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//赋值
System.Data.DataTable dt = datatable;
if (dt.Rows.Count > 0)
{
if (Convert.ToInt32(dt.Rows[0]["LorR"]) == 0)
{
worksheet.Cells[1, 1] = "【左】"; //左座椅还是右
}
else
{
worksheet.Cells[1, 1] = "【右】";
}
string carType = dt.Rows[0]["CarType"].ToString();
worksheet.Cells[2, 2] = carType + "座椅分装单"; //第一行
worksheet.Cells[4, 2] = dt.Rows[0]["ProductNo"].ToString(); //第二行
BLL.T_JISA t_JISA = new T_JISA();
string JISASer = t_JISA.GetJISASer(dt.Rows[0]["ProductNo"].ToString());
worksheet.Cells[5, 2] = JISASer;
worksheet.Cells[6, 2] = dt.Rows[0]["CreateTime"].ToString(); //第二行
//worksheet.Cells[8,2] = dt.Rows[0]["ProductNo"].ToString(); //第二行
worksheet.Cells[11, 2] = dt.Rows[0]["CarModelName"].ToString(); //第二行
worksheet.Cells[13, 2] = dt.Rows[0]["Color"].ToString() + dt.Rows[0]["ColorCode"].ToString(); //第二行
int row = 15;
if (dt.Rows[0]["MasterBarCodeL"].ToString() != "")
{
worksheet.Cells[row++, 2] = "左前";
}
if (dt.Rows[0]["MasterBarCodeR"].ToString() != "")
{
worksheet.Cells[row++, 2] = "右前";
}
if (dt.Rows[0]["MasterBarCodeC"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后座椅整垫";
}
if (dt.Rows[0]["MasterBarCode40"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后背40%";
}
if (dt.Rows[0]["MasterBarCode60"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后背60%";
}
if (dt.Rows[0]["MasterBarCodeB"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后整背";
}
DataMatrix.net.DmtxImageEncoder dataMatix = new DataMatrix.net.DmtxImageEncoder();
string QRCodepath = "";
if (LorR == 0)
{
Bitmap dataMatixCode = dataMatix.EncodeImage(0 + dt.Rows[0]["ProductNo"].ToString(), 15);
QRCodepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Image\\" + dt.Rows[0]["ProductNo"].ToString();
dataMatixCode.Save(QRCodepath);
}
else if (LorR == 1)
{
Bitmap dataMatixCode = dataMatix.EncodeImage(1 + dt.Rows[0]["ProductNo"].ToString(), 15);
QRCodepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Image\\" + dt.Rows[0]["ProductNo"].ToString();
dataMatixCode.Save(QRCodepath);
}
Microsoft.Office.Interop.Excel.Range m_objRange = worksheet.get_Range("B6", Type.Missing);
m_objRange.Select();
Excel.Pictures pics = (Excel.Pictures)worksheet.Pictures();
pics.Insert(QRCodepath, m_objRange);
Dictionary <int, string> DictPrinterName = new Dictionary <int, string>();
worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //纸张大小
//worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; 页面横向
worksheet.PageSetup.CenterHorizontally = true; //文字水平居中
xlApp.Visible = true;
System.Windows.Forms.Application.DoEvents();
//开始打印
worksheet.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//打印预览
//worksheet.PrintPreview();
//打印结束后清除Excel内存
workbooks.Close();
xlApp.Application.Quit();
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();//强行销毁
}
}
private void button1_Click(object sender, EventArgs e)
{
this.Hide();
var text1 = this.textBox1.Text;
var text2 = this.textBox2.Text;
var text3 = this.textBox3.Text;
var text4 = this.textBox4.Text;
Worksheet ws = Globals.ThisAddIn.GetWorksheet();
ws.Cells[2, 1] = Globals.ThisAddIn.checkCells(text1);
ws.Cells[5, 1] = Globals.ThisAddIn.checkCells(text2);;
ws.Cells[8, 1] = Globals.ThisAddIn.checkCells(text3);;
ws.Cells[11, 1] = Globals.ThisAddIn.checkCells(text4);;
if (!Globals.ThisAddIn.IsDirectoryEmpty("C:\\Users\\admin\\Source\\Repos\\BusinessPlanWriter\\BPWChartImages\\"))
{
//Avoids duplicate pictures of charts
foreach (Picture picture in ws.Pictures())
{
picture.Delete();
}
//Insert Chart 1
try
{
Range oRange = ws.Cells[3, 1];
oRange.ColumnWidth = 76;
oRange.RowHeight = 200;
float Left = (float)((double)oRange.Left);
float Top = (float)((double)oRange.Top);
const float ImageSize = 200;
Shape picture = ws.Shapes.AddPicture("C:\\Users\\admin\\Source\\Repos\\BusinessPlanWriter\\BPWChartImages\\1.1.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize * 2, ImageSize);
picture.Placement = XlPlacement.xlMoveAndSize;
}
catch (Exception exception)
{
//System.Windows.Forms.MessageBox.Show("No Chart for Section 1.1");
ws.Range["A3"].EntireRow.AutoFit();
}
//Insert Chart 2
try
{
Range oRange = ws.Cells[6, 1];
oRange.ColumnWidth = 76;
oRange.RowHeight = 200;
float Left = (float)((double)oRange.Left);
float Top = (float)((double)oRange.Top);
const float ImageSize = 200;
Shape picture = ws.Shapes.AddPicture("C:\\Users\\admin\\Source\\Repos\\BusinessPlanWriter\\BPWChartImages\\1.2.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize * 2, ImageSize);
picture.Placement = XlPlacement.xlMoveAndSize;
}
catch (Exception exception)
{
//System.Windows.Forms.MessageBox.Show("No Chart for Section 1.2");
ws.Range["A6"].EntireRow.AutoFit();
}
//Insert Chart 3
try
{
Range oRange = ws.Cells[9, 1];
oRange.ColumnWidth = 76;
oRange.RowHeight = 200;
float Left = (float)((double)oRange.Left);
float Top = (float)((double)oRange.Top);
const float ImageSize = 200;
Shape picture = ws.Shapes.AddPicture("C:\\Users\\admin\\Source\\Repos\\BusinessPlanWriter\\BPWChartImages\\1.3.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize * 2, ImageSize);
picture.Placement = XlPlacement.xlMoveAndSize;
}
catch (Exception exception)
{
//System.Windows.Forms.MessageBox.Show("No Chart for Section 1.3");
ws.Range["A9"].EntireRow.AutoFit();
}
//Insert Chart 4
try
{
Range oRange = ws.Cells[12, 1];
oRange.ColumnWidth = 76;
oRange.RowHeight = 200;
float Left = (float)((double)oRange.Left);
float Top = (float)((double)oRange.Top);
const float ImageSize = 200;
Shape picture = ws.Shapes.AddPicture("C:\\Users\\admin\\Source\\Repos\\BusinessPlanWriter\\BPWChartImages\\1.4.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize * 2, ImageSize);
picture.Placement = XlPlacement.xlMoveAndSize;
}
catch (Exception exception)
{
//System.Windows.Forms.MessageBox.Show("No Chart for Section 1.4");
ws.Range["A12"].EntireRow.AutoFit();
}
}
ws.Range["A2"].EntireRow.AutoFit();
ws.Range["A5"].EntireRow.AutoFit();
ws.Range["A8"].EntireRow.AutoFit();
ws.Range["A11"].EntireRow.AutoFit();
}
