public Excel.Worksheet AddSheet(string SheetName)
//添加一个工作表
{
Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
s.Name = SheetName;
return(s);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="xlsSheet"></param>
/// <param name="xlsBook"></param>
/// <param name="xlsSheetName"></param>
private string SaveAsExcel(Excel.Worksheet xlsSheet, Excel.Workbook xlsBook)
{
//string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) ;
string desktopPath = @"C:\NPOI";
if (System.IO.Directory.Exists(desktopPath) == false)
{
System.IO.Directory.CreateDirectory(desktopPath);
}
desktopPath = desktopPath + "\\" + "IQC";
if (System.IO.Directory.Exists(desktopPath) == true)
{
System.IO.Directory.Delete(desktopPath);
}
xlsSheet.SaveAs(desktopPath, 56); //保存在
////OpenXls(desktopPath);
//PrinttheExcel(xlsSheet, desktopPath);
xlsBook.Close();
xlsSheet = null;
KillProcess("Excel");
string dd = System.IO.Path.GetExtension(desktopPath);
return(desktopPath + ".xls");
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列(列对应的字母)</param>
/// <param name="endColumn">结束列(列对应的字母)</param>
/// <param name="width"></param>
public void SetColumnWidth(string startColumn, string endColumn, int width)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
/// 自动调整列宽
/// </summary>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(string column)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
range.EntireColumn.AutoFit();
}
private void SetExcelTitleTestROHS(List <SampleItemsIqcRecordModel> models, Excel.Worksheet xlsSheet)
{
foreach (var m in models)
{
if (m.SampleItem.Contains("ROHS检验"))
{
xlsSheet.Cells[3, 14] = "ROHS结果：□OK □NG □NA";
xlsSheet.Cells[4, 14] = "测试编号：";
return;
}
if (m.SampleItem.Contains("NOT ROHS"))
{
xlsSheet.Cells[3, 14] = "不用测 ROHS";
xlsSheet.Cells[4, 14] = " ";
return;
}
}
//SpamleRecordDal RecordDal = new SpamleRecordDal();
//Int64 Remainder = RecordDal.GetMaiterialConunt(models[0].SampleMaterial) % 2;
Int64 Remainder = 2;
if (Remainder == 0)
{
xlsSheet.Cells[3, 14] = "ROHS结果：□OK □NG □NA"; xlsSheet.Cells[4, 14] = "测试编号：";
}
else
{
xlsSheet.Cells[3, 14] = "不用测 ROHS"; xlsSheet.Cells[4, 14] = " ";
}
}
/// <summary>
/// 设置行高
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="endRow">结束行</param>
/// <param name="height">行高</param>
public void SetRowHeight(int startRow, int endRow, int height)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
range.RowHeight = height;
}
//--------------------------------------------------------------------------------------------------------
/// <summary>
/// 关闭Excel文件，释放对象；最后一定要调用此函数，否则会引起异常
/// </summary>
/// <param></param>
public void CloseExcelApplication()
{
try
{
xlsWbs = null;
xlsWb = null;
xlsWs = null;
xlsRg = null;
if (xlsApp != null)
{
xlsApp.ActiveWorkbook.Close(false, null, null);
xlsApp.Workbooks.Close();
//Object missing = Type.Missing;
xlsApp.Quit();
xlsApp = null;
//ReleaseAllRef(excelApplication);//Error
}
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 自动调整行高
/// </summary>
/// <param name="columnNum">列号</param>
public void RowAutoFit(int rowNum)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// J列到V列 加虚线，A到V列 加实线
/// <param name="xlsSheet">Excel表</param>
/// <param name="startRowIndex">启始行</param>
/// <param name="rowIndex">行数</param>
/// <param name="A">A列</param>
/// <param name="J">J列</param>
/// <param name="V">V列</param>
private void ResetXlsCellStatus(Excel.Worksheet xlsSheet, int startRowIndex, int rowIndex, string A, string J, string V)
{
int m = startRowIndex + rowIndex + 1;
xlsSheet.get_Range(J + startRowIndex, V + m).Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlDashDot; //加虚线
xlsSheet.get_Range(A + m, V + m).Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous; //加实线 抵部不好加 顶部加1
}
/// <summary>
/// 以excelOpenFileName为模板新建Excel文件
/// </summary>
public bool OpenExcelFile()
{
if (xlsApp != null)
{
//检查文件是否存在
if (xlsOpenFileName == "")
{
throw new Exception("请选择文件!");
}
}
if (!File.Exists(xlsOpenFileName))
{
throw new Exception(xlsOpenFileName + "该文件不存在!");
}
try
{
xlsApp = new Excel.ApplicationClass();
xlsWbs = xlsApp.Workbooks;
xlsWb = ((Excel.Workbook)xlsWbs.Open(xlsOpenFileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value));
xlsWs = (Excel.Worksheet)xlsWb.Worksheets[excelActiveWorkSheetIndex];
xlsApp.Visible = false;
return(true);
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception("(1)没有安装Excel 2003;\n(2)或没有安装Excel 2003 .NET 可编程性支持;\n详细信息:\n" + e.Message);
}
}
/// <summary>
/// 从J列开始 画13列 垂直线
/// </summary>
/// <param name="xlsSheet"></param>
/// <param name="startRowIndex"></param>
/// <param name="stopRowIndex"></param>
private static void xlEdgeLeftLine(Excel.Worksheet xlsSheet, int startRowIndex, int stopRowIndex, int nunmber, char startNunmber)
{
for (int i = 0; i < nunmber; i++)
{
string ColName = Convert.ToChar(startNunmber + i).ToString();
xlsSheet.get_Range(ColName + startRowIndex, ColName + stopRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
}
}
public void setFormat()
{
Excel.Worksheet mysheet = myWorkBook.Sheets[1] as Excel.Worksheet;
mysheet.Cells.WrapText = true;
//mysheet.Cells.AutoFit();
mysheet.Rows.AutoFit();
mysheet.Columns.AutoFit();
}
public ClsAllExcel()
{
xlsApp = null;
xlsWbs = null;
xlsWb = null;
xlsWs = null;
ActiveSheetIndex = 1;
}
//Ribbon界面的回调函数，响应事件，将Excel表格数据写入CAD
public void OnButton1Pressed(IRibbonControl control)
{
//选取Excel表格数据
wbk = app.ActiveWorkbook;
wsh = (Worksheet)wbk.ActiveSheet;
Excel.Range rngLeftTop = null;
Excel.Range rngRightButtom = null;
rngLeftTop = (Excel.Range)app.InputBox("点击左上角单元格", Type: 8);
rngRightButtom = (Excel.Range)app.InputBox("点击右下角单元格", Type: 8);
object[,] data = (object[, ])wsh.Range[rngLeftTop.Address + ":" + rngRightButtom.Address].Value2;
//把数据导入Access数据库
try
{
AccessDataBase.WriteDB(data);
MessageBox.Show("选定的数据读取完毕，点击确定导入CAD");
}
catch (SystemException ex)
{
MessageBox.Show(ex.ToString());
}
//选定的数据读取完毕，点击确定导入CAD
if (rngLeftTop != null & rngRightButtom != null)
{
//打开cad
try
{
AcadApp = (Autodesk.AutoCAD.Interop.AcadApplication)System.Runtime.InteropServices.Marshal.GetActiveObject("AutoCAD.Application");
AcadDoc = AcadApp.ActiveDocument;
}
catch
{
OpenFileDialog op = new OpenFileDialog();
op.Filter = "CAD文件(*.dwg)|*.dwg|CAD图形文件(*.dxf)|*.dxf";
op.Title = "打开CAD文件";
op.ShowDialog();
string filePath = op.FileName;
if (filePath == "")
{
MessageBox.Show("选择CAD文件无效!!", "文件无效!!");
}
else
{
AcadApp = new Autodesk.AutoCAD.Interop.AcadApplication();
AcadDoc = AcadApp.Documents.Open(filePath, null, null);
}
}
AcadApp.Application.Visible = true;
//使CAD程序跳到在最前面，需要添加引用“Microsoft.VisualBasic”
Microsoft.VisualBasic.Interaction.AppActivate(AcadApp.Caption);
//让CAD自动执行netload命令加载程序集DLL,如果注册表加载方法无效的话
AcadDoc.SendCommand("(command \"_netload\" \"" + @"C:\\Windows\\System\\xl2cad\\xl2cad.dll" + "\") ");
AcadDoc.SendCommand("zdbtext ");
}
else
{
MessageBox.Show("没有选择数据！");
}
}
/// <summary>
/// 自动调整列宽
/// </summary>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(int columnNum)
{
string strcolumnNum = GetColumnName(columnNum);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
range.EntireColumn.AutoFit();
}
public void CreateExcel(IDictionary <string, System.Data.DataTable> iDicDT, string FilePath, bool Visible)
{
Excel.Application excel = new Excel.Application(); //引用Excel对象
Excel.Workbook book = excel.Application.Workbooks.Add(System.Reflection.Missing.Value); //引用
Excel.Worksheet sheet3 = (Excel.Worksheet)book.Worksheets["Sheet3"];//选中
object nothing = System.Reflection.Missing.Value;
book.Worksheets.Add(nothing, sheet3, iDicDT.Count - 3, nothing);
int index = 0;
foreach (KeyValuePair <string, System.Data.DataTable> val in iDicDT)
{
index++;
Excel.Worksheet tempExcel = (Excel.Worksheet)book.Worksheets["Sheet" + index];//选中
tempExcel.Name = val.Key;
System.Data.DataTable tempdt = val.Value;
for (int i = 0; i < tempdt.Columns.Count; i++)
{
tempExcel.Cells[1, i + 1] = tempdt.Columns[i].ColumnName;
}
for (int i = 0; i < tempdt.Rows.Count; i++)
{
for (int j = 0; j < tempdt.Columns.Count; j++)
{
try
{
tempExcel.Cells[i + 2, j + 1] = tempdt.Rows[i][j].ToString();
}
catch { }
}
}
}
//保存
if (!string.IsNullOrEmpty(FilePath))
{
SaveExcelFile(excel, FilePath);
}
//打开显示
excel.Visible = Visible;
excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列</param>
/// <param name="endColumn">结束列</param>
/// <param name="width"></param>
public void SetColumnWidth(int startColumn, int endColumn, int width)
{
string strStartColumn = GetColumnName(startColumn);
string strEndColumn = GetColumnName(endColumn);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
range.ColumnWidth = width;
}
//将内存中数据表格添加到Excel指定工作表的指定位置二
public void AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
ws.Cells[i + startX, j + startY] = dt.Rows[i][j];
}
}
}
/// <summary>
/// BC列合并时处理数据
/// </summary>
/// <param name="xlsSheet"></param>
/// <param name="startMergeRowIndex"></param>
/// <param name="ValueUp"></param>
/// <param name="ValueDown"></param>
/// <param name="standardValue"></param>
private void setBCValueToXlsCell(Excel.Worksheet xlsSheet, int startMergeRowIndex, int endMergeRowIndex, string standardValue)
{
xlsSheet.get_Range("B" + startMergeRowIndex, "C" + endMergeRowIndex).Merge(); //合并单元格
xlsSheet.get_Range("B" + startMergeRowIndex).Value = standardValue;
xlsSheet.get_Range("B" + startMergeRowIndex).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; //水平对齐靠左
xlsSheet.get_Range("B" + startMergeRowIndex).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //垂直对对齐 居中
xlsSheet.get_Range("B" + startMergeRowIndex).NumberFormatLocal = "@"; //设置为文本
xlsSheet.get_Range("B" + startMergeRowIndex).Font.Size = "12"; //设置字体大小
xlsSheet.get_Range("B" + startMergeRowIndex).Font.Name = "宋体"; //设置字体
}
private void setCPDEmethod(Excel.Worksheet xlsSheet, int startMergeRowIndex, int endMergeRowIndex)
{
xlsSheet.get_Range("H" + startMergeRowIndex, "I" + endMergeRowIndex).Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
xlsSheet.get_Range("H" + startMergeRowIndex).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; //水平对齐靠左
xlsSheet.get_Range("I" + startMergeRowIndex).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //水平对齐靠左
xlsSheet.get_Range("H" + startMergeRowIndex, "I" + endMergeRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsSheet.get_Range("H" + startMergeRowIndex, "I" + endMergeRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsSheet.get_Range("H" + startMergeRowIndex, "I" + endMergeRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsSheet.get_Range("A" + startMergeRowIndex, "H" + endMergeRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
}
//设置一个单元格的属性 字体， 大小，颜色 ，对齐方式
public void SetCellProperty(Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)
{
name = "宋体";
size = 12;
color = Excel.Constants.xlAutomatic;
HorizontalAlignment = Excel.Constants.xlRight;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
}
/// <summary>
/// 填充Excel标题内容
/// </summary>
/// <param name="models"></param>
/// <param name="xlsSheet"></param>
private void SetExcelTitle(List <SampleItemsIqcRecordModel> models, Excel.Worksheet xlsSheet)
{
xlsSheet.Cells[2, 2] = DateTime.Now.ToString("yyyy-MM-dd");
xlsSheet.Cells[2, 8] = "品号：" + models[0].SampleMaterial;
xlsSheet.Cells[2, 17] = "NO:" + models[0].OrderID;
xlsSheet.Cells[3, 1] = "品名：" + models[0].SampleMaterialName;
xlsSheet.Cells[3, 5] = "规格：" + models[0].SampleMaterialSpec;
xlsSheet.Cells[3, 11] = "数量：" + models[0].SampleMaterialNumber.ToString();
xlsSheet.Cells[4, 1] = "供应商:" + models[0].SampleMaterialSupplier;
xlsSheet.Cells[4, 5] = "图号/检验规范：" + models[0].SampleMaterialDrawID;
xlsSheet.Cells[4, 11] = "检验方式：" + models[0].CheckWay;
}
/// <summary>
/// B列 插入标准值
/// </summary>
/// <param name="xlsSheet"></param>
/// <param name="startRowIndex">起始行</param>
/// <param name="standardValue"></param>
/// <param name="column">列数</param>
private void InsertStandardValue(Excel.Worksheet xlsSheet, int startRowIndex, string standardValue, string column)
{
int StopRowIndex = startRowIndex + 1;
xlsSheet.get_Range(column + startRowIndex, "B" + StopRowIndex).Merge(); //合并单元格
xlsSheet.get_Range(column + startRowIndex, "B" + StopRowIndex).Value = standardValue;
xlsSheet.get_Range(column + startRowIndex).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; //水平对齐靠左
xlsSheet.get_Range(column + startRowIndex).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //垂直对对齐 居中
xlsSheet.get_Range(column + startRowIndex).NumberFormatLocal = "@"; //设置为文本
xlsSheet.get_Range(column + startRowIndex).Font.Size = "12"; //设置字体大小
xlsSheet.get_Range(column + startRowIndex).Font.Name = "宋体"; //设置字体
}
private void createExcel()
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;
try
{
FileInfo file = new FileInfo(fileLoc);
if (file.Exists)
{
excelApp = new Excel.Application();
workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
sheets = workbook.Sheets;
//check columns exist
foreach (Excel.Worksheet sheet in sheets)
{
Console.WriteLine(sheet.Name);
sheet.Select(Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = "My New Sheet";
newSheet.Cells[1, 1] = "BOO!";
workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
}
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
newSheet = null;
sheets = null;
workbook = null;
excelApp = null;
GC.Collect();
}
}
public static void Merge(string sourceFile, string baseFile, string targetFile, int rowCount, int columnCount)
{
object MISSING = Type.Missing;
Excel.Application excel = new Excel.Application();
try
{
Excel.Workbook sourceBook = excel.Workbooks.Open(sourceFile,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
Excel.Worksheet sourceSheet = (Excel.Worksheet)sourceBook.Sheets[1];
Excel.Workbook baseBook = excel.Workbooks.Open(baseFile,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
Excel.Worksheet baseSheet = (Excel.Worksheet)baseBook.Sheets[1];
//baseSheet.Unprotect(string.Empty);
for (int column = 1; column <= columnCount; column++)
{
for (int row = 1; row <= rowCount; row++)
{
Excel.Range baseRange = (Excel.Range)baseSheet.Cells[row, column];
if (baseRange.AllowEdit)
{
Excel.Range sourceRange = (Excel.Range)sourceSheet.Cells[row, column];
baseRange.Value2 = sourceRange.Value2;
}
}
}
//baseSheet.Protect(string.Empty,
// MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
// MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
sourceBook.Close(false, MISSING, MISSING);
baseBook.SaveCopyAs(targetFile);
baseBook.Close(false, MISSING, MISSING);
}
finally
{
Office.ExcelHelper.Kill(excel);
}
Thread.Sleep(100);
}
/// <summary>
/// BC列不合并时处理数据 ，并处理一合并时清除的一些线
/// </summary>
/// <param name="xlsSheet"></param>
/// <param name="MergeRowIndex">合并的行数</param>
/// <param name="valueUp">值上限</param>
/// <param name="valueDown">值下限</param>
/// <param name="standardValue">规格值</param>
private void SetBCValueToXlsCell(Excel.Worksheet xlsSheet, int startRowIndex, string valueUp, string valueDown, string standardValue)
{
InsertStandardValue(xlsSheet, startRowIndex, standardValue, "B");
InserUpDownValue(xlsSheet, startRowIndex, valueUp, valueDown, "C");
int StopRowIndex = startRowIndex + 1;
xlsSheet.get_Range("B" + startRowIndex, "C" + StopRowIndex).Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
xlsSheet.get_Range("B" + startRowIndex, "C" + StopRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsSheet.get_Range("B" + startRowIndex, "C" + StopRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsSheet.get_Range("A" + startRowIndex, "A" + StopRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsSheet.get_Range("A" + startRowIndex, "A" + StopRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsSheet.get_Range("F" + startRowIndex, "H" + StopRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
xlsSheet.get_Range("F" + startRowIndex, "H" + StopRowIndex).Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
}
/// <summary>
/// 创建一个Excel示例
/// </summary>
public void CreateExcel2()
{
Excel.Application excel = new Excel.Application(); //引用Excel对象
Excel.Workbook book = excel.Application.Workbooks.Add(System.Reflection.Missing.Value); //引用
Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets["Sheet1"]; //选中
sheet.Cells[1, 3] = "Mytest"; //左上角第一格的坐标是[1,1]
Excel.Worksheet sheet2 = (Excel.Worksheet)book.Worksheets["Sheet2"]; //选中
sheet2.Cells[1, 3] = "Mytestdddd"; //左上角第一格的坐标是[1,1]
book.Worksheets.Add(After: sheet2, Count: 5);
excel.Visible = true;//使Excel可视
//保存
switch (ExistsRegedit())
{
case 1:
{
excel.ActiveWorkbook.SaveAs("D:\\test.xls", XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
break;
}
case 2:
{
excel.ActiveWorkbook.SaveAs("D:\\test.xls", Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
break;
}
default:
throw new Exception("您的电脑需要安装Office2003 或 Office2007才能导出Excel文件。");
}
//打开显示
excel.Visible = true;
// excel.Quit();
// excel=null;
// GC.Collect();//垃圾回收
}
public void CopyWorksheet(int SourceWorksheetIndex, int DesWorksheetIndex)
{
try
{
Excel.Worksheet sheetSource = (Excel.Worksheet)xlsWb.Worksheets[SourceWorksheetIndex];
sheetSource.Select(Missing.Value);
Excel.Worksheet sheetDest = (Excel.Worksheet)xlsWb.Worksheets[DesWorksheetIndex];
sheetSource.Copy(Missing.Value, sheetDest);
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
/// <summary>
/// 设置活动工作表
/// </summary>
/// <param name="SheetIndex">要设置为活动工作表的索引值</param>
public void SetActiveWorkSheet(int SheetIndex)
{
if (SheetIndex <= 0)
{
throw new Exception("索引超出范围！");
}
try
{
ActiveSheetIndex = SheetIndex;
xlsWs = (Excel.Worksheet)xlsWb.Worksheets[ActiveSheetIndex];
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
private Excel.Worksheet AddNewSheet(string workSheetName)
{
Excel.Worksheet xlSheet = null;
try
{
object obj = workbook.Worksheets.Add(Type.Missing, workbook.ActiveSheet, Type.Missing, Type.Missing);
xlSheet = obj as Excel.Worksheet;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
if (xlSheet != null)
{
xlSheet.Name = workSheetName;
}
return(xlSheet);
}
public void Export2DatasetDSPhongThi(System.Data.DataSet i_DataSet, string i_TableName, int i_iSheetStartRow)
{
try
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
m_objExcelApp = new Excel.Application();
m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
m_objExcelApp.Workbooks[1].Worksheets.Select(1);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]);
int i_iExcelRow = 0;
bool v_bol_stop = false;
while (!v_bol_stop)
{
int i_iExcelCol = 0;
System.Data.DataRow v_iDataRow = default(System.Data.DataRow);
v_iDataRow = i_DataSet.Tables[i_DataSet.Tables[i_TableName].TableName].NewRow();
v_iDataRow[i_iExcelCol] = i_iExcelCol + 1;
for (i_iExcelCol = 0; i_iExcelCol <= i_DataSet.Tables[i_TableName].Columns.Count - 2; i_iExcelCol++)
{
if (!object.ReferenceEquals(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, 4]/*.Value()*/, null))
{
if (!(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/ == null))
{
v_iDataRow[i_iExcelCol + 1] = m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/;
}
}
else
{
v_bol_stop = true;
}
}
if (!v_bol_stop)
{
i_DataSet.Tables[i_TableName].Rows.InsertAt(v_iDataRow, i_iExcelRow);
i_iExcelRow++;
}
}
m_objExcelApp.DisplayAlerts = false;
m_objExcelApp.Workbooks.Close();
m_objExcelApp.DisplayAlerts = true;
m_objExcelApp.Quit();
Unmount();
}
catch (Exception v_e)
{
m_objExcelApp.DisplayAlerts = false;
m_objExcelApp.Workbooks.Close();
m_objExcelApp.DisplayAlerts = true;
m_objExcelApp.Quit();
Unmount();
throw (v_e);
}
}
public void ChangeSheetExported(int i_sheet_index)
{
m_objExcelApp.Workbooks[1].Worksheets.Select(i_sheet_index);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[i_sheet_index]);
}
private void Unmount()
{
m_objExcelWorksheet = null;
m_objExcelApp = null;
}
private void init_excel()
{
string v_strFileName = "";
v_strFileName = GetOutputFileNameWithPath();
if (!CopyFileSuccess(v_strFileName))
{
return;
}
m_objExcelApp.Workbooks.Open(v_strFileName, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
m_objExcelApp.Workbooks[1].Worksheets.Select(1);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]);
m_init_successful = true;
}
public int GetCountRow()
{
try
{
m_objExcelApp = new Excel.Application();
System.Globalization.CultureInfo oldCI =
System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture =
new System.Globalization.CultureInfo("en-US");
m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
m_objExcelApp.Workbooks[1].Worksheets.Select(1);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]);
return m_objExcelWorksheet.UsedRange.Rows.Count;
}
catch (Exception v_e)
{
throw (v_e);
}
}
public void Export2Grid(C1FlexGrid i_fg, int i_iSheetStartRow, int i_iSheetCol, int i_iGridCol)
{
try
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
m_objExcelApp = new Excel.Application();
m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
m_objExcelApp.Workbooks[1].Worksheets.Select(1);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]);
int v_iGridRow = 0;
for (v_iGridRow = i_fg.Rows.Fixed; v_iGridRow <= i_fg.Rows.Count - 1; v_iGridRow++)
{
i_fg[v_iGridRow, i_iGridCol] = m_objExcelWorksheet.Cells[i_iSheetStartRow + v_iGridRow - i_fg.Rows.Fixed, i_iSheetCol];//.Value
}
m_objExcelApp.Workbooks.Close();
m_objExcelApp.Quit();
Unmount();
}
catch (Exception v_e)
{
m_objExcelApp.Workbooks.Close();
m_objExcelApp.Quit();
Unmount();
throw (v_e);
}
}
