/// <summary>
/// 默认生成模板样式
/// </summary>
public void FormatTamplate()
{
ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Microsoft.Office.Interop.Excel.Range rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B1", "G1");
rang.Select();
rang.Merge(false);
rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B2", "G2");
rang.Select();
rang.Merge(false);
rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B3", "G3");
rang.Select();
rang.Merge(false);
ws.Cells.set_Item(1, 1, "输出表");
ws.Cells.set_Item(2, 1, "表名称");
ws.Cells.set_Item(3, 1, "备注");
ws.Cells.set_Item(3, 2, this._tableName);
ws.Cells.set_Item(4, 1, "字段编号");
ws.Cells.set_Item(4, 2, "字段");
ws.Cells.set_Item(4, 3, "数据元素");
ws.Cells.set_Item(4, 4, "长度");
ws.Cells.set_Item(4, 5, "小数位");
ws.Cells.set_Item(4, 6, "类型");
ws.Cells.set_Item(4, 7, "字段含义");
ws.Columns.AutoFit();
}
/// <summary>
/// 合并单元格，填写值并设置边框
/// </summary>
/// <param name="worksheet"></param>
/// <param name="value">单元格的值</param>
/// <param name="sRow">开始行</param>
/// <param name="sCol">开始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endCol">结束列</param>
public static void MergeCells2(this Microsoft.Office.Interop.Excel.Worksheet worksheet, object value, int sRow, int sCol, int endRow, int endCol)
{
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[sRow, sCol], worksheet.Cells[endRow, endCol]);
range.Merge();
range.Value = value;
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
private void mergeAndCenter(Microsoft.Office.Interop.Excel.Range theRange)
{
//this function merges and centers the selected range of cells
theRange.Merge();
theRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
theRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
return;
}
//打印填数据
private void fill_excel(Microsoft.Office.Interop.Excel._Worksheet mysheet, Microsoft.Office.Interop.Excel._Workbook mybook)
{
int ind = 0;
if (dataGridView2.Rows.Count > 12)
{
//在第6行插入
for (int i = 0; i < dataGridView2.Rows.Count - 12; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)mysheet.Rows[6, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
Microsoft.Office.Interop.Excel.Range range1 = mysheet.get_Range("F6");
range1.Merge(mysheet.get_Range("G6"));
}
ind = dataGridView1.Rows.Count - 12;
}
//外表信息
mysheet.Cells[3, 8].Value = dtOuter.Rows[0]["生产指令编号"].ToString();
mysheet.Cells[4, 8].Value = dtp开始生产时间.Value.ToShortDateString() + "--" + dtp结束生产时间.Value.ToShortDateString();
//内表2信息,生产记录
for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
mysheet.Cells[6 + i, 6] = dataGridView2.Rows[i].Cells[0].Value.ToString();
mysheet.Cells[6 + i, 8] = dataGridView2.Rows[i].Cells[1].Value.ToString();
mysheet.Cells[6 + i, 9] = dataGridView2.Rows[i].Cells[2].Value.ToString();
}
//内表1，目录
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
string s;
if (dataGridView1.Rows[i].Cells[4].Value != null)
{
s = dataGridView1.Rows[i].Cells[4].Value.ToString();
}
else
{
s = "0";
}
mysheet.Cells[5 + i, 1] = dataGridView1.Rows[i].Cells[2].Value.ToString();
mysheet.Cells[5 + i, 2] = dataGridView1.Rows[i].Cells[3].Value.ToString();
mysheet.Cells[5 + i, 3] = s;
}
//外表，汇总，审核，批准
mysheet.Cells[18 + ind, 7] = dtOuter.Rows[0]["汇总人"].ToString();
mysheet.Cells[18 + ind, 9] = DateTime.Parse(dtOuter.Rows[0]["汇总时间"].ToString()).ToString("D");
mysheet.Cells[20 + ind, 7] = dtOuter.Rows[0]["审核人"].ToString();
mysheet.Cells[20 + ind, 9] = DateTime.Parse(dtOuter.Rows[0]["审核时间"].ToString()).ToString("D");
mysheet.Cells[22 + ind, 7] = dtOuter.Rows[0]["批准人"].ToString();
mysheet.Cells[22 + ind, 9] = DateTime.Parse(dtOuter.Rows[0]["批准时间"].ToString()).ToString("D");
}
public bool Export(DataGridView dgv)
{
//定义Excel操作对象
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
//定义Excel工作表
Microsoft.Office.Interop.Excel.Worksheet workSheet = excelApp.Workbooks.Add().Worksheets[1];
//设置标题样式（从第2行，第2列开始）
workSheet.Cells[2, 2] = "学生成绩表";//设置标题内容
workSheet.Cells[2, 2].RowHeight = 25;
Microsoft.Office.Interop.Excel.Range range = workSheet.get_Range("B2", "H2");
range.Merge(0); //合并表头单元格
range.Borders.Value = 1; //设置表头的边框
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置单元格内容剧中显示
range.Font.Size = 15;
//获取总列数和总行数
int colcomCount = dgv.ColumnCount;
int rowCount = dgv.RowCount;
//显示列标题
for (int i = 0; i < colcomCount; i++)
{
//从第3行开始
workSheet.Cells[3, i + 2] = dgv.Columns[i].HeaderText;
workSheet.Cells[3, i + 2].Borders.Value = 1;
workSheet.Cells[3, i + 2].RowHeight = 23;
}
//显示数据，从第4列，低2列，开始
for (int i = 0; i < rowCount - 1; i++)
{
for (int n = 0; n < colcomCount; n++)
{
//从第3行开始
workSheet.Cells[i + 4, n + 2] = dgv.Rows[i + 1].Cells[n].Value;
workSheet.Cells[i + 4, n + 2].Borders.Value = 1;
workSheet.Cells[i + 4, n + 2].RowHeight = 23;
}
}
//设置列宽和数据一致
workSheet.Columns.AutoFit();
//打印预览
excelApp.Visible = true;
excelApp.Sheets.PrintPreview();
//释放对象
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
return(true);
}
/// <summary>
/// 写文本
/// </summary>
/// <param name="wsheet"></param>
/// <param name="title"></param>
/// <param name="colcount"></param>
private void DrawText(Microsoft.Office.Interop.Excel.Worksheet wsheet, string txt, System.Drawing.Font font, TextAlign align, int colcount)
{
_rowindex++;
//取得整个报表的标题
wsheet.Cells[_rowindex, 1] = txt;
//设置整个报表的标题格式
Microsoft.Office.Interop.Excel.Range range = wsheet.Range[wsheet.Cells[_rowindex, 1], wsheet.Cells[_rowindex, colcount]];
SetFont(range, font);
//range.Borders.LineStyle = 1; //边线
range.HorizontalAlignment = (int)align; // Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
//合并单元格
range.Merge(range.MergeCells);
}
internal void createHeaders(int row, int col, string htext, string cell1,
string cell2, int mergeColumns, string b, bool font, int size, string
fcolor)
{
worksheet.Cells[row, col] = htext;
workSheet_range = worksheet.get_Range(cell1, cell2);
workSheet_range.Merge(mergeColumns);
switch (b)
{
case "YELLOW":
workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
break;
case "GRAY":
workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
break;
case "GAINSBORO":
workSheet_range.Interior.Color =
System.Drawing.Color.Gainsboro.ToArgb();
break;
case "Turquoise":
workSheet_range.Interior.Color =
System.Drawing.Color.Turquoise.ToArgb();
break;
case "PeachPuff":
workSheet_range.Interior.Color =
System.Drawing.Color.PeachPuff.ToArgb();
break;
default:
// workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
break;
}
// workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
// workSheet_range.Font.Bold = font;
workSheet_range.ColumnWidth = size;
if (fcolor.Equals(""))
{
workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
}
else
{
workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
}
}
private void InsertReportHeaderRow(ExcelWorksheet worksheet, string startCell, string endCell,
string value, bool isBold, bool isCenter)
{
ExcelRange range = worksheet.get_Range(startCell + ":" + endCell);
range.Merge();
range.Value = value;
range.Font.Bold = isBold;
if (isCenter)
{
ExcelTemplate.SetTopAlignCenter(worksheet, startCell, endCell);
}
else
{
ExcelTemplate.SetTopAlignLeft(worksheet, startCell, endCell);
}
}
public void createHeaders(int inRow, int inColumn, string inHeaderText, string inCell1, string inCell2, int inMergeColumns, string inBackGroundColor, bool inBold, int inSize, string inFontColor, int inFontSize)
{
workSheet1.Cells[inRow, inColumn] = inHeaderText;
workSheetRange = workSheet1.Range[inCell1, inCell2];
workSheetRange.Merge(inMergeColumns);
switch (inBackGroundColor)
{
case "BEIGE":
workSheetRange.Interior.Color = System.Drawing.Color.Beige.ToArgb();
break;
case "GRAY":
workSheetRange.Interior.Color = Color.FromArgb(221, 221, 221);
break;
case "LIGHTGRAY":
workSheetRange.Interior.Color = Color.FromArgb(238, 238, 238);
break;
case "BLACK":
workSheetRange.Interior.Color = Color.FromArgb(0, 0, 0);
break;
default:
break;
}
workSheetRange.Font.Bold = inBold;
workSheetRange.EntireColumn.AutoFit();
workSheetRange.Font.Size = inFontSize;
workSheetRange.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheetRange.Font.Name = "Arial";
if (inFontColor.Equals(""))
{
workSheetRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
}
else
{
workSheetRange.Font.Color = System.Drawing.Color.Black.ToArgb();
}
workSheetRange.EntireColumn.AutoFit();
}
public void createHeaders(int row, int col, string htext, string cell1,
string cell2, int mergeColumns,string b, bool font,int size,string
fcolor)
{
worksheet.Cells[row, col] = htext;
workSheet_range = worksheet.get_Range(cell1, cell2);
workSheet_range.Merge(mergeColumns);
switch(b)
{
case "YELLOW":
workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
break;
case "GRAY":
workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
break;
case "GAINSBORO":
workSheet_range.Interior.Color =
System.Drawing.Color.Gainsboro.ToArgb();
break;
case "Turquoise":
workSheet_range.Interior.Color =
System.Drawing.Color.Turquoise.ToArgb();
break;
case "PeachPuff":
workSheet_range.Interior.Color =
System.Drawing.Color.PeachPuff.ToArgb();
break;
default:
// workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
break;
}
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.Font.Bold = font;
workSheet_range.ColumnWidth = size;
if (fcolor.Equals(""))
{
workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
}
else {
workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
}
}
public void createHeadersMergeCenterBorder(int inRow, int inColumn, string inHeaderText, string inCell1, string inCell2, int inMergeColumns, string inBackGroundColor, bool inBold, int inSize, string inFontColor, int inFontSize)
{
workSheet1.Cells[inRow, inColumn] = inHeaderText;
workSheetRange = workSheet1.Range[inCell1, inCell2];
workSheetRange.Merge(inMergeColumns);
workSheetRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
switch (inBackGroundColor)
{
case "BEIGE":
workSheetRange.Interior.Color = System.Drawing.Color.Beige.ToArgb();
break;
case "GRAY":
workSheetRange.Interior.Color = Color.FromArgb(221, 221, 221);
break;
case "LIGHTGRAY":
workSheetRange.Interior.Color = Color.FromArgb(238, 238, 238);
break;
default:
break;
}
workSheetRange.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheetRange.Font.Bold = inBold;
workSheetRange.EntireColumn.AutoFit();
workSheetRange.Font.Size = inFontSize;
workSheetRange.Font.Name = "Arial";
if (inFontColor.Equals(""))
{
workSheetRange.Font.Color = System.Drawing.Color.White.ToArgb();
}
else
{
workSheetRange.Font.Color = System.Drawing.Color.Black.ToArgb();
}
workSheetRange.EntireColumn.AutoFit();
}
/// <summary>
/// 处理固定的4个参数表
/// </summary>
/// <param name="dt"></param>
/// <param name="name"></param>
/// <param name="rowsOffset"></param>
/// <param name="colsOffset"></param>
private void ParseParameterList(DataTable dt, String name, int rowsOffset, int colsOffset)
{
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 8]);
range.Select();
range.Merge(false);
ws.Cells.set_Item(rowsOffset - 2, colsOffset - 1, "PARAMETERLIST");
ws.Cells.set_Item(rowsOffset - 2, colsOffset, name);
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "类型名称");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "长度");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "小数位");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 6, "默认值");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 7, "必输");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 8, "短文本");
if (dt.Rows.Count > 0)
{
ParseParameterlistToExcel(dt, rowsOffset, colsOffset);
}
}
public static void CreateHeaders(int row, int col, string htext, string cell1,
string cell2, int mergeColumns, string b, bool font, int size, string
fcolor)
{
Worksheet.Cells[row, col] = htext;
WorkSheetRange = Worksheet.Range[cell1, cell2];
WorkSheetRange.Merge(mergeColumns);
switch (b)
{
case "YELLOW":
WorkSheetRange.Interior.Color = Color.Yellow.ToArgb();
break;
case "GRAY":
WorkSheetRange.Interior.Color = Color.Gray.ToArgb();
break;
case "GAINSBORO":
WorkSheetRange.Interior.Color =
Color.Gainsboro.ToArgb();
break;
case "Turquoise":
WorkSheetRange.Interior.Color =
Color.Turquoise.ToArgb();
break;
case "PeachPuff":
WorkSheetRange.Interior.Color =
Color.PeachPuff.ToArgb();
break;
}
WorkSheetRange.Borders.Color = Color.Black.ToArgb();
WorkSheetRange.Font.Bold = font;
WorkSheetRange.ColumnWidth = size;
WorkSheetRange.Font.Color = fcolor.Equals("") ? Color.White.ToArgb() : Color.Black.ToArgb();
}
/// <summary>
/// 写标题
/// </summary>
/// <param name="wsheet"></param>
/// <param name="title"></param>
/// <param name="colcount"></param>
private void DrawTitle(Microsoft.Office.Interop.Excel.Worksheet wsheet, int colcount)
{
if (!IsDrawTitle)
{
return;
}
_rowindex++; //加行数
Microsoft.Office.Interop.Excel.Range cellrange = (Microsoft.Office.Interop.Excel.Range)wsheet.Cells[_rowindex, 1];
//cellrange.RowHeight = 31; //行高31
//插入图片
//InsertImage(GetImagePath(), cellrange, wsheet);
//取得整个报表的标题
string titletxt = _title;
if (_isTitleAppendSheetName)
{
titletxt += "(" + wsheet.Name + ")";
}
wsheet.Cells[_rowindex, 1] = titletxt;
//设置整个报表的标题格式
Microsoft.Office.Interop.Excel.Range range = wsheet.Range[wsheet.Cells[_rowindex, 1], wsheet.Cells[_rowindex, colcount]];
//设置字体
SetFont(range, _titlefont);
//range.Borders.LineStyle = 1; //边线框
//下边框线
Microsoft.Office.Interop.Excel.Border border = range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom);
//border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlSlantDashDot;// 选择先的类型
border.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
//字体剧中
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//合并单元格
range.Merge(range.MergeCells);
range.WrapText = true;
}
/// <summary>
/// 打印到excel
/// </summary>
/// <param name="table"></param>
/// <param name="SavePath"></param>
/// <param name="yundan"></param>
public void Printer(System.Data.DataTable table, string SavePath, string yundan)
{
//---------------------------------****20170911NPOI重写excel文件生成*****---------------------------------------------
//HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//if (hssfworkbook == null)
//{
// throw new Exception("无法创建NPOI");
//}
//// 新建一个Excel页签
//ISheet sheet = hssfworkbook.CreateSheet("报关单");
//IRow row = sheet.CreateRow(0); //创建sheet页的第0行（索引从0开始）
//row.CreateCell(0, CellType.String).SetCellValue("发票声明书");//创建第0行第0列
///-----------------------------------------------------------------------------------------------------------
//创建一个Excel应用程序对象，如果未创建成功则推出。
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
if (excel1 == null)
{
throw new Exception("无法创建Excel对象，可能你的电脑未装Excel");
}
Microsoft.Office.Interop.Excel.Workbooks workBooks1 = excel1.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workBook1 = workBooks1.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook1.Worksheets[1]; //取得sheet1
//worksheet.PageSetup.Zoom = 63; //打印时页面设置,缩放比例
//把DataTable的表头导入到Excel的第一行
Microsoft.Office.Interop.Excel.Range xlsRow100 = worksheet.Range[worksheet.Cells[4, 1], worksheet.Cells[3000, 20]];
Microsoft.Office.Interop.Excel.Range xlsRow99 = worksheet.Range[worksheet.Cells[4, 8], worksheet.Cells[3000, 8]];
xlsRow99.ColumnWidth = 12; //设置列宽度
xlsRow100.Font.Name = "Arial"; //设置字体
xlsRow100.Font.Bold = true; //加粗显示
xlsRow100.Font.Size = 10; //字体大小
Microsoft.Office.Interop.Excel.Range xlsRow = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 11]];
xlsRow.Merge(true); //合并单元格
xlsRow.Value = "发票声明书";
xlsRow.Font.Name = "Arial"; //设置字体
xlsRow.Font.Size = 18; //字体大小
xlsRow.Font.Bold = true; //加粗显示
xlsRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中
xlsRow.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中
//xlsRow.Interior.Color = "255,255,0"; //设置背景颜色
//xlsRow.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
//xlsRow.Borders.Weight = Excel.XlBorderWeight.xlMedium;//边框常规粗细
Microsoft.Office.Interop.Excel.Range xlsRow1 = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[2, 11]];
xlsRow1.Merge(true); //合并单元格
xlsRow1.Value = "INVOICE STATEMENT ";
xlsRow1.Font.Name = "Arial"; //设置字体
xlsRow1.Font.Size = 18; //字体大小
xlsRow1.Font.Bold = true; //加粗显示
xlsRow1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中
xlsRow1.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中
Microsoft.Office.Interop.Excel.Range xlsRow2 = worksheet.Range[worksheet.Cells[4, 2], worksheet.Cells[11, 2]];
xlsRow2.Interior.Color = Color.FromArgb(255, 255, 153);
worksheet.Cells[4, 2] = "收件人"; //Excel单元格赋值
worksheet.Cells[5, 2] = "Consignee:"; //Excel单元格赋值
worksheet.Cells[6, 2] = "地址:"; //Excel单元格赋值
worksheet.Cells[10, 2] = "邮编:"; //Excel单元格赋值
worksheet.Cells[7, 2] = "Address:"; //Excel单元格赋值
worksheet.Cells[11, 2] = "Zip Code"; //Excel单元格赋值
worksheet.Cells[4, 7] = "公司名称"; //Excel单元格赋值 YANG CHEN WEI
worksheet.Cells[5, 7] = "Company Name:";
worksheet.Cells[5, 8] = "NICCHU SHOMU K.K";
worksheet.Cells[7, 8] = "O48";
worksheet.Cells[9, 8] = "080-4730-8688";
worksheet.Cells[11, 8] = "JAPAN";
worksheet.Cells[6, 7] = "城市/地区号:";
worksheet.Cells[7, 7] = "Town/Area Code";
worksheet.Cells[8, 7] = "电话/传真:";
worksheet.Cells[9, 7] = "Phone/Fax:";
worksheet.Cells[10, 7] = "州名/国家:";
worksheet.Cells[11, 7] = "State/Country:";
worksheet.Cells[5, 3] = "YANG CHEN WEI";
worksheet.Cells[7, 3] = "1-3-4-2F ";
worksheet.Cells[8, 3] = "TODA SHI.SAITAMA ";
worksheet.Cells[9, 3] = "KEN.JAPAN";
worksheet.Cells[11, 3] = "335-0016";
xlsRow2.ColumnWidth = 30;
xlsRow2.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框
xlsRow2.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细
xlsRow2.Font.Name = "Arial"; //设置字体
xlsRow2.Font.Bold = true; //加粗显示
Microsoft.Office.Interop.Excel.Range xlsRow3 = worksheet.Range[worksheet.Cells[4, 1], worksheet.Cells[11, 1]];
xlsRow3.ColumnWidth = 2; //设置列宽度
Microsoft.Office.Interop.Excel.Range xlsRow4 = worksheet.Range[worksheet.Cells[4, 2], worksheet.Cells[4, 11]];
xlsRow4.RowHeight = 25; //设置行高度
Microsoft.Office.Interop.Excel.Range xlsRow5 = worksheet.Range[worksheet.Cells[4, 1], worksheet.Cells[11, 11]];
xlsRow5.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框
xlsRow5.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细
Microsoft.Office.Interop.Excel.Range xlsRow6 = worksheet.Range[worksheet.Cells[4, 7], worksheet.Cells[11, 7]];
xlsRow6.Interior.Color = Color.FromArgb(255, 255, 153);
xlsRow6.ColumnWidth = 20;
xlsRow6.Font.Name = "Arial"; //设置字体
xlsRow6.Font.Bold = true; //加粗显示
Microsoft.Office.Interop.Excel.Range xlsRow7 = worksheet.Range[worksheet.Cells[13, 2], worksheet.Cells[14, 2]];
xlsRow7.Font.Name = "Arial"; //设置字体
xlsRow7.Font.Bold = true; //加粗显示
xlsRow7.Interior.Color = Color.FromArgb(255, 255, 153);
worksheet.Cells[13, 2] = "运单号：";
worksheet.Cells[14, 2] = "Airway Bill No：";
worksheet.Cells[14, 3] = yundan;
xlsRow7.ColumnWidth = 30;
xlsRow7.RowHeight = 25;
Microsoft.Office.Interop.Excel.Range xlsRow8 = worksheet.Range[worksheet.Cells[4, 3], worksheet.Cells[14, 3]];
xlsRow8.ColumnWidth = 18;
Microsoft.Office.Interop.Excel.Range xlsRow9 = worksheet.Range[worksheet.Cells[4, 4], worksheet.Cells[11, 4]];
xlsRow9.ColumnWidth = 2;
Microsoft.Office.Interop.Excel.Range xlsRow10 = worksheet.Range[worksheet.Cells[4, 6], worksheet.Cells[11, 6]];
xlsRow10.ColumnWidth = 2;
worksheet.Cells[16, 2] = "详细的商品名称";
worksheet.Cells[16, 3] = "海关商品编码";
worksheet.Cells[16, 4] = "生产厂商";
worksheet.Cells[16, 6] = "重量";
worksheet.Cells[16, 8] = "体积";
worksheet.Cells[16, 9] = "数量";
worksheet.Cells[16, 10] = "单价";
worksheet.Cells[16, 11] = "报关总价";
Microsoft.Office.Interop.Excel.Range xlsRow11 = worksheet.Range[worksheet.Cells[16, 2], worksheet.Cells[16, 11]];
xlsRow11.Interior.Color = Color.FromArgb(255, 255, 153);
xlsRow11.Font.Name = "Arial"; //设置字体
Microsoft.Office.Interop.Excel.Range xlsRow12 = worksheet.Range[worksheet.Cells[16, 4], worksheet.Cells[16, 5]];
xlsRow12.Merge(true); //合并单元格
Microsoft.Office.Interop.Excel.Range xlsRow13 = worksheet.Range[worksheet.Cells[16, 6], worksheet.Cells[16, 7]];
xlsRow13.Merge(true); //合并单元格
xlsRow11.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框
xlsRow11.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细Full Description of Weight
xlsRow11.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中
xlsRow11.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中
worksheet.Cells[17, 2] = "Full Description of";
worksheet.Cells[17, 3] = "Harmonised";
worksheet.Cells[17, 7] = "Weight";
worksheet.Cells[17, 8] = "Dimensions";
worksheet.Cells[17, 9] = "No of ";
worksheet.Cells[17, 10] = "Unit value ";
worksheet.Cells[17, 11] = "Total Value ";
worksheet.Cells[18, 2] = "Goods";
worksheet.Cells[18, 3] = "Code (if have)"; //Items Manufacturer
worksheet.Cells[18, 9] = "Items";
worksheet.Cells[18, 10] = "(USD$)";
worksheet.Cells[18, 11] = "(USD$)";
worksheet.Cells[17, 4] = "Manufacturer";
Microsoft.Office.Interop.Excel.Range xlsRow14 = worksheet.Range[worksheet.Cells[17, 4], worksheet.Cells[18, 5]];
xlsRow14.Merge(true); //合并单元格
Microsoft.Office.Interop.Excel.Range xlsRow15 = worksheet.Range[worksheet.Cells[17, 4], worksheet.Cells[18, 4]];
xlsRow15.Merge(true); //合并单元格
Microsoft.Office.Interop.Excel.Range xlsRow16 = worksheet.Range[worksheet.Cells[16, 1], worksheet.Cells[18, 11]];
xlsRow16.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框
xlsRow16.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细
//---------------------开始插入数据Datatable---------------------------------------------------------------------------
double allprice = 0;
for (int i = 0; i < table.Rows.Count; i++)
{
worksheet.Cells[(19 + i), 2] = table.Rows[i]["bgname"].ToString(); //table.Rows[i]["BName"].ToString();
worksheet.Cells[(19 + i), 3] = table.Rows[i]["bgcode"].ToString();
worksheet.Cells[(19 + i), 9] = table.Rows[i]["count"].ToString();
worksheet.Cells[(19 + i), 10] = table.Rows[i]["money"].ToString();
worksheet.Cells[(19 + i), 11] = "0"; //table.Rows[i]["Totil"].ToString();
allprice = allprice + Convert.ToDouble("100"); //table.Rows[i]["Totil"].ToString()
}
Microsoft.Office.Interop.Excel.Range xlsRow17 = worksheet.Range[worksheet.Cells[19, 1], worksheet.Cells[18 + table.Rows.Count, 11]];
xlsRow17.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框
xlsRow17.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细
xlsRow17.RowHeight = 25; //设置行高度
xlsRow17.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中
xlsRow17.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中
//------------------------------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Range xlsRow18 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count, 1], worksheet.Cells[19 + table.Rows.Count, 11]];
xlsRow18.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框
xlsRow18.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细
xlsRow18.Interior.Color = Color.FromArgb(255, 255, 153);
Microsoft.Office.Interop.Excel.Range xlsRow19 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count, 1], worksheet.Cells[19 + table.Rows.Count, 10]];
xlsRow19.Merge(true);
xlsRow19.Value = "Declared Value Terms of Trade";
xlsRow19.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中
xlsRow19.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中
worksheet.Cells[19 + table.Rows.Count, 11] = allprice;
//-------------------------------------------------------------------------------------------------------------------------
worksheet.Cells[19 + table.Rows.Count + 2, 2] = "本人认为以上提供的资料属实和正确，货物原产地是：";
worksheet.Cells[19 + table.Rows.Count + 2, 6] = "China";
worksheet.Cells[19 + table.Rows.Count + 3, 2] = "I declare that the above information is true and correct to the best of my knowledge and";
worksheet.Cells[19 + table.Rows.Count + 4, 2] = "that the goods are of ";
worksheet.Cells[19 + table.Rows.Count + 4, 6] = "origin ";
worksheet.Cells[19 + table.Rows.Count + 4, 5] = "China ";
//----------------------------------最下面----------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Range xlsRow20 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count + 7, 1], worksheet.Cells[19 + table.Rows.Count + 12, 11]];
xlsRow20.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //设置边框
xlsRow20.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; //边框常规粗细 Town/Area Code:
worksheet.Cells[19 + table.Rows.Count + 7, 2] = "发件人";
worksheet.Cells[19 + table.Rows.Count + 8, 2] = "Consignor:";
worksheet.Cells[19 + table.Rows.Count + 9, 2] = "地址：";
worksheet.Cells[19 + table.Rows.Count + 10, 2] = "Address:";
worksheet.Cells[19 + table.Rows.Count + 11, 2] = "州名/国家";
worksheet.Cells[19 + table.Rows.Count + 12, 2] = "State/County";
worksheet.Cells[19 + table.Rows.Count + 7, 7] = "公司名称：";
worksheet.Cells[19 + table.Rows.Count + 8, 7] = "Company Name:";
worksheet.Cells[19 + table.Rows.Count + 9, 7] = "城市/地区号：";
worksheet.Cells[19 + table.Rows.Count + 10, 7] = "Town/Area Code:";
worksheet.Cells[19 + table.Rows.Count + 11, 7] = "电话/传真/电子邮件：";
worksheet.Cells[19 + table.Rows.Count + 12, 7] = "Phone/Fax/E－mail:";
Microsoft.Office.Interop.Excel.Range xlsRow21 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count + 7, 2], worksheet.Cells[19 + table.Rows.Count + 12, 2]];
xlsRow21.Interior.Color = Color.FromArgb(255, 255, 153);
Microsoft.Office.Interop.Excel.Range xlsRow22 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count + 7, 7], worksheet.Cells[19 + table.Rows.Count + 12, 7]];
xlsRow22.Interior.Color = Color.FromArgb(255, 255, 153);
Microsoft.Office.Interop.Excel.Range xlsRow23 = worksheet.Range[worksheet.Cells[19 + table.Rows.Count + 7, 8], worksheet.Cells[19 + table.Rows.Count + 8, 11]];
xlsRow23.Merge(true);
worksheet.Cells[19 + table.Rows.Count + 8, 3] = "XUXU";
worksheet.Cells[19 + table.Rows.Count + 9, 3] = "1049 Jinfan Street Wucheng District，";
worksheet.Cells[19 + table.Rows.Count + 10, 3] = "Jinhua City , Zhejiang Province, China";
worksheet.Cells[19 + table.Rows.Count + 12, 3] = "China";
worksheet.Cells[19 + table.Rows.Count + 8, 8] = "LeCheng Network Technology CO., Ltd";
worksheet.Cells[19 + table.Rows.Count + 10, 8] = "O579";
worksheet.Cells[19 + table.Rows.Count + 12, 8] = "18606881258";
try
{
//保存Excel
workBook1.Saved = true;
workBook1.SaveCopyAs(SavePath);
}
catch (Exception ex)
{
throw new Exception("导出文件时出错，文件可能正被打开!\n" + ex.Message);
//MessageBox.Show("导出文件时出错，文件可能正被打开!\n" + ex.ToString());
}
workBook1.Close();
excel1.Visible = true;
if (excel1 != null)
{
excel1.Workbooks.Close();
excel1.Quit();
int generation = System.GC.GetGeneration(excel1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
excel1 = null;
System.GC.Collect(generation);
}
}
/// <summary>
/// Вывод таблицы в эксель
/// </summary>
public void OutToExcell(string title, DataView table)
{
var excelapp = new Microsoft.Office.Interop.Excel.Application();
var workbook = excelapp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;
//Получение названий колонок
var ColumnName = new List <string>();
for (int i = 0; i < table.Table.Columns.Count; i++)
{
ColumnName.Add(table.Table.Columns[i].ToString());
}
//Выводим название колонок
for (int x = 0; x < ColumnName.Count; x++)
{
worksheet.Rows[2].Columns[x + 1] = ColumnName[x];
}
//заполням ячейки
for (int y = 3; y < table.Count + 3; y++)
{
for (int x = 0; x < ColumnName.Count; x++)
{
worksheet.Rows[y].Columns[x + 1] = table.Table.Rows[y - 3][ColumnName[x]];
}
}
// (Титульник над содержимым) Выделяем диапазон ячеек от A1 до числа столбцов из DataView
Microsoft.Office.Interop.Excel.Range TitleRange = (Microsoft.Office.Interop.Excel.Range)worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, ColumnName.Count]).Cells;
// Производим объединение
TitleRange.Merge(Type.Missing);
//Размер текста
TitleRange.Cells.Font.Size = 16;
//Выравнивание по центру
TitleRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//Задание bold для текста
TitleRange.Font.Bold = true;
//Задаем название титульника
worksheet.Cells[1, 1] = title;
//Выделение всех ячеек с данными
Microsoft.Office.Interop.Excel.Range ContentRange = (Microsoft.Office.Interop.Excel.Range)worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[table.Count + 2, ColumnName.Count]).Cells;
//Выставление линий
ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
ContentRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//Выставление автоширины
ContentRange.EntireColumn.AutoFit();
//Отображаем Excel
excelapp.AlertBeforeOverwriting = false;
excelapp.Visible = true;
}
private void btnReporte_Click(object sender, EventArgs e)
{
try
{
BD metodos = new BD();
BD.ObtenerConexion();
tableProductosFiltro = metodos.consultarProductoDetalleReporte();
BD.CerrarConexion();
/*
* Productos.id_producto AS ID, Productos.modelo AS MODELO, Tamanos.tamano AS TAMAÑO,
* Material.nombre AS MATERIAL, Categoria.nombre AS CATEGORIA, Productos.cantidad AS CANTIDAD,
* Tipo.nombre AS TIPO, Productos.precio_publico AS PRECIO_PUBLICO, Productos.precio_frecuente AS PRECIO_FRECUENTE,
* Productos.precio_mayorista AS PRECIO_MAYORISTA, Tamanos.descripcion AS DESCRIPCION, Productos.peso AS PESO
*/
for (int i = 0; i < tableProductosFiltro.Rows.Count; i++)
{
for (int j = 0; j < tableProductosFiltro.Columns.Count; j++)
{
Console.Write(tableProductosFiltro.Rows[i][j]);
}
Console.WriteLine("");
}
}
catch
{
txtGenerando.Text = "Error de conexión ...";
}
if (tableProductosFiltro.Rows.Count != 0)
{
try
{
txtGenerando.Text = "GENERANDO DOCUMENTO ...";
Cursor.Current = Cursors.WaitCursor;
SaveFileDialog fichero = new SaveFileDialog();
fichero.FileName = "reporte_inventario_" + Inicio.fecha;
fichero.Filter = "Excel (*.xls)|*.xls";
if (fichero.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application aplicacion;
Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
Microsoft.Office.Interop.Excel.Range inicio;
Microsoft.Office.Interop.Excel.Range ultimo;
aplicacion = new Microsoft.Office.Interop.Excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
hoja_trabajo = (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
hoja_trabajo.Range["B5:M5"].Font.Bold = true;
//hoja_trabajo.Cells[2,8] = "Reporteador";
hoja_trabajo.Cells[6, 3] = "Fecha del reporte: " + Inicio.fecha;
aplicacion.get_Range("H2:M4").Merge(true);
Microsoft.Office.Interop.Excel.Range titulo = hoja_trabajo.get_Range("H2:M4");
titulo.Merge();
titulo.Value = "INVENTARIO";
//titulo.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//titulo.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
titulo.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
titulo.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
titulo.Font.Size = 36;
titulo.BorderAround2(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium);
titulo.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbGreen;
titulo.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhite;
///////////////////////////////
//
aplicacion.get_Range("H7:M10").Merge(true);
Microsoft.Office.Interop.Excel.Range nombre = hoja_trabajo.get_Range("H7:M10");
nombre.Merge();
nombre.Value = "BASES Y MOLDURAS";
nombre.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
nombre.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
nombre.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbGreen;
nombre.Font.Size = 30;
////////////////////////////
Microsoft.Office.Interop.Excel.Range encabezado = hoja_trabajo.get_Range("B5:T12");
encabezado.BorderAround2(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium);
encabezado.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhiteSmoke;
//CONTENIDO
hoja_trabajo.Cells[13, 2] = "Id";
hoja_trabajo.Cells[13, 3] = "Modelo";
hoja_trabajo.Cells[13, 6] = "Tamaño";
hoja_trabajo.Cells[13, 8] = "Material";
hoja_trabajo.Cells[13, 9] = "Categoria";
hoja_trabajo.Cells[13, 11] = "Cantidad";
hoja_trabajo.Cells[13, 12] = "Tipo";
hoja_trabajo.Cells[13, 14] = "P. P";
hoja_trabajo.Cells[13, 15] = "P. F";
hoja_trabajo.Cells[13, 16] = "P. M";
hoja_trabajo.Cells[13, 17] = "Descripción";
hoja_trabajo.Cells[13, 20] = "Peso";
int hi = 14;
int hj = 2;
for (int i = 0; i < tableProductosFiltro.Rows.Count; i++)
{
for (int j = 0; j < tableProductosFiltro.Columns.Count; j++)
{
if (j == 0)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
hj++;
}
else if (j == 1)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
hj = hj + 3;
aplicacion.get_Range("C" + hi.ToString(), "E" + hi.ToString()).Merge(true);
}
else if (j == 2)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
hj = hj + 2;
aplicacion.get_Range("F" + hi.ToString(), "G" + hi.ToString()).Merge(true);
}
else if (j == 3)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj++;
}
else if (j == 4)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj = hj + 2;
aplicacion.get_Range("I" + hi.ToString(), "J" + hi.ToString()).Merge(true);
}
else if (j == 5)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Cells[hi, hj].Style.Color = Color.LightBlue;
hj++;
}
else if (j == 6)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj = hj + 2;
aplicacion.get_Range("L" + hi.ToString(), "M" + hi.ToString()).Merge(true);
}
else if (j == 7 || j == 8 || j == 9)
{
float num = (float)Convert.ToDouble(tableProductosFiltro.Rows[i][j].ToString());
String numero = string.Format("{0:c2}", num);
hoja_trabajo.Cells[hi, hj] = numero;
hj++;
}
else if (j == 10)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
//hoja_trabajo.Range[hi, hj].Style.Color = Color.LightBlue;
hj = hj + 3;
aplicacion.get_Range("Q" + hi.ToString(), "S" + hi.ToString()).Merge(true);
}
else if (j == 11)
{
hoja_trabajo.Cells[hi, hj] = tableProductosFiltro.Rows[i][j].ToString();
hj++;
}
}
hj = 2;
hi++;
}
//bordes
inicio = hoja_trabajo.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
ultimo = hoja_trabajo.get_Range("B13", inicio);
Microsoft.Office.Interop.Excel.Borders bordeTotal = ultimo.Borders;
bordeTotal.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
bordeTotal.Weight = 3d;
//
//bold
hoja_trabajo.Range["B13:T13"].Font.Bold = true;
hoja_trabajo.Range["B13:T13"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGreen;
hoja_trabajo.Range["C6:E6"].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightSkyBlue;
aplicacion.get_Range("C13", "E13").Merge(true);
aplicacion.get_Range("F13", "G13").Merge(true);
aplicacion.get_Range("I13", "J13").Merge(true);
aplicacion.get_Range("Q13", "S13").Merge(true);
aplicacion.get_Range("L13", "M13").Merge(true);
libros_trabajo.SaveAs(fichero.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
txtGenerando.Text = "";
try
{
System.Diagnostics.Process.Start(fichero.FileName);
}
catch
{
DialogResult pregunta2;
pregunta2 = MetroFramework.MetroMessageBox.Show(this, "No se puede abrir el documento", "AVISO", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
DialogResult pregunta;
pregunta = MetroFramework.MetroMessageBox.Show(this, "\nDocumento generado con exito\n Guardado en: " + fichero.FileName + " ", "Documento", MessageBoxButtons.OK, MessageBoxIcon.Question);
Cursor.Current = Cursors.Default;
}
else
{
Cursor.Current = Cursors.Default;
txtGenerando.Text = "";
}
}
catch (Exception ex)
{
DialogResult pregunta;
pregunta = MetroFramework.MetroMessageBox.Show(this, "Ya se ha generado este documento", "AVISO" + ex, MessageBoxButtons.OK, MessageBoxIcon.Warning);
Console.WriteLine("Error" + ex);
Cursor.Current = Cursors.Default;
txtGenerando.Text = "";
}
}
else
{
DialogResult pregunta;
pregunta = MetroFramework.MetroMessageBox.Show(this, "No existen datos para generar el documento", "AVISO", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public string ExcelExport(System.Data.DataTable DT, string title)
{
try
{
//创建Excel
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(System.Type.Missing);
//创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出
Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
//如果数据中存在数字类型 可以让它变文本格式显示
ExcelSheet.Cells.NumberFormat = "@";
//设置工作表名
ExcelSheet.Name = title;
//设置Sheet标题
string start = "A1";
string end = ChangeASC(DT.Columns.Count) + "1";
Microsoft.Office.Interop.Excel.Range _Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.Merge(0); //单元格合并动作(要配合上面的get_Range()进行设计)
_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
_Range.Font.Size = 22; //设置字体大小
_Range.Font.Name = "宋体"; //设置字体的种类
ExcelSheet.Cells[1, 1] = title; //Excel单元格赋值
_Range.EntireColumn.AutoFit(); //自动调整列宽
//写表头
for (int m = 1; m <= DT.Columns.Count; m++)
{
ExcelSheet.Cells[2, m] = DT.Columns[m - 1].ColumnName.ToString();
start = "A2";
end = ChangeASC(DT.Columns.Count) + "2";
_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.Font.Size = 15; //设置字体大小
_Range.Font.Bold = true; //加粗
_Range.Font.Name = "宋体"; //设置字体的种类
_Range.EntireColumn.AutoFit(); //自动调整列宽
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
//写数据
for (int i = 0; i < DT.Rows.Count; i++)
{
for (int j = 1; j <= DT.Columns.Count; j++)
{
//Excel单元格第一个从索引1开始
// if (j == 0) j = 1;
ExcelSheet.Cells[i + 3, j] = DT.Rows[i][j - 1].ToString();
}
}
//表格属性设置
for (int n = 0; n < DT.Rows.Count + 1; n++)
{
start = "A" + (n + 3).ToString();
end = ChangeASC(DT.Columns.Count) + (n + 3).ToString();
//获取Excel多个单元格区域
_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.Font.Size = 12; //设置字体大小
_Range.Font.Name = "宋体"; //设置字体的种类
_Range.EntireColumn.AutoFit(); //自动调整列宽
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式 _Range.EntireColumn.AutoFit(); //自动调整列宽
}
ExcelApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存
//弹出保存对话框,并保存文件
Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog();
sfd.DefaultExt = ".xlsx";
sfd.Filter = "Office 2007 File|*.xlsx|Office 2000-2003 File|*.xls|所有文件|*.*";
if (sfd.ShowDialog() == true)
{
if (sfd.FileName != "")
{
ExcelBook.SaveAs(sfd.FileName); //将其进行保存到指定的路径
// MessageBox.Show("导出文件已存储为: " + sfd.FileName, "温馨提示");
}
}
//释放可能还没释放的进程
ExcelBook.Close();
ExcelApp.Quit();
// PubHelper.Instance.KillAllExcel(ExcelApp);
return(sfd.FileName);
}
catch
{
// MessageBox.Show("导出文件保存失败！", "警告！");
return(null);
}
}
/// <summary>
/// 修改电子表格
/// </summary>
public static void ChangeExcel(string fileName, int row, string col)
{
try
{
//FrmGress frm = new FrmGress();
//frm.Show();
// DevExpress.XtraEditors.ProgressBarControl gress = frm.progressBarControl1;
//gress.Properties.Step = 5;
//gress.PerformStep();
object wrap = true;
object addin = false;
object Indent = 0;
object Shrink = false;
object Reading = true;
object rows = row;
object cols = col;
Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(fileName);
Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);
string first = "";
for (int x = row; x < 1000; x++)
{
if (ws.get_Range(col + x.ToString(), Type.Missing).Value2 != null)
{
first = ws.get_Range(col + x.ToString(), Type.Missing).Value2.ToString();
break;
}
}
int start = 1;
int end = 1;
int temp = 1;
string sec = "";
string result = "";
bool stop = false;
for (int i = row; i < 1000; i++)
{
stop = false;
if (ws.get_Range(col + Convert.ToString(i + 1), Type.Missing).Value2 != null)
{
sec = ws.get_Range(col + Convert.ToString(i + 1), Type.Missing).Value2.ToString();
if (first != sec)
{
result = ws.get_Range(col + Convert.ToString(i), Type.Missing).Value2.ToString();
first = ws.get_Range(col + Convert.ToString(i + 1), Type.Missing).Value2.ToString();
if (end == 1)
{
start = i + 1;
}
//end=1;
stop = true;
}
else
{
end++;
//ws.Cells[i + 1, col] = "";
}
}
else
{
stop = true;
}
if (stop && end > 1)
{
//gress.PerformStep();
end += start - 1;
for (int k = start; k <= end; k++)
{
ws.Cells[k, col] = "";
}
Microsoft.Office.Interop.Excel.Range range = ws.get_Range(col + start.ToString(), col + end.ToString());
range.Merge(0);
// range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
range.WrapText = wrap;
range.AddIndent = addin;
range.IndentLevel = Indent;
range.ShrinkToFit = Shrink;
//range.ReadingOrder = Microsoft.Office.Interop.Excel.Constants.xlContext;
range.MergeCells = Reading;
range.Value2 = result;
end = 1;
start = i + 1;
if (ws.get_Range(col + Convert.ToString(i + 1), Type.Missing).Value2 == null)
{
range.Value2 = sec;
// gress.Properties.Step = 100;
// gress.PerformStep();
break;
}
}
}
ws.Activate();
wb.Save();
// frm.Close();
//ep.Quit();
ep.Visible = true;
}
catch (Exception e) { MessageBox.Show(e.Message); }
}
protected void ImgBtnExport_Click(object sender, ImageClickEventArgs e)
{
//HttpContext.Current.Response.Clear();
//HttpContext.Current.Response.AddHeader(
// "content-disposition", string.Format("attachment; filename={0}", "PurchaseOrder"));
//HttpContext.Current.Response.ContentType = "application/ms-excel";
// using (System.IO.StringWriter sw = new System.IO.StringWriter())
// {
// using (HtmlTextWriter htw = new HtmlTextWriter(sw))
// {
//Create a form to contain the grid
//Table table = new Table();
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
try
{
dttable = (DataTable)ViewState["ImportExel"];
dttable1 = (DataTable)ViewState["TermConditn"];
#region [For CompanyDetails-Excel]
#region [Image]
Microsoft.Office.Interop.Excel.Range range = ExcelApp.get_Range("A1", "G1");
range.Font.Size = 12;
range.Font.Bold = true;
range.Locked = true;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.RowHeight = 30;
range.Merge(true);
range.Value2 = imgAntTime.ImageUrl;
range.EntireColumn.ColumnWidth = 20;
range.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
#endregion
#region [Company Name]
Microsoft.Office.Interop.Excel.Range range1 = ExcelApp.get_Range("A2", "G2");
range1.Font.Size = 12;
range1.Font.Bold = true;
range1.Locked = true;
range1.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range1.RowHeight = 65;
range1.Merge(true);
range1.Value2 = lblCompanyName.Text + "\n" + lblCompanyAddress.Text + "\n" + "Phone No :" + lblPhnNo.Text + "\n" + "Fax No :" + lblFaxNo.Text;
range1.EntireColumn.ColumnWidth = 20;
range1.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range1.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
#endregion
#region [ReportName:PurchaseOrderDetails]
Microsoft.Office.Interop.Excel.Range range2 = ExcelApp.get_Range("A3", "G3");
range2.Font.Size = 12;
range2.Font.Bold = true;
range2.Locked = true;
range2.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range2.RowHeight = 15;
range2.Merge(true);
range2.Value2 = "Purchase Order Details";
range2.EntireColumn.ColumnWidth = 20;
range2.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range2.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
#endregion
#region [PODetails]
Microsoft.Office.Interop.Excel.Range range3 = ExcelApp.get_Range("A4", "D4");
range3.Font.Size = 12;
range3.Font.Bold = true;
range3.Locked = true;
range3.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range3.RowHeight = 30;
range3.Merge(true);
range3.Value2 = "To :" + "\n" + lblSuplier.Text;
range3.EntireColumn.ColumnWidth = 20;
#endregion
#region [PODetails2]
Microsoft.Office.Interop.Excel.Range range4 = ExcelApp.get_Range("E4", "G4");
range4.Font.Size = 12;
range4.Font.Bold = true;
range4.Locked = true;
range4.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range4.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range4.RowHeight = 30;
range4.Merge(true);
range4.Value2 = "PO No :" + lblPono.Text + "\n" + "PO Date:" + lblPODate.Text;
range4.EntireColumn.ColumnWidth = 20;
#endregion
#region [For Adding From Grid To Excel]
// Storing header part in Excel
for (int i = 1; i < dttable.Columns.Count + 1; i++)
{
ExcelApp.Cells[5, i] = dttable.Columns[i - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < dttable.Rows.Count; i++)
{
for (int j = 0; j < dttable.Columns.Count; j++)
{
ExcelApp.Cells[i + 6, j + 1] = dttable.Rows[i][j].ToString();
}
}
#endregion
#region [ReportName:Terms And Conditions]
Cnt1 = dttable.Rows.Count + 7;
Cnt2 = Convert.ToInt32(dttable.Rows.Count) - 1;
ExcelApp.Cells[Cnt1 + 1, Cnt2 + 1] = "Terms And Condition";
ExcelApp.get_Range(ExcelApp.Cells[Cnt1 + 1, Cnt2 + 1], ExcelApp.Cells[Cnt1 + 1, Cnt2 + 1]).Font.Bold = true;
#endregion
#region [For Adding From Grid To Excel]
if (ViewState["TermConditn"] != null)
{
if (dttable1.Rows.Count > 0)
{
// Storing header part in Excel
for (int i = 1; i < dttable1.Columns.Count + 1; i++)
{
if (i == 2)
{
Microsoft.Office.Interop.Excel.Range Headr = ExcelApp.get_Range(ExcelApp.Cells[dttable.Rows.Count + 7, 2], ExcelApp.Cells[dttable.Rows.Count + 7, 8]);
Headr.RowHeight = 30;
Headr.Merge(true);
Headr.Value2 = dttable1.Columns[i - 1].ColumnName;
}
else
{
ExcelApp.Cells[dttable.Rows.Count + 7, i] = dttable1.Columns[i - 1].ColumnName;
}
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < dttable1.Rows.Count; i++)
{
for (int j = 0; j < dttable1.Columns.Count; j++)
{
if (j == 1)
{
Microsoft.Office.Interop.Excel.Range TermsData = ExcelApp.get_Range(ExcelApp.Cells[dttable.Rows.Count + 8, 2], ExcelApp.Cells[dttable.Rows.Count + 8, 7]);
TermsData.RowHeight = 140;
TermsData.Merge(true);
TermsData.Value2 = dttable1.Rows[i][j].ToString();
}
else
{
//Microsoft.Office.Interop.Excel.Range Term = ExcelApp.get_Range(ExcelApp.Cells[i + dttable.Rows.Count + 8, j + 1],ExcelApp.Cells[i + dttable.Rows.Count + 8, j ]);
ExcelApp.Cells[i + dttable.Rows.Count + 8, j + 1] = dttable1.Rows[i][j].ToString();
//Term.RowHeight = 140;
//Term.Value2 = dttable1.Rows[i][j].ToString();
}
}
}
}
}
else
{
ExcelApp.Cells[dttable.Rows.Count + 7, 16] = "No Terms And Conditons";
}
#endregion
//ExcelApp.ActiveWorkbook.SaveCopyAs(@"E:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
// Obj_Comm.Export("Purchase Order" + DateTime.Now.ToString("dd-MMM-yyyy") + ".xls", ExcelApp);
//HttpContext.Current.Response.Write(sw.ToString());
//HttpContext.Current.Response.End();
//ExcelApp.ActiveWorkbook.Saved = true;
//ExcelApp.Quit();
// }
//}
//********
// render the table into the htmlwriter
//sw.Write(ExcelApp);
//htw.Write(ExcelApp);
//HttpContext.Current.Response.Write(sw.ToString());
//HttpContext.Current.Response.End();
ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
#endregion
}
catch (ThreadAbortException tex)
{
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
//}
//}
}
private void excelMergeCells(Microsoft.Office.Interop.Excel.Range excelcells, Microsoft.Office.Interop.Excel.Worksheet excelworksheet, String cellLeftTop, String cellRightBottom)
{
excelcells = excelworksheet.get_Range(cellLeftTop, cellRightBottom);
//Объединяем ячейки
excelcells.Merge(Type.Missing);
}
//打印功能
private Microsoft.Office.Interop.Excel._Worksheet printValue(Microsoft.Office.Interop.Excel._Worksheet mysheet, Microsoft.Office.Interop.Excel._Workbook mybook)
{
int ind = 0;
//内表信息
int rownum = dt记录详情.Rows.Count;
if (rownum > 14)
{
//在第6行插入
for (int i = 0; i < rownum - 14; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)mysheet.Rows[19, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
Microsoft.Office.Interop.Excel.Range range1 = mysheet.get_Range("C19");
range1.Merge(mysheet.get_Range("D19"));
range1.Merge(mysheet.get_Range("E19"));
//mysheet.Cells[19, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//水平居中
//mysheet.Cells[19, 3].Font.Name = "宋体";//字体大小
//mysheet.Cells[19, 3].Font.Size = 10.5;//字体大小
}
ind = rownum - 14;
}
for (int i = 0; i < rownum; i++)
{
mysheet.Cells[5 + i, 1].Value = dt记录详情.Rows[i]["序号"].ToString();
mysheet.Cells[5 + i, 2].Value = dt记录详情.Rows[i]["确认项目"].ToString();
mysheet.Cells[5 + i, 3].Value = dt记录详情.Rows[i]["确认内容"].ToString();
mysheet.Cells[5 + i, 6].Value = dt记录详情.Rows[i]["确认结果"].ToString() == "Yes" ? "√" : "×";
}
//外表信息
mysheet.Cells[3, 1].Value = " 生产指令编号： " + mySystem.Parameter.csbagInstruction;
mysheet.Cells[3, 5].Value = " 生产日期： " + mySystem.Parameter.csbagInstruction;
string temp = dt记录.Rows[0]["班次"].ToString() == "白班" ? "生产班次： 白班☑ 夜班□" : "生产班次： 白班□ 夜班☑";
mysheet.Cells[3, 6].Value = temp;
mysheet.Cells[19 + ind, 1].Value = " 备注： " + dt记录.Rows[0]["备注"].ToString();
String stringtemp = "";
stringtemp = "确认人：" + dt记录.Rows[0]["操作员"].ToString();
stringtemp = stringtemp + " 确认日期：" + Convert.ToDateTime(dt记录.Rows[0]["操作日期"].ToString()).Year.ToString() + "年 " + Convert.ToDateTime(dt记录.Rows[0]["操作日期"].ToString()).Month.ToString() + "月 " + Convert.ToDateTime(dt记录.Rows[0]["操作日期"].ToString()).Day.ToString() + "日";
mysheet.Cells[20 + ind, 1].Value = stringtemp;
stringtemp = "复核人：" + dt记录.Rows[0]["审核员"].ToString();
stringtemp = stringtemp + " 复核日期：" + Convert.ToDateTime(dt记录.Rows[0]["审核日期"].ToString()).Year.ToString() + "年 " + Convert.ToDateTime(dt记录.Rows[0]["审核日期"].ToString()).Month.ToString() + "月 " + Convert.ToDateTime(dt记录.Rows[0]["审核日期"].ToString()).Day.ToString() + "日";
mysheet.Cells[20 + ind, 5].Value = stringtemp;
//加页脚
int sheetnum;
SqlDataAdapter da = new SqlDataAdapter("select ID from " + table + " where 生产指令ID=" + InstruID.ToString(), mySystem.Parameter.conn);
DataTable dt = new DataTable("temp");
da.Fill(dt);
List <Int32> sheetList = new List <Int32>();
for (int i = 0; i < dt.Rows.Count; i++)
{
sheetList.Add(Convert.ToInt32(dt.Rows[i]["ID"].ToString()));
}
sheetnum = sheetList.IndexOf(Convert.ToInt32(dt记录.Rows[0]["ID"])) + 1;
//读取ID对应的生产指令编码
SqlCommand comm生产指令编码 = new SqlCommand();
comm生产指令编码.Connection = mySystem.Parameter.conn;
comm生产指令编码.CommandText = "select * from 生产指令 where ID= @name";
comm生产指令编码.Parameters.AddWithValue("@name", InstruID);
SqlDataReader myReader生产指令编码 = comm生产指令编码.ExecuteReader();
while (myReader生产指令编码.Read())
{
Instruction = myReader生产指令编码["生产指令编号"].ToString();
//List<String> list班次 = new List<string>();
//list班次.Add(myReader班次["班次"].ToString());
}
myReader生产指令编码.Close();
comm生产指令编码.Dispose();
mysheet.PageSetup.RightFooter = Instruction + "-02-" + sheetnum.ToString("D3") + " &P/" + mybook.ActiveSheet.PageSetup.Pages.Count.ToString(); // "生产指令-步骤序号- 表序号 /&P"; // &P 是页码
//返回
return(mysheet);
}
/// <summary>
/// 处理固定的4个参数表
/// </summary>
/// <param name="dt"></param>
/// <param name="name"></param>
/// <param name="rowsOffset"></param>
/// <param name="colsOffset"></param>
private void ParseParameterList(DataTable dt, String name, int rowsOffset, int colsOffset)
{
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 8]);
range.Select();
range.Merge(false);
ws.Cells.set_Item(rowsOffset - 2, colsOffset - 1, "PARAMETERLIST");
ws.Cells.set_Item(rowsOffset - 2, colsOffset, name);
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "类型名称");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "长度");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "小数位");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 6, "默认值");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 7, "必输");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 8, "短文本");
if (dt.Rows.Count > 0)
{
ParseParameterlistToExcel(dt, rowsOffset, colsOffset);
}
}
protected void ImgBtnExport_Click(object sender, ImageClickEventArgs e)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
try
{
dttable = (DataTable)ViewState["ImportExel"];
#region [For CompanyDetails-Excel]
#region [Image]
Microsoft.Office.Interop.Excel.Range range = ExcelApp.get_Range("A1", "K1");
range.Font.Size = 12;
range.Font.Bold = true;
range.Locked = true;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.RowHeight = 30;
range.Merge(true);
range.Value2 = imgAntTime.ImageUrl;
range.EntireColumn.ColumnWidth = 20;
range.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//range.CopyPicture(
//string path = string.Format("http://*****:*****@"D:\AntLOGO", Microsoft.Office.Core.MsoTriState.msoFalse,
//Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 100, 100);
//ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
catch (ThreadAbortException tex)
{
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
protected void ImgBtnExport_Click1(object sender, ImageClickEventArgs e)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
try
{
dttable = (DataTable)ViewState["ImportExel"];
#region [For RequisitionDetails-Excel]
#region [Image]
Microsoft.Office.Interop.Excel.Range range = ExcelApp.get_Range("A1", "I1");
range.Font.Size = 12;
range.Font.Bold = true;
range.Locked = true;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.RowHeight = 30;
range.Merge(true);
range.Value2 = imgAntTime.ImageUrl;
range.EntireColumn.ColumnWidth = 20;
range.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//range.CopyPicture(
//string path = string.Format("http://*****:*****@"D:\AntLOGO", Microsoft.Office.Core.MsoTriState.msoFalse,
//Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 100, 100);
ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
#endregion
}
catch (ThreadAbortException tex)
{
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//保存学生数据到指定Excel文件中
private bool saveFile(string fileName)
{
bool result = false;//默认保存失败
try
{
//创建Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;//获取缺少的object类型值
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workBook = excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet workSheet = workBook.Worksheets["Sheet1"]; //workBook.Worksheets.Add(missing, missing, missing, missing);
int rowIndex = 1; //excel操作行标号
Microsoft.Office.Interop.Excel.Range contentRange1 = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]];
contentRange1.Merge(); //合并单元格
contentRange1.RowHeight = 1;
rowIndex = rowIndex + 1; //第二行标号
Microsoft.Office.Interop.Excel.Range contentRange2 = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]];
contentRange2.Merge(); //合并单元格
contentRange2.RowHeight = 40;
contentRange2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示
contentRange2.Font.Bold = true;
contentRange2.Font.Name = "宋体";
contentRange2.Font.Size = 18;
contentRange2.NumberFormat = "@";//文本格式
workSheet.Cells[rowIndex, 1] = this.titleInformation.Xxmc + " " + this.titleInformation.Xymc + " " + this.titleInformation.Zymc + "人员基本信息表";
//contentRange2.UseStandardHeight = 5;
rowIndex = rowIndex + 1; //第三行标号
Microsoft.Office.Interop.Excel.Range contentRange3 = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]];
contentRange3.RowHeight = 30; //设置行高
contentRange3.NumberFormat = "@"; //文本格式
Microsoft.Office.Interop.Excel.Range contentRange31 = excel.Range[workSheet.Cells[rowIndex, 2], workSheet.Cells[rowIndex, 3]];
contentRange31.Merge();
workSheet.Cells[rowIndex, 1] = "班级：";
workSheet.Cells[rowIndex, 2] = this.titleInformation.Bjmc;
workSheet.Cells[rowIndex, 4] = "填表人：";
workSheet.Cells[rowIndex, 5] = this.titleInformation.Tbrmc;
workSheet.Cells[rowIndex, 7] = "联系方式：";
workSheet.Cells[rowIndex, 8] = this.titleInformation.Lxdh;
workSheet.Cells[rowIndex, 10] = "填表日期：";
workSheet.Cells[rowIndex, 11] = this.titleInformation.Tbrq.ToString("yyyy/MM/dd");
rowIndex = rowIndex + 1;//第四行标号
Microsoft.Office.Interop.Excel.Range contentRange4 = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]];
contentRange4.Worksheet.StandardWidth = 15;
contentRange4.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
contentRange4.Borders.LineStyle = 1; //设置边框格式
contentRange4.NumberFormat = "@"; //文本格式
workSheet.Cells[rowIndex, 1] = "姓名";
workSheet.Cells[rowIndex, 2] = "身份证号";
workSheet.Cells[rowIndex, 3] = "性别";
workSheet.Cells[rowIndex, 4] = "民族";
workSheet.Cells[rowIndex, 5] = "文化程度";
workSheet.Cells[rowIndex, 6] = "政治面貌";
workSheet.Cells[rowIndex, 7] = "宗教信仰";
workSheet.Cells[rowIndex, 8] = "户籍地代码";
workSheet.Cells[rowIndex, 9] = "户籍地详细地址";
workSheet.Cells[rowIndex, 10] = "户口性质";
workSheet.Cells[rowIndex, 11] = "备注";
rowIndex = rowIndex + 1;//第五行标号开始进行数据录入
foreach (StudentInfo stuItem in this.StuList)
{
Microsoft.Office.Interop.Excel.Range contentRange = excel.Range[workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, 11]];
contentRange.Worksheet.StandardWidth = 15;
contentRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
contentRange.NumberFormat = "@"; //文本格式
contentRange.Borders.LineStyle = 1; //设置边框格式
workSheet.Cells[rowIndex, 1] = stuItem.Xm;
workSheet.Cells[rowIndex, 2] = stuItem.Zjhm;
workSheet.Cells[rowIndex, 3] = stuItem.Xb;
workSheet.Cells[rowIndex, 4] = stuItem.Mz;
workSheet.Cells[rowIndex, 5] = stuItem.Whcd;
workSheet.Cells[rowIndex, 6] = stuItem.Zzmm;
workSheet.Cells[rowIndex, 7] = stuItem.Zjxy;
workSheet.Cells[rowIndex, 8] = stuItem.Hjdxzqh;
workSheet.Cells[rowIndex, 9] = stuItem.Hjdxz;
workSheet.Cells[rowIndex, 10] = stuItem.Hkxz;
workSheet.Cells[rowIndex, 11] = stuItem.Bzxx;
rowIndex++; //换下一行
}
excel.Application.DisplayAlerts = false; //不显示提示对话框
workBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlAddIn8); //保存工作表:xlAddIn8设置输出格式为xls
workBook.Close(false, missing, missing);
//获取并关闭Excel相关进程
System.Diagnostics.Process[] excelProcess = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process p in excelProcess)
{
p.Kill();
}
result = true;//标志正常写入完成
}
catch (Exception e)
{
Console.WriteLine(e.Message);
result = false;
}
return(result);
}
private void ExportExcel(string fileName, DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
Microsoft.Office.Interop.Excel.Range range1 = worksheet.get_Range("B2", "W3");
range1.Select();
range1.Merge();
range1.Font.Size = 15;
range1.Borders.LineStyle = 1;
range1.Value2 = "昌 吉 州 人 民 医 院 手 术 通 知 单";
range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range range2 = worksheet.get_Range("B4", "W4");
range2.Select();
range2.Merge();
range2.Font.Size = 11;
range2.Value2 = " 手术日期：" + dtDataTime.Text;
range2.Borders.LineStyle = 1;
Microsoft.Office.Interop.Excel.Range excelRange = worksheet.get_Range("A6", "W6");
excelRange.Select();
xlApp.ActiveWindow.FreezePanes = true;
//写入标题
//int ColCount = 0;
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[5, i + 2] = myDGV.Columns[i].HeaderText;
range = xlApp.Cells[5, i + 2];
range.Font.Bold = true;
range.RowHeight = 25;
range.Interior.ColorIndex = 34;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.Borders.LineStyle = 1;
if (i == 3 || i == 8 || i == 9)
{
range.ColumnWidth = 10;
}
else
{
range.EntireColumn.AutoFit();
}
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 6, i + 2] = myDGV.Rows[r].Cells[i].Value;
range = worksheet.Cells[r + 6, i + 2];
range.Font.Size = 9;
range.WrapText = true;
int[] a = { 1, 1, 2, 4, 5, 6, 7, 8, 9 };
foreach (int dr in a)
{
if (i == dr)
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
else
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
}
range.EntireRow.AutoFit();//行高自适应
range.Borders.LineStyle = 1;
}
System.Windows.Forms.Application.DoEvents();
}
//worksheet.Columns.EntireColumn.Width = 40;//列宽自适应
//worksheet.Rows.AutoFilter();
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
ProgressBar pbar = new ProgressBar();
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public static void CreateHeaders(int row, int col, string htext, string cell1,
string cell2, int mergeColumns, string b, bool font, int size, string
fcolor)
{
Worksheet.Cells[row, col] = htext;
WorkSheetRange = Worksheet.Range[cell1, cell2];
WorkSheetRange.Merge(mergeColumns);
switch (b)
{
case "YELLOW":
WorkSheetRange.Interior.Color = Color.Yellow.ToArgb();
break;
case "GRAY":
WorkSheetRange.Interior.Color = Color.Gray.ToArgb();
break;
case "GAINSBORO":
WorkSheetRange.Interior.Color =
Color.Gainsboro.ToArgb();
break;
case "Turquoise":
WorkSheetRange.Interior.Color =
Color.Turquoise.ToArgb();
break;
case "PeachPuff":
WorkSheetRange.Interior.Color =
Color.PeachPuff.ToArgb();
break;
}
WorkSheetRange.Borders.Color = Color.Black.ToArgb();
WorkSheetRange.Font.Bold = font;
WorkSheetRange.ColumnWidth = size;
WorkSheetRange.Font.Color = fcolor.Equals("") ? Color.White.ToArgb() : Color.Black.ToArgb();
}
public void OutPut2(DataTable dt)
{
bool isShowExcel = false;
if (dt == null)
{
return;
}
if (dt.Rows.Count == 0)
{
//return;
}
Missing miss = Missing.Value;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("请确保您的电脑已经安装Excel！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//xlApp.UserControl = true;
Microsoft.Office.Interop.Excel.Workbooks workBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workBook = null;
if (!File.Exists(base.m_TemplateFilePath))
{
workBook = workBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//创建新的
}
else
{
workBook = workBooks.Add(base.m_TemplateFilePath); //根据现有excel模板产生新的Workbook
}
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1]; //获取sheet1
xlApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存
//workSheet.get_Range("A3", "B3").Merge(workSheet.get_Range("A3", "B3").MergeCells);//合并单元格
if (workSheet == null)
{
MessageBox.Show("请确保您的电脑已经安装Excel！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
try
{
//Microsoft.Office.Interop.Excel.Range range = null;
xlApp.Visible = isShowExcel;//若是true,则在导出的时候会显示excel界面
int totalCount = dt.Rows.Count;
if (File.Exists(base.m_TemplateFilePath))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
for (int k = 0; k < workBook.Names.Count; k++)
{
if (workBook.Names.Item(dt.Columns[j].Caption) != null)//.Name == dt.Columns[j].Caption)
{
//定义第一个全局命名区域
int row = workBook.Names.Item(dt.Columns[j].Caption).RefersToRange.Row;
int col = workBook.Names.Item(dt.Columns[j].Caption).RefersToRange.Column;
workSheet.Cells[row + i + 1, col] = "'" + dt.Rows[i][j].ToString();
break;
}
}
}
}
}
else
{
if (MessageBox.Show("没有找到数据表格模板，是否按默认的格式导出？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.No)
{
return;
}
workSheet.Cells[1, 1] = dt.TableName;//导出标题
for (int j = 0; j < dt.Columns.Count; j++)
{
workSheet.Cells[4, j + 1] = dt.Columns[j].ColumnName;
workBook.Names.Add(dt.Columns[j].ColumnName, workSheet.Cells[4, j + 1]);//, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//workBook.Names.Item(dt.Columns[j].ColumnName).RefersTo = workSheet.get_Range(workSheet.Cells[4, j + 1]);
}
Microsoft.Office.Interop.Excel.Range newExpenseTypeRange = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[3, dt.Columns.Count]);
newExpenseTypeRange.Merge(workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[3, dt.Columns.Count]).MergeCells);//合并单元格
newExpenseTypeRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
newExpenseTypeRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//写入数值
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j].GetType().Equals(typeof(DateTime)))
{
workSheet.Cells[i + 5, j + 1] = ((DateTime)(dt.Rows[i][j])).ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
workSheet.Cells[i + 5, j + 1] = dt.Rows[i][j];//项目序号
}
}
}
WorkSheetPageSet(xlApp, workSheet);
}
string fileExt = DateTime.Now.ToString("yyyyMMddHHmmss");
string fileOutPut = base.m_OutputFilePath.Insert(m_OutputFilePath.LastIndexOf("."), fileExt);
workBook.SaveAs(fileOutPut, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//workSheet.SaveAs(base.templateFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBooks.Close();
if (MessageBox.Show("Excel导出成功：" + fileOutPut + "\r\n是否要打开？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
System.Diagnostics.Process.Start(fileOutPut);
}
}
catch (Exception ex)
{
MessageBox.Show("Excel导出失败，错误：" + ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
xlApp.Quit();
}
finally
{
xlApp.Quit();
}
}///// <summary>
public void ExcelExport(System.Data.DataTable dt, string SheetName)
{
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
SaveFileDialog savefiledialog = new SaveFileDialog();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata = appexcel.Workbooks.Add(System.Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets[1];
//Microsoft.Office.Interop.Excel.Range rangedata;
//创建Excel
//Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbook ExcelBook = appexcel.Workbooks.Add(System.Type.Missing);
////创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出
//Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
////设置Sheet标题
string start = "A1";
string end = ChangeASC(dt.Columns.Count) + "1";
Microsoft.Office.Interop.Excel.Range _Range = (Microsoft.Office.Interop.Excel.Range)worksheetdata.get_Range(start, end);
_Range.Merge(0); //单元格合并动作(要配合上面的get_Range()进行设计)
_Range = worksheetdata.get_Range(start, end);
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
_Range.Font.Size = 22; //设置字体大小
_Range.Font.Name = "宋体"; //设置字体的种类
worksheetdata.Cells[1, 1] = SheetName; //Excel单元格赋值
_Range.EntireColumn.AutoFit(); //自动调整列宽
//设置对象不可见
appexcel.Visible = false;
System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
//workbookdata = appexcel.Workbooks.Add(miss);
//worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
//给工作表赋名称
worksheetdata.Name = SheetName;
start = "A2";
end = ChangeASC(dt.Columns.Count) + "2";
_Range = worksheetdata.get_Range(start, end);
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetdata.Cells[2, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
//rangedata = worksheetdata.get_Range("a3", miss);
//Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数，最大行
int irowcount = dt.Rows.Count;
int iparstedrow = 1, icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
int ieachsize = 1000;
//icolumnaccount为实际列数，最大列数
int icolumnaccount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize + 1, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow <= irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
{
icurrsize = irowcount - iparstedrow + 1;
}
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
{
objval[i, j] = dt.Rows[i + iparstedrow - 1][j].ToString();
}
//System.Windows.Forms.Application.DoEvents();
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
_Range = worksheetdata.get_Range(X, col);
// 调用range的value2属性，把内存中的值赋给excel
_Range.Value2 = objval;
_Range.EntireColumn.AutoFit(); //自动调整列宽
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
iparstedrow = iparstedrow + icurrsize;
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(_Range);
_Range = null;
//调用方法关闭excel进程
appexcel.Visible = true;
}
protected virtual void Merge(int col1, int row1, int col2, int row2)
{
_excelRange = _excelSheet.get_Range(_excelSheet.Cells[col1, row1], _excelSheet.Cells[col1, row2]);
_excelRange.Merge(true);
}
/// <summary>
/// 如果字段的类型是结构体，则需要分开处理
/// </summary>
/// <param name="dt"></param>
/// <param name="rowsOffset"></param>
/// <param name="colsOffset"></param>
private void ParseStructToExcel(DataTable dt, ref int rowsOffset, int colsOffset)
{
foreach (DataRow row in dt.Rows)
{
if (row["DataType"].ToString() == "STRUCTURE")
{
String structurName = row["DataTypeName"].ToString();
String Documentation = row[FuncFieldText.DOCUMENTATION].ToString();
if (_parsedStructure.Contains(structurName))
{
return;
}
DataTable dt2 = m_function.FunctionMeta.StructureDetail[structurName];
if (dt2 != null)
{
if (dt2.Rows.Count > 0)
{
_parsedStructure.Add(structurName);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]);
range.Select();
range.Merge(false);
ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "STRUCTURE");
ws.Cells.set_Item(rowsOffset - 4, colsOffset, "结构");
ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称");
ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注");
ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, structurName);
ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation);
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本");
ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset);
rowsOffset += dt2.Rows.Count + 7;
}
}
}
else if (row["DataType"].ToString() == "TABLE")
{
String tableName = row["DataTypeName"].ToString();
String Documentation = row[FuncFieldText.DOCUMENTATION].ToString();
if (_parsedTable.Contains(tableName))
{
return;
}
DataTable dt2 = m_function.FunctionMeta.StructureDetail[tableName];
if (dt2 != null)
{
if (dt2.Rows.Count > 0)
{
_parsedTable.Add(tableName);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]);
range.Select();
range.Merge(false);
ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "TABLE");
ws.Cells.set_Item(rowsOffset - 4, colsOffset, "表");
ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称");
ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注");
ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, tableName);
ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation);
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本");
ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset);
rowsOffset += dt2.Rows.Count + 7;
}
}
}
}
}
/// <summary>
/// 如果字段的类型是结构体，则需要分开处理
/// </summary>
/// <param name="dt"></param>
/// <param name="rowsOffset"></param>
/// <param name="colsOffset"></param>
private void ParseStructToExcel(DataTable dt, ref int rowsOffset, int colsOffset)
{
foreach (DataRow row in dt.Rows)
{
if (row["DataType"].ToString() == "STRUCTURE")
{
String structurName = row["DataTypeName"].ToString();
String Documentation = row[FuncFieldText.DOCUMENTATION].ToString();
if (_parsedStructure.Contains(structurName))
{
return;
}
DataTable dt2 = m_function.FunctionMeta.StructureDetail[structurName];
if (dt2 != null)
{
if (dt2.Rows.Count > 0)
{
_parsedStructure.Add(structurName);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]);
range.Select();
range.Merge(false);
ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "STRUCTURE");
ws.Cells.set_Item(rowsOffset - 4, colsOffset, "结构");
ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称");
ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注");
ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, structurName);
ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation);
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本");
ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset);
rowsOffset += dt2.Rows.Count + 7;
}
}
}
else if (row["DataType"].ToString() == "TABLE")
{
String tableName = row["DataTypeName"].ToString();
String Documentation = row[FuncFieldText.DOCUMENTATION].ToString();
if (_parsedTable.Contains(tableName))
{
return;
}
DataTable dt2 = m_function.FunctionMeta.StructureDetail[tableName];
if (dt2 != null)
{
if (dt2.Rows.Count > 0)
{
_parsedTable.Add(tableName);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]);
range.Select();
range.Merge(false);
ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "TABLE");
ws.Cells.set_Item(rowsOffset - 4, colsOffset, "表");
ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称");
ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注");
ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, tableName);
ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation);
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本");
ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset);
rowsOffset += dt2.Rows.Count + 7;
}
}
}
}
}
/// <summary>
/// 合并单元格并填写值
/// </summary>
/// <param name="worksheet"></param>
/// <param name="value">单元格的值</param>
/// <param name="sRow">开始行</param>
/// <param name="sCol">开始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endCol">结束列</param>
public static void MergeCells(this Microsoft.Office.Interop.Excel.Worksheet worksheet, object value, int sRow, int sCol, int endRow, int endCol)
{
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[sRow, sCol], worksheet.Cells[endRow, endCol]);
range.Merge();
range.Value = value;
}