public void CreateExcelFile()
{
app = new Microsoft.Office.Interop.Excel.Application();
workbook = app.Workbooks.Add(1);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
AddExcelProcessTolist();
}
public ExcelWriter(string _fileName)
{
fileName = _fileName;
if(File.Exists(FilePath))
throw new ApplicationException("File already exists: " + FilePath);
File.Create(FilePath);
app = new Microsoft.Office.Interop.Excel.Application();
Console.Error.WriteLine("Connected to Excel");
wbs = app.Workbooks;
wb = wbs.Add(1);
wb.Activate();
wss = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1);
Console.Error.WriteLine("Excel Worksheet Initialized");
}
public ExcelFile(string filePath)
{
_application = new Microsoft.Office.Interop.Excel.Application();
_workbook = _application.Workbooks.Open(filePath);
_worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets.Item[1];
_filename = filePath;
_currentSheetNumber = 1;
}
public string GetValue(string filename, int row, int col)
{
string fileaddress = Constants.BaseDirectory + filename;
//app.Workbooks.Open(fileaddress, 0, false, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, "", "", false,
// Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, false, false, false);
workbook = app.Workbooks.Open(fileaddress, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
string result = (string)worksheet.Columns[row, col];
return result;
}
public void Create()
{
excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
workBook = excelApplication.Workbooks.Add();
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
workSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
createHeader();
}
public void createDoc()
{
try
{
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
workbook = app.Workbooks.Add(1);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
}
catch (Exception e)
{
Console.Write("Error : " + e.Message);
}
finally
{
}
}
public bool CreateExcel(List<Po> pos)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (Po po in pos)
{
xlWorkSheet.Cells[po.Row, po.Column] = po.Value;
}
xlWorkBook.SaveAs("C:\\newcat2", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
int test = 0;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
GC.WaitForPendingFinalizers();
return true;
}
public void NextSheet()
{
_currentSheetNumber++;
_worksheet = _workbook.Worksheets[_currentSheetNumber];
}
static public void DataGridView2Excel(DataGridView dv, string sheetName, ToolStripProgressBar pb)
{
//建立Excel对象
Microsoft.Office.Interop.Excel.Application objExcel = null;
Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(System.Reflection.Missing.Value);
objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
if (sheetName != "")
{
objsheet.Name = sheetName;
}
}
catch
{
MessageBox.Show("您的机器上没有安装Excel，请安装后再使用此功能。");
return;
}
int rowscount = dv.Rows.Count;
int colscount = dv.Columns.Count;
int displayColumnsCount = 1;
for (int i = 0; i <= dv.ColumnCount - 1; i++)
{
if (dv.Columns[i].Visible == true)
{
objExcel.Cells[1, displayColumnsCount] = dv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
if (pb != null)
{
pb.Minimum = 0;
pb.Maximum = dv.RowCount;
}
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dv.RowCount - 1; row++)
{
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dv.Columns[col].Visible == true)
{
try
{
string s = dv.Rows[row].Cells[col].Value.ToString().Trim();
//非数字设置为文本格式
if (!IsNum(s))
{
objExcel.Cells[row + 2, displayColumnsCount].NumberFormatLocal = "@";
}
objExcel.Cells[row + 2, displayColumnsCount] = s;
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
if (pb != null)
{
pb.Value = row + 1;
}
}
objExcel.Visible = true;
}
private void bomoToexcel()
{
DataTable _px = new DataTable();
var query = new BmobQuery();
query.WhereGreaterThan("indexID", 2651); //分数大于60岁
query.OrderBy("indexID");
query.Limit(600);
var childfuture = Bmob.FindTaskAsync <computerTiKu>("computerTiKu", query);
if (childfuture.Result.results.Count != 0)
{
_px = ToDataTable <computerTiKu>(childfuture.Result.results);
// MessageBox.Show("总行数" + _px.Rows.Count);
}
//_dspx = AdDispatchManage.GetValue(sqlstr);
int row;
row = 2;
int b;
b = 0;
string PathStr;
string SourceFileName;
string DestinationFileName;
PathStr = Application.StartupPath.Trim();
// MakeBigWord.MakeBigWord.KillExcelProcess();
DestinationFileName = PathStr + @"\plantmp.xls";
SourceFileName = PathStr + @"\yhygzpx.xls";
try
{
File.Delete(DestinationFileName);
File.Copy(SourceFileName, DestinationFileName);
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(DestinationFileName);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = xlBook.Worksheets[1];
xlSheet.Activate();
xlSheet.Application.Visible = true;
try
{
for (int i = 0; i < _px.Rows.Count; i++)
{
if (_px.Rows[i]["titleSubject"].ToString() != null)
{
xlSheet.Cells[i + row, 1] = _px.Rows[i]["titleSubject"].ToString();
}
else
{
MessageBox.Show(i + "titleSubject");
}
if (_px.Rows[i]["titleSubject"].ToString() != null)
{
xlSheet.Cells[i + row, 1] = _px.Rows[i]["titleSubject"].ToString();
}
else
{
MessageBox.Show(i + "titleSubject");
}
if (_px.Rows[i]["optionA"].ToString() != null && _px.Rows[i]["optionB"].ToString() != null && _px.Rows[i]["optionC"].ToString() != null && _px.Rows[i]["optionD"].ToString() != null)
{
Choice[] student = new Choice[4];
student[0] = new Choice();
student[0].key = "A";
student[0].content = _px.Rows[i]["optionA"].ToString();
student[1] = new Choice();
student[1].key = "B";
student[1].content = _px.Rows[i]["optionB"].ToString();
student[2] = new Choice();
student[2].key = "C";
student[2].content = _px.Rows[i]["optionC"].ToString();
student[3] = new Choice();
student[3].key = "D";
student[3].content = _px.Rows[i]["optionD"].ToString();
string aa = Newtonsoft.Json.JsonConvert.SerializeObject(student);
xlSheet.Cells[i + row, 2] = aa;
//设置一个Person类
//Choice p = new Choice();
//p.key = _px.Rows[i]["optionA"].ToString();
//p.content = _px.Rows[i]["optionB"].ToString();
//p.C = _px.Rows[i]["optionC"].ToString();
//p.D = _px.Rows[i]["optionD"].ToString();
//string json1 = JsonConvert.SerializeObject(p);
//Console.WriteLine(json1 + "\n");
////缩进输出
//string json2 = JsonConvert.SerializeObject(p, Formatting.Indented);
//Console.WriteLine(json2 + "\n");
// {"A":"+价格发现功能+","B":"+套利功能+","C":"+投机功能+","D":"+套期保值功能"}
}
else
{
MessageBox.Show(i + "A=" + _px.Rows[i]["optionA"].ToString() + "B=" + _px.Rows[i]["optionB"].ToString() + "C=" + _px.Rows[i]["optionC"].ToString() + "D=" + _px.Rows[i]["optionD"].ToString());
}
xlSheet.Cells[i + row, 3] = "3RcsJ77J";
if (_px.Rows[i]["answer"].ToString() != null)
{
String title = _px.Rows[i]["answer"].ToString().Substring(4, _px.Rows[i]["answer"].ToString().Length - 4); ///去掉两个字符串
xlSheet.Cells[i + row, 4] = "[\"" + title + "\"]";
}
else
{
MessageBox.Show(i + "answer");
}
if (_px.Rows[i]["explain"].ToString() != null)
{
xlSheet.Cells[i + row, 5] = _px.Rows[i]["explain"].ToString();
}
else
{
MessageBox.Show(i + "explain");
}
xlSheet.Cells[i + row, 6] = 500 + i;
xlSheet.Cells[i + row, 7] = "P0Vm666E";
b = i;
//xlSheet.Cells[i + row, 2] = _px.Rows[i]["indexID"].ToString();
//xlSheet.Cells[i + row, 3] = _px.Rows[i]["titleSubject"].ToString();
//xlSheet.Cells[i + row, 4] = _px.Rows[i]["answer"].ToString();
//xlSheet.Cells[i + row, 5] = _px.Rows[i]["optionA"].ToString();
//xlSheet.Cells[i + row, 6] = _px.Rows[i]["chapterflag"].ToString();
//xlSheet.Cells[i + row, 7] = _px.Rows[i]["core"].ToString();
//xlSheet.Cells[i + row, 8] = _px.Rows[i]["answerCode"].ToString();
//xlSheet.Cells[i + row, 9] = "'" + _px.Rows[i]["optionB"].ToString() + "/" + _px.Rows[i]["optionC"].ToString();
//xlSheet.Cells[i + row, 10] = _px.Rows[i]["optionD"].ToString();
//xlSheet.Cells[i + row, 11] = _px.Rows[i]["questiontype"].ToString();
//xlSheet.Cells[i + row, 12] = _px.Rows[i]["subjectType"].ToString() + "/" + _px.Rows[i]["explain"].ToString();
}
}
catch
{ }
try
{
Microsoft.Office.Interop.Excel.Range r1 = xlSheet.Range[xlSheet.Cells[3, 1], xlSheet.Cells[b + row + 4, 6]];
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = 7;
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = 7;
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = 7;
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = 7;
r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = 7;
}
catch
{ }
}
public void export(DataGrid dta)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet worksheet = new Microsoft.Office.Interop.Excel.Worksheet();
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)app.ActiveSheet;
try
{
// Put Column Header into excel work sheet
for (int i = 0; i < dta.Columns.Count; i++)
{
worksheet.Range["A1"].Offset[0, i].Value = dta.Columns[i].Header;
}
Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)worksheet.Rows[1];
firstRow.Cells.Interior.ColorIndex = 36;
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;
firstRow.EntireRow.Font.Bold = true;
BorderAround(firstRow, 0);
for (int rowIndex = 0; rowIndex < dta.Items.Count; rowIndex++)
{
for (int columnIndex = 0; columnIndex < dta.Columns.Count; columnIndex++)
{
worksheet.Range["A2"].Offset[rowIndex, columnIndex].Value =
dtaToString((dta.Items[rowIndex] as DataRowView).Row.ItemArray[columnIndex]);
}
}
worksheet.Columns.AutoFit();
app.Visible = true;
}
catch (Exception)
{
Console.Write("Error");
}
}
/// <summary>
/// 得到工作表
/// </summary>
/// <param name="worksheetName"></param>
/// <returns></returns>
public void GetWorkSheet(string worksheetName)
{
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(worksheetName);
}
public static void WriteDoc()
{
var activeWriteRowNum = 4;
App = new Microsoft.Office.Interop.Excel.Application {Visible = true};
Workbook = App.Workbooks.Open("DragonLegendSalesReport-AutoGen", Type.Missing, false, Type.Missing, "19931993", Type.Missing,true);
Worksheet = (Microsoft.Office.Interop.Excel.Worksheet) Workbook.Sheets[1];
var temp = "blah";
const string format = "ddd MMM d, yyyy";
var time = DateTime.Now;
var hasTodayInfo = true;
while (temp != time.ToString(format))
{
var range = Worksheet.Range["B" + activeWriteRowNum, "M" + activeWriteRowNum];
var myvalues = (Array) range.Cells.Value;
var strArray = myvalues.OfType<object>().Select(x => x.ToString()).ToArray();
if (strArray.Length == 0)
{
hasTodayInfo = false;
break;
}
temp = strArray[0];
activeWriteRowNum++;
}
if (hasTodayInfo)
activeWriteRowNum--;
//creates the main header
CreateHeaders(2, 2, "Sales Report", "B2", "M2", 2, "YELLOW", true, 10, "n");
//creates subheaders
CreateHeaders(3, 2, "Date", "B3", "C3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 4, "TIC:0|2", "D3", "D3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 5, "CUS:0|2", "E3", "E3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 6, "TIC:9|15", "F3", "F3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 7, "CUS:9|15", "G3", "G3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 8, "TIC:17|21", "H3", "H3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 9, "CUS:17|21", "I3", "I3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 10, "TIC:21|24", "J3", "J3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 11, "CUS:21|24", "K3", "K3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 12, "TIC TOTAL", "L3", "L3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 13, "CUS TOTAL", "M3", "M3", 0, "GAINSBORO", true, 10, "");
//add Data to cells
CreateHeaders(activeWriteRowNum, 2, "", "B" + activeWriteRowNum, "C" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 2, time.ToString(format), "B" + activeWriteRowNum, "C" + activeWriteRowNum, "");
CreateHeaders(activeWriteRowNum, 4, "", "D" + activeWriteRowNum, "D" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 4, ReceptionScreen.TotalTicketSold[0].ToString(), "D" + activeWriteRowNum,
"D" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 5, "", "E" + activeWriteRowNum, "E" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 5, ReceptionScreen.TotalCutomers[0].ToString(), "E" + activeWriteRowNum,
"E" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 6, "", "F" + activeWriteRowNum, "F" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 6, ReceptionScreen.TotalTicketSold[1].ToString(), "F" + activeWriteRowNum,
"F" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 7, "", "G" + activeWriteRowNum, "G" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 7, ReceptionScreen.TotalCutomers[1].ToString(), "G" + activeWriteRowNum,
"G" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 8, "", "H" + activeWriteRowNum, "H" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 8, ReceptionScreen.TotalTicketSold[2].ToString(), "H" + activeWriteRowNum,
"H" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 9, "", "I" + activeWriteRowNum, "I" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 9, ReceptionScreen.TotalCutomers[2].ToString(), "I" + activeWriteRowNum,
"I" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 10, "", "J" + activeWriteRowNum, "J" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 10, ReceptionScreen.TotalTicketSold[3].ToString(), "J" + activeWriteRowNum,
"J" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 11, "", "K" + activeWriteRowNum, "K" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
AddData(activeWriteRowNum, 11, ReceptionScreen.TotalCutomers[3].ToString(), "K" + activeWriteRowNum,
"K" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 12, "", "L" + activeWriteRowNum, "L" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
var total = ReceptionScreen.TotalTicketSold[0] + ReceptionScreen.TotalTicketSold[1] +
ReceptionScreen.TotalTicketSold[2] + ReceptionScreen.TotalTicketSold[3];
AddData(activeWriteRowNum, 12, total.ToString(), "L" + activeWriteRowNum, "L" + activeWriteRowNum, "#,##0");
CreateHeaders(activeWriteRowNum, 13, "", "M" + activeWriteRowNum, "M" + activeWriteRowNum, 2, "WHITE", false,
10, "n");
total = ReceptionScreen.TotalCutomers[0] + ReceptionScreen.TotalCutomers[1] +
ReceptionScreen.TotalCutomers[2] + ReceptionScreen.TotalCutomers[3];
AddData(activeWriteRowNum, 13, total.ToString(), "M" + activeWriteRowNum, "M" + activeWriteRowNum, "#,##0");
App.DisplayAlerts = false;
Workbook.SaveAs("DragonLegendSalesReport-AutoGen", Type.Missing, "19931993", Type.Missing, true);
App.Quit();
}
public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)
//合并单元格
{
ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
}
private void fillDocumentExcel1(string _filename)
{
Object wMissing = System.Reflection.Missing.Value;
Object wTrue = true;
Object wFalse = false;
string[] text = inputAppFullName.Text.Split(';');
string d = text[0];
Microsoft.Office.Interop.Excel.Application appExcel1 = new Microsoft.Office.Interop.Excel.Application();
// Microsoft.Office.Interop.Excel.Workbook bookExcel = new Microsoft.Office.Interop.Excel.Workbook();
Microsoft.Office.Interop.Excel.Worksheet worksheetExcel1 = new Microsoft.Office.Interop.Excel.Worksheet();
Microsoft.Office.Interop.Excel.Sheets sheetsExcel1;
Microsoft.Office.Interop.Excel.Range rangeExcel1;
appExcel1.Visible = true;
//Работа с документом - Договор
appExcel1.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
sheetsExcel1 = appExcel1.Worksheets;
worksheetExcel1 = (Microsoft.Office.Interop.Excel.Worksheet)sheetsExcel1.get_Item(1);
rangeExcel1 = worksheetExcel1.get_Range("A12", "I12");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
rangeExcel1.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
rangeExcel1.Value2 = "Счёт № " + inputNumContract.Text + " от " + dateAcc.Text;
rangeExcel1 = worksheetExcel1.get_Range("A14", "I14");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = "Плательщик: " + inputAppNameOrgOrPF.Text;
rangeExcel1 = worksheetExcel1.get_Range("A15:A16", "I15:I16");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = "Грузополучатель: " + JFCargoCarrier;
rangeExcel1 = worksheetExcel1.get_Range("B19", "E19");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = "Путёвка в санаторий " + inputAppWay.Text + ", " + inputNumDays.Text + "дн., \" " + inputAppNumberAndCategory.Text + " \" c " + inputAppStartTour.Text + " , " + d;
rangeExcel1 = worksheetExcel1.get_Range("H19", Type.Missing);
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = sumAcc.Text;
rangeExcel1 = worksheetExcel1.get_Range("I19", Type.Missing);
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = sumAcc.Text;
rangeExcel1 = worksheetExcel1.get_Range("I26", Type.Missing);
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = sumAcc.Text;
rangeExcel1 = worksheetExcel1.get_Range("E27", "I27");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = sumAcc.Text;
}
private void BindExcelDropDowns(Microsoft.Office.Interop.Excel.Application oXL, Microsoft.Office.Interop.Excel.Worksheet oSheet, int columnIndex, string flatList)
{
//Bind AccesLevel
try
{
for (int i = 1; i < 100; i++)
{
var cell = oSheet.Cells[i, columnIndex];
cell.Validation.Delete();
cell.Validation.Add(
Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation,
Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween,
flatList,
Type.Missing);
cell.Validation.IgnoreBlank = true;
cell.Validation.InCellDropdown = true;
}
}
catch (Exception)
{
throw;
}
//Bind Supplier
}
/// <summary>
/// 导出Excel 的方法
/// </summary>
private void tslExport_Click(string fileName, DataGridView myDGV)
{
string saveFileName = "";
//bool fileSaved = false;
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
//写入标题
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]);
rang.NumberFormat = "000000000000";
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
// if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
}
public CreateDefaultExcel()
{
try
{
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
Microsoft.Office.Interop.Excel.Workbook oWB = oXL.Workbooks.Add(missing);
Microsoft.Office.Interop.Excel.Worksheet oSheet = oWB.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
oSheet.Cells[1, 1] = "Item code";
oSheet.Cells[1, 2] = "Item description";
oSheet.Cells[1, 3] = "Bar code";
oSheet.Cells[1, 4] = "Category";
oSheet.Cells[1, 5] = "Group";
oSheet.Cells[1, 6] = "Item type";
oSheet.Cells[1, 7] = "Supplier";
//oSheet.Cells[1, 9] = "Supplier code";
oSheet.Cells[1, 08] = "Quantity";
oSheet.Cells[1, 09] = "Access level";
oSheet.Cells[1, 10] = "MOQ";
oSheet.Name = "ExcelItemDetails";
//if we want to add For Application Root directry we can add
//Microsoft.Office.Interop.Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
// as Microsoft.Office.Interop.Excel.Worksheet;
//oSheet2.Name = "Sheet2";
//oSheet2.Cells[1, 1] = "Something completely different";
//string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
// + "\\Sample.xlsx";
string fileName = null;
SaveFileDialog savefile = new SaveFileDialog();
savefile.FileName = "Default.xlsx";
if (savefile.ShowDialog() == DialogResult.OK)
{
// Bind Access Level
var accesLevelList = new System.Collections.Generic.List <string>();
accesLevelList.Add("1");
accesLevelList.Add("2");
accesLevelList.Add("3");
var flatAccesLevelList = string.Join(",", accesLevelList.ToArray());
var oMissing = Type.Missing;
columnIndex = 09;
oXL.Visible = false;
BindExcelDropDowns(oXL, oSheet, columnIndex, flatAccesLevelList);
// Bind Supplier
DataTable dtSupplier = objBal.BindSupplier();
var supplierlList = new System.Collections.Generic.List <string>();
if (dtSupplier.Rows.Count > 0)
{
for (int i = 0; i < dtSupplier.Rows.Count; i++)
{
supplierlList.Add(dtSupplier.Rows[i]["Company"].ToString());
}
}
var flaSupplierlList = string.Join(",", supplierlList.ToArray());
var oMissingsupplierlList = Type.Missing;
columnIndex = 07;
oXL.Visible = false;
BindExcelDropDowns(oXL, oSheet, columnIndex, flaSupplierlList);
fileName = savefile.FileName;
oWB.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, missing, missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
MessageBox.Show("Data saved in Excel format at location " + fileName, "Successfully Saved", MessageBoxButtons.OK, MessageBoxIcon.Question);
oWB.Close(missing, missing, missing);
oXL.UserControl = true;
oXL.Quit();
}
}
catch (Exception ex)
{
throw ex;
}
}
private void btnBackUP_Click(object sender, EventArgs e)
{
if (this.cbxSelectKQuan.SelectedItem == null)
{
MessageBox.Show("请选择矿权！");
return;
}
if (this.cbxUpdateKQuan.SelectedItem == null || this.cbxKQu.SelectedItem == null || this.dateTimeInput1 == null ||
this.txtUpdateReason == null || this.txtOperator == null || this.txtManager == null || this.txtComment == null)
{
MessageBox.Show("请完善记录信息！");
return;
}
string[] TableName = { "JGAB301_核查矿区", "JGAB302_原上表矿区", "JGAB303_勘查工作区", "JGAB304_采矿权", "JGAB305_探矿权",
"JGAB306_矿体", "JGAB307_采空区", "JGAB308_核查块段", "JGAB309_核查块段储量", "JGAB310_原块段", "JGAB311_原块段储量",
"JGAB312_块段对照表", "JGAB313_资料目录", "JGAB314_附件目录", "JGAB315_专题图件", "JGAB316_专题图件图层",
"JGAB317_煤质特征", "JGAB318_储量利用", "JGAB319_大块段对照表", "JGAB320_合并原块段", "JGAB321_采矿权三率" };
string[] tableField = { "TZYSBH", "TYBH", "HCKQBH", "CKZBH", "CKQR", "CKQFW", "DZ", "KSBH", "KSMC", "FZJG", "YXQQ", "YXQZ", "XKCSS", "XKCSX", "KCZKZ", "ZKZMC", "ZYJSL", "ZYKSL", "BYJSL", "BYKSL", "DKSNL", "DJSNL", "SKSNL", "SJSNL", "NDKSL", "NDJSL", "KCFSM", "KCFS", "XKFSM", "XKFS", "RXKSL", "KQBH", "JJLXM", "JJLX", "CYRY", "NCZ", "JSSCCB", "KCPLX", "CXSBSL" };
// 文件保存路径及名称
// 创建Excel文档
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet sheet = null;
string sheetName = "";
//删除自己生成的两个sheet
for (int i = 2; i < 4; i++)
{
sheetName = "Sheet" + i;
sheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Sheets.get_Item(sheetName);
sheet.Delete();
}
for (int i = 0; i < 1; i++)
{
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Name = "更新日志表";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 1] = "更新矿权";//也可以这样赋值
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 2] = "所属矿区";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 3] = "更新时间";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 4] = "更新原因";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 5] = "负责人";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 6] = "操作员";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 7] = "备注";
//合并 单元格 设置表头
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "A2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "A2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("B1", "B2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("B1", "B2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("C1", "C2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("C1", "C2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("D1", "D2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("D1", "D2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("E1", "E2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("E1", "E2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("F1", "F2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("F1", "F2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("G1", "G2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("G1", "G2").MergeCells);
//得到 Range 范围 域对象
Microsoft.Office.Interop.Excel.Range range = ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "G69");
//设置 该range内的 样式 颜色 边框
////设置Excel表格的 列宽
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "A69").ColumnWidth = 20;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("B1", "B69").ColumnWidth = 20;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("C1", "C69").ColumnWidth = 30;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("D1", "D69").ColumnWidth = 20;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("E1", "E69").ColumnWidth = 20;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("F1", "F69").ColumnWidth = 30;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("G1", "G69").ColumnWidth = 30;
//设置 域 Range 的颜色 从 A1到W1
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "G1").Interior.ColorIndex = 15;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A2", "G2").Interior.ColorIndex = 15;
//设置某个域range被选中
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A3", "G3").Select();
//左右 设置 选中域内的 Excel单元格从C 到W 是活动的 前面的A B 为固定的
//但是 上下 方向 表头（这里表头合并两行 ）没有固定 选C3 到W3（表示从C的第三行开始 为 活动 的 上面两行为固定的）
ExcelApp.ActiveWindow.FreezePanes = true;
//设置 某个域range内 单元格里的字体颜色
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "G2").Font.Color = -16744448;//（搜索Excel颜色对照表）
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A3", "G24").Font.Color = -16776961;
//文字 居中
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.Font.Size = 10;
range.Borders.LineStyle = 1;
//设置边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
range.WrapText = true;
//赋值 就
for (int j = 0; j < 1; j++)
{
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 1] = this.cbxSelectKQuan.SelectedItem.ToString();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 2] = this.cbxKQu.SelectedItem.ToString();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 3] = this.dateTimeInput1.Text.ToString();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 4] = this.txtUpdateReason.Text;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 5] = this.txtManager.Text;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 6] = this.txtOperator.Text;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 7] = this.txtComment.Text;
}
}
ExcelApp.DisplayAlerts = true;
object missing = System.Reflection.Missing.Value;
// 文件保存
string excelPath = historyPath + "\\矿权-" + this.cbxSelectKQuan.SelectedItem.ToString() + "-" + DateTime.Today.Year.ToString() + "年" + DateTime.Today.Month.ToString() + "月.xls";
ExcelBook.SaveAs(excelPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
ExcelBook.Close(Type.Missing, excelPath, Type.Missing);
ExcelApp.Quit();
string P_str_Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + access_Path + ";Persist Security Info=False";
OleDbConnection oledbcon = new OleDbConnection(P_str_Con);//实例化OLEDB连接对象
//使用事务保持数据的一致性与完整性
string P_str_Sql_04 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB304_采矿权] from JGAB304_采矿权 where CKZBH = '"
+ this.cbxSelectKQuan.SelectedItem.ToString() + "'"; //记录连接Excel的语句
string P_str_Sql_21 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB321_采矿权三率] from JGAB321_采矿权三率 where CKZBH = '"
+ this.cbxSelectKQuan.SelectedItem.ToString() + "'"; //记录连接Excel的语句
string P_str_Sql_08 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB308_核查块段] from JGAB308_核查块段 where CKZBH = '"
+ this.cbxSelectKQuan.SelectedItem.ToString() + "'"; //记录连接Excel的语句
string P_str_Sql_06 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB306_矿体] from JGAB306_矿体 where (KTBH IN (select DISTINCT KTBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_09 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB309_核查块段储量] from JGAB309_核查块段储量 where (TYBH IN (select DISTINCT TYBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_12 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB312_块段对照表] from JGAB312_块段对照表 where (HCTYBH IN (select DISTINCT TYBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_10 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB310_原块段] from JGAB310_原块段 where (KTBH IN (select DISTINCT KTBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_11 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB311_原块段储量] from JGAB311_原块段储量 where (KDBH IN (select DISTINCT YKDBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_18 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB318_储量利用] from JGAB318_储量利用 where (TYBH IN (select DISTINCT CLLYTYBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_17 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB317_煤质特征] from JGAB317_煤质特征 where (TYBH IN (select DISTINCT MCBH from JGAB306_矿体 where (KTBH IN (select DISTINCT KTBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))))";
string P_str_Sql_19 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB319_大块段对照表] from JGAB319_大块段对照表 where (YKDBH IN (select DISTINCT YKDBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_20 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB320_合并原块段] from JGAB320_合并原块段 where (HBTYBH IN (select DISTINCT YTYBH from JGAB319_大块段对照表 where (YKDBH IN (select DISTINCT YKDBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))))";
string P_str_Sql_01 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB301_核查矿区] from JGAB301_核查矿区 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_02 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB302_原上表矿区] from JGAB302_原上表矿区 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_03 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB303_勘查工作区] from JGAB303_勘查工作区 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_05 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB305_探矿权] from JGAB305_探矿权 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_07 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB307_采空区] from JGAB307_采空区 where CKQBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'";
string P_str_Sql_13 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB313_资料目录] from JGAB313_资料目录 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_14 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB314_附件目录] from JGAB314_附件目录 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_15 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB315_专题图件] from JGAB315_专题图件 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_16 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB316_专题图件图层] from JGAB316_专题图件图层 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string[] SQLList = { P_str_Sql_04, P_str_Sql_21, P_str_Sql_08, P_str_Sql_06, P_str_Sql_09, P_str_Sql_12, P_str_Sql_10, P_str_Sql_11
, P_str_Sql_18, P_str_Sql_17, P_str_Sql_19, P_str_Sql_20, P_str_Sql_01, P_str_Sql_02, P_str_Sql_03,P_str_Sql_05, P_str_Sql_07, P_str_Sql_13, P_str_Sql_14
, P_str_Sql_15, P_str_Sql_16 };
oledbcon.Open();//打开数据库连接
OleDbCommand oledbcom = new OleDbCommand();
oledbcom.Connection = oledbcon;
oledbcom.Transaction = oledbcon.BeginTransaction();//开始事务
try
{
for (int i = 0; i < SQLList.Length; i++)
{
string strsql = SQLList[i].ToString();
if (strsql.Trim().Length > 1)
{
oledbcom.CommandText = strsql;
oledbcom.ExecuteNonQuery();
}
//SetTextMessage(i * 100 / SQLList.Length);
}
oledbcom.Transaction.Commit();
MessageBox.Show("操作成功！");
}
catch (Exception ex)
{
MessageBox.Show("操作遇到问题，已撤销所做操作！");
oledbcom.Transaction.Rollback();//回滚数据，保证数据的完整性
}
finally
{
oledbcon.Close(); //关闭数据库连接
oledbcon.Dispose(); //释放资源
}
}
private void btnExportExcel_Click(object sender, RoutedEventArgs e)
{
//Set Filter being displayed
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Title = "Export";
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
//If user presses OK
if (saveDialog.ShowDialog() == true)
{
//get excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//Add workbook to excel
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
//Store worksheet
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
try
{
//Get active worksheet
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)(workbook.ActiveSheet);
worksheet.Cells[1, 1] = "STT";
worksheet.Cells[1, 2] = "Mã món ăn";
worksheet.Cells[1, 3] = "Tên món ăn";
worksheet.Cells[1, 4] = "Số lượng";
worksheet.Cells[1, 5] = "Đơn giá";
worksheet.Cells[1, 6] = "Thành tiền";
//Set the value for remaining row of worksheet by the value being presented in GridView
for (int iRow = 0; iRow < dataGridReport.Items.Count; iRow++)
{
//get current row
ReportDetailInfo reportDetailInfo = (ReportDetailInfo)dataGridReport.Items[iRow];
worksheet.Cells[iRow + 2, 1] = reportDetailInfo.STT;
worksheet.Cells[iRow + 2, 2] = reportDetailInfo.DishID;
worksheet.Cells[iRow + 2, 3] = reportDetailInfo.DishName;
worksheet.Cells[iRow + 2, 4] = reportDetailInfo.Quantity;
worksheet.Cells[iRow + 2, 5] = reportDetailInfo.UnitPrice;
worksheet.Cells[iRow + 2, 6] = reportDetailInfo.TotalPrice;
}
//Don't allow displaying alerts
//this is just a setup to prevent some kind of disturb things
excel.DisplayAlerts = false;
//Save Workbook
workbook.SaveAs(saveDialog.FileName);
excel.DisplayAlerts = true;
//Display successful dialog
MessageBox.Show("Xuất excel thành công", "Thành công", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Lỗi!!!", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
workbook.Close();
excel.Quit();
workbook = null;
excel = null;
GC.Collect();
}
}
}
void cbSheet_ValueMemberChanged(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(cbSheet.Text) && cbSheet.Text != "" && cbSheet.Text.Length > 1 && cbSheet.Items.Count > 1)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[cbSheet.Text];
GetResultSetForDGV();
}
}
public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)
//获取一个工作表
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName];
return(s);
}
private void DisableSheetOptions()
{
lblSheetFake.Visible = false;
lblSheet.Visible = true;
cbSheet.Text = "";
cbSheet.Enabled = false;
cbSheet.BackColor = System.Drawing.Color.FromName("Control");
EmptyCbSheetList();
ws = null;
if (dgvResultSetJob.Rows.Count > 0)
RemoveDataGridViewRows(dgvResultSetJob);
if (dgvResultSetOrderLines.Rows.Count > 0)
RemoveDataGridViewRows(dgvResultSetOrderLines);
dgvResultSetJob.Enabled = false;
dgvResultSetJob.Visible = false;
dgvResultSetOrderLines.Enabled = false;
dgvResultSetOrderLines.Visible = false;
Size sz = new Size(620, 10);
panel1.Visible = false;
panel1.Enabled = false;
btnRunUpdate.ForeColor = System.Drawing.Color.FromName("ControlDarkDark");
btnRunUpdate.Enabled = false;
Point pt = new Point(82, 142);
btnRunUpdate.Location = pt;
pt = new Point(332, 142);
btnExit.Location = pt;
}
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[OldSheetName];
s.Name = NewSheetName;
return(s);
}
private void fillDocumentExcel3(string _filename)
{
Object wMissing = System.Reflection.Missing.Value;
Object wTrue = true;
Object wFalse = false;
string[] text = inputAppFullName.Text.Split(';');
string d = text[0];
Microsoft.Office.Interop.Excel.Application appExcel1 = new Microsoft.Office.Interop.Excel.Application();
// Microsoft.Office.Interop.Excel.Workbook bookExcel = new Microsoft.Office.Interop.Excel.Workbook();
Microsoft.Office.Interop.Excel.Worksheet worksheetExcel1 = new Microsoft.Office.Interop.Excel.Worksheet();
Microsoft.Office.Interop.Excel.Sheets sheetsExcel1;
Microsoft.Office.Interop.Excel.Range rangeExcel1;
appExcel1.Visible = true;
//Работа с документом - Приходный ордер
appExcel1.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
sheetsExcel1 = appExcel1.Worksheets;
worksheetExcel1 = (Microsoft.Office.Interop.Excel.Worksheet)sheetsExcel1.get_Item(1);
rangeExcel1 = worksheetExcel1.get_Range("N10", "P10");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = inputNumContract.Text;
rangeExcel1 = worksheetExcel1.get_Range("Q10", "S10");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = DateDeb.Text;
rangeExcel1 = worksheetExcel1.get_Range("M18:M19", "P18:P19");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = sumDeb.Text + "руб. 00 коп.";
rangeExcel1 = worksheetExcel1.get_Range("D21", "S21");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = d;
}
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二
{
Sheet.Name = NewSheetName;
return(Sheet);
}
public void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
Data data = (Data) e.Argument; // Cast e argument to a local Data instance
// Start Excel/Multiframe calls - Either 11 or 20 stages, depending on debugMode (11 is debugMode, skips creating frame. Does debug info only)
bool debugMode = Properties.Settings.Default.DebugMode;
int progressTotal = debugMode == true ? 11 : 20;
int progressCount = 0;
bool unableToAddQ2Loads = false;
try
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Opening Excel"); progressCount++;
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = Properties.Settings.Default.DisplayExcel;
excelApp.DisplayAlerts = false;
wb = excelApp.Workbooks.Open(fileName);
ws = wb.Sheets.get_Item(1); // Expects "Input" sheet to be the first worksheet
// Insert values into Excel Inputs Sheet
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Inserting values into Input Sheet"); progressCount++;
ws.Range["windSpeedVu"].Value = data.ultimateWindSpeed;
ws.Range["windSpeedVs"].Value = data.serviceWindSpeed;
ws.Range["internalPressure"].Value = data.internalPressure;
ws.Range["shedType"].Value = data.roofType;
ws.Range["shedWallType"].Value = data.wallType;
ws.Range["shedEaveHeight"].Value = data.eaveHeight;
ws.Range["shedRoofPitch"].Value = data.roofPitch;
ws.Range["shedSpan"].Value = data.span;
ws.Range["shedBaySpacing"].Value = data.baySpacing;
ws.Range["shedNumberOfBays"].Value = data.numberOfBays;
ws.Range["shedSupports"].Value = data.supports;
ws.Range["shedEndColumnType"].Value = data.endColumnType;
ws.Range["shedEndColumnSection"].Value = data.endColumnSection;
ws.Range["shedEndRafterType"].Value = data.endRafterType;
ws.Range["shedEndRafterSection"].Value = data.endRafterSection;
ws.Range["shedMidColumnType"].Value = data.midColumnType;
ws.Range["shedMidColumnSection"].Value = data.midColumnSection;
ws.Range["shedMidRafterType"].Value = data.midRafterType;
ws.Range["shedMidRafterSection"].Value = data.midRafterSection;
ws.Range["shedMullions"].Value = data.numberOfMullions;
ws.Range["shedMullionsType"].Value = data.mullionType;
ws.Range["shedMullionsSection"].Value = data.mullionSection;
ws.Range["shedCompressionStrutType"].Value = data.strutType;
ws.Range["shedCompressionStrutSection"].Value = data.strutSection;
ws.Range["shedKneeBraceEnd"].Value = data.endKneeBraceType == "None" ? "no" : "yes";
if (data.endKneeBraceType != "None")
{
ws.Range["shedKneeBraceEndType"].Value = data.endKneeBraceType;
ws.Range["shedKneeBraceEndSection"].Value = data.endKneeBraceSection;
}
ws.Range["shedKneeBraceMid"].Value = data.midKneeBraceType == "None" ? "no" : "yes";
if (data.midKneeBraceType != "None")
{
ws.Range["shedKneeBraceMidType"].Value = data.midKneeBraceType;
ws.Range["shedKneeBraceMidSection"].Value = data.midKneeBraceSection;
}
ws.Range["shedKneeBracePercentEave"].Value = data.kneeBracePercentEave;
ws.Range["shedKneeBracePercentSpan"].Value = data.kneeBracePercentSpan;
ws.Range["shedApexBraceEnd"].Value = data.endApexBraceType == "None" ? "no" : "yes";
if (data.endApexBraceType != "None")
{
ws.Range["shedApexBraceEndType"].Value = data.endApexBraceType;
ws.Range["shedApexBraceEndSection"].Value = data.endApexBraceSection;
}
ws.Range["shedApexBraceMid"].Value = data.midApexBraceType == "None" ? "no" : "yes";
if (data.midApexBraceType != "None")
{
ws.Range["shedApexBraceMidType"].Value = data.midApexBraceType;
ws.Range["shedApexBraceMidSection"].Value = data.midApexBraceSection;
}
ws.Range["shedEavePurlinType"].Value = data.eavePurlinType;
ws.Range["shedEavePurlinSection"].Value = data.eavePurlinSection;
ws.Range["shedRoofPurlinType"].Value = data.strutType;
ws.Range["shedRoofPurlinSection"].Value = data.strutSection;
// Start calling macros from Excel Workbook
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting initial excel values"); progressCount++;
excelApp.Run("CsharpSetExcelInputValues");
if (debugMode == false)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting units in Multiframe"); progressCount++;
excelApp.Run("CsharpSetUnits");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Frame"); progressCount++;
excelApp.Run("CsharpCreateFrame");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Knee Braces"); progressCount++;
excelApp.Run("CsharpAddKneeBraces");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Apex Braces"); progressCount++;
excelApp.Run("CsharpAddApexBraces");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Mullions"); progressCount++;
excelApp.Run("CsharpAddMullions");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Eave Purlins"); progressCount++;
excelApp.Run("CsharpAddEavePurlins");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Restraints and Sections"); progressCount++;
excelApp.Run("CsharpAddRestraintsAndSections");
}
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Calculating Wind Pressures"); progressCount++;
excelApp.Run("CsharpGetWindPressures");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Calculating Longwind Reductions"); progressCount++;
excelApp.Run("CsharpGetLWPressures");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Copying Pressures to Loads Sheet"); progressCount++;
excelApp.Run("CsharpCopyPressures");
if (debugMode == false)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating New Load Cases"); progressCount++;
excelApp.Run("CsharpCreateNewLoadCases");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting up Loads"); progressCount++;
excelApp.Run("CsharpAddLoadsSetup");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Q2 Loads"); progressCount++;
unableToAddQ2Loads = (bool)excelApp.Run("CsharpAddQ2Loads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding End Portal Loads"); progressCount++;
excelApp.Run("CsharpAddEndPortalLoads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Mid Portal Loads"); progressCount++;
excelApp.Run("CsharpAddMidPortalLoads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding End Wall Loads"); progressCount++;
excelApp.Run("CsharpAddEndWallLoads");
}
if (debugMode == true)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Geometry Ratios"); progressCount++;
data.excelGeometryRatios = (double[]) excelApp.Run("CsharpGetGeometryRatiosDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Pressure Coefficients"); progressCount++;
data.excelPressureCoefficients = (double[]) excelApp.Run("CsharpGetPressureCoefficientsDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Load Cases"); progressCount++;
data.excelLoadCases = (string[])excelApp.Run("CsharpGetLoadCasesDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Loads"); progressCount++;
data.excelLoads = (string[,]) excelApp.Run("CsharpGetLoadsDebug");
}
// Alert if Q2 loads were unable to be added
if (unableToAddQ2Loads == true)
MessageBox.Show("Q2 loads were unable to be added. You will need to add them manually", "Unable to add Q2 Loads");
}
catch (Exception ex)
{
if (ex is ArgumentException || ex is ArgumentNullException)
MessageBox.Show("Error closing Excel (Marshal.ReleaseFinalComObject)\n\n" + ex.Message + "\n\n" + ex.StackTrace, "Error Closing Excel");
else
MessageBox.Show("Error closing Excel\n\n" + ex.Message + "\n\n" + ex.StackTrace, "Error Closing Excel");
}
finally
{
// Close Excel
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Closing Excel"); progressCount++;
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(ws);
wb.Close();
Marshal.FinalReleaseComObject(wb);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
}
}
public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)
//ws：要设值的工作表 X行Y列 value 值
{
ws.Cells[x, y] = value;
}
void CreateExcelDocument()
{
System.Windows.Input.Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlWorkBooks.Add(1);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
try
{
xlWorkSheet.Name = (rm as ResourceManager).GetString("LB_Expenses");
xlWorkSheet.Cells[1, 1] = exitems[CB_Offer.SelectedIndex].Venue.Trim() + " - " + exitems[CB_Offer.SelectedIndex].Address.Trim();
Microsoft.Office.Interop.Excel.Range formatRange = xlWorkSheet.get_Range("A1", "D1");
formatRange.Font.Bold = true;
formatRange.WrapText = true;
xlWorkSheet.Range["a1", "D1"].Merge();
string rang = "A1:" + "D" + (expList.Count + 3);
xlWorkSheet.get_Range(rang).Cells.Font.Name = "Comic Sans MS";
xlWorkSheet.Range[rang].Font.Size = 16;
xlWorkSheet.Range[rang].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);
xlWorkSheet.Range[rang].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
xlWorkSheet.Range[rang].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
formatRange = xlWorkSheet.get_Range(rang);
formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.Cells[2, 1] = LB_Expense.Content;
xlWorkSheet.Cells[2, 2] = LB_Cost.Content;
xlWorkSheet.Cells[2, 3] = LB_Count.Content;
xlWorkSheet.Cells[2, 4] = (rm as ResourceManager).GetString("LB_Amount");
int sum = 0;
for (int i = 0; i < expList.Count; i++)
{
xlWorkSheet.Cells[3 + i, 1] = expList[i].ExpenseName.Trim();;
xlWorkSheet.Cells[3 + i, 2] = f.StringCurrencyFormat(expList[i].Expense.ToString());
xlWorkSheet.Cells[3 + i, 3] = f.StringCurrencyFormat(expList[i].Count.ToString());
xlWorkSheet.Cells[3 + i, 4] = f.StringCurrencyFormat((expList[i].Expense * expList[i].Count).ToString());
xlWorkSheet.Cells[3 + i, 2].NumberFormat = "0";
xlWorkSheet.Cells[3 + i, 3].NumberFormat = "0";
xlWorkSheet.Cells[3 + i, 4].NumberFormat = "0";
sum += expList[i].Expense * expList[i].Count;
}
xlWorkSheet.Range["A" + (expList.Count + 3), "C" + (expList.Count + 3)].Merge();
xlWorkSheet.Cells[expList.Count + 3, 1] = (rm as ResourceManager).GetString("LB_Amount");
xlWorkSheet.Cells[expList.Count + 3, 4] = f.StringCurrencyFormat(sum.ToString());
xlWorkSheet.get_Range(rang).Columns.AutoFit();
rang = "A" + (expList.Count + 3) + ":" + "D" + (expList.Count + 3);
formatRange = xlWorkSheet.get_Range(rang);
formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
xlApp.DisplayAlerts = false;
Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog();
int venlenght = exitems[CB_Offer.SelectedIndex].Venue.Length;
int addlenght = exitems[CB_Offer.SelectedIndex].Address.Length;
saveFileDialog.FileName = (rm as ResourceManager).GetString("LB_Expenses") + "_" + exitems[CB_Offer.SelectedIndex].Venue.Substring(0, venlenght < 15 ? venlenght:15) + "_" + exitems[CB_Offer.SelectedIndex].Address.Substring(0, addlenght < 15 ? addlenght:15);
saveFileDialog.Filter = (rm as ResourceManager).GetString("SaveFileDialogFilter");
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = (rm as ResourceManager).GetString("SaveFileDialogTitle");
if (saveFileDialog.ShowDialog() == true)
{
System.IO.FileInfo file = new System.IO.FileInfo(saveFileDialog.FileName);
if (f.IsFileLocked(file, (rm as ResourceManager), ResourceNames) == false)
{
xlWorkBook.SaveAs(saveFileDialog.FileName);
ViewModel.WinMessageBoxItem wmsgbi = new ViewModel.WinMessageBoxItem((rm as ResourceManager).GetString("MessageBoxSaveTitle"), (rm as ResourceManager).GetString("MessageBoxSaveText"), MaterialDesignThemes.Wpf.PackIconKind.InformationCircle);
Windows.WinMessageBox wmsg = new Windows.WinMessageBox(wmsgbi, (rm as ResourceManager), ResourceNames, false);
wmsg.Show();
}
}
xlWorkBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}
catch (Exception ex)
{
System.Windows.Input.Mouse.OverrideCursor = null;
ViewModel.WinMessageBoxItem wmsb = new ViewModel.WinMessageBoxItem("Error", ex.Message, MaterialDesignThemes.Wpf.PackIconKind.Error);
Windows.WinMessageBox msb = new Windows.WinMessageBox(wmsb, (rm as ResourceManager), ResourceNames, false);
msb.Show();
xlWorkBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}
System.Windows.Input.Mouse.OverrideCursor = null;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
saveFileDialog1.Filter = "Libro de Excel (*.xlsx)|*.xlsx | Libro de Excel 97-2003 (*.xls)|*.xls | Archivo csv (*.csv)|*.csv";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
if (saveFileDialog1.FilterIndex == 1)
{
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
xla.Visible = true;
Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Worksheet ws = ((Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet);
int i = 1;
int j = 1;
int jj = lvlcheckin.Columns.Count;
for (int rr = 0; rr < jj; rr++)
{
ws.Cells[i, j] = lvlcheckin.Columns[rr].Text;
j = j + 1;
}
i = 2;
j = 1;
foreach (ListViewItem lista in lvlcheckin.Items)
{
ws.Cells[i, j] = lista.Text.ToString();
foreach (ListViewItem.ListViewSubItem drv in lista.SubItems)
{
ws.Cells[i, j] = drv.Text.ToString();
j += 1;
}
j = 1;
i += 1;
}
wb.SaveAs(saveFileDialog1.FileName, 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);
wb.Close(false, Type.Missing, Type.Missing);
xla.Quit();
}
else if (saveFileDialog1.FilterIndex == 2)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
foreach (ColumnHeader ch in lvlcheckin.Columns)
{
sb.Append(ch.Text + ",");
}
sb.AppendLine();
foreach (ListViewItem lvi in lvlcheckin.Items)
{
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
if (lvs.Text.Trim() == string.Empty)
{
sb.Append(" ,");
}
else
{
sb.Append(lvs.Text + ",");
}
}
sb.AppendLine();
}
System.IO.StreamWriter sw = new System.IO.StreamWriter(saveFileDialog1.FileName);
sw.Write(sb.ToString());
sw.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Skp的中文文本追加0
/// </summary>
private void AddCnSkpTxtZero()
{
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
try
{
// 创建Application对象
this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// 得到WorkBook对象, 打开已有的文件
xBook = this.xApp.Workbooks._Open(
@"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtTextCnEnMap2.xlsx",
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// 显示进度条
this.ResetProcessBar(xBook.Sheets.Count);
for (int i = xBook.Sheets.Count; i >= 1; i--)
{
// 取得相应的Sheet
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i];
for (int j = 1; j < 50; j++)
{
Microsoft.Office.Interop.Excel.Range rngCn = xSheet.get_Range("L" + j, Missing.Value);
if (rngCn != null && rngCn.Value2 != null && !string.IsNullOrEmpty(rngCn.Value2 as string))
{
rngCn.Value2 = rngCn.Value2 + "^0^";
}
}
// 更新进度条
this.ProcessBarStep();
}
// 隐藏进度条
this.CloseProcessBar();
// 保存
xSheet.SaveAs(
@"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtTextCnEnMap3.xlsx",
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception me)
{
MessageBox.Show(me.Message);
}
finally
{
// 隐藏进度条
this.CloseProcessBar();
// 清空各种对象
xSheet = null;
xBook = null;
if (this.xApp != null)
{
this.xApp.Quit();
this.xApp = null;
}
}
}
private void output_Click(object sender, EventArgs e)
{
string path = outputpath.Text.Trim();
string tableName = outputtables.Text;
if (path == "")
{
MessageBox.Show("请选择导出地址！");
}
else if (tableName == null || tableName == "")
{
MessageBox.Show("请选择表名！");
}
else
{
//导出
string filename = path + "/" + tableName + ".xls";
string sql = "select * from " + tableName;
DataTable dt = GetDataTable(sql);
if (dt == null)
{
return;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
if (File.Exists(filename))
{
File.Delete(filename);
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.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];
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
//写入数值
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
try
{
workbook.Saved = true;
workbook.SaveCopyAs(filename);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
return;
}
finally {
workbook.Close(true);
excel.Quit();
GC.Collect();
}
DialogResult result = MessageBox.Show("数据导出成功！是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
System.Diagnostics.Process.Start(filename);
}
}
}
private void CleanUp()
{
lock(sync) {
if(wb != null) {
wb.Close(Type.Missing, Type.Missing, Type.Missing);
Release(wb);
wb = null;
}
if(wbs != null) { Release(wbs); wbs = null; }
if(ws != null) { Release(ws); ws = null; }
if(wss != null) { Release(wss); wss = null; }
if(app != null) {
app.Quit();
Release(app);
app = null;
}
}
}
void CreateExcelDocument()
{
System.Windows.Input.Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlWorkBooks.Add(1);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
try
{
xlWorkSheet.Name = (rm as ResourceManager).GetString("Menu_Contact");
Microsoft.Office.Interop.Excel.Range formatRange = xlWorkSheet.get_Range("A1:C1");
formatRange.Font.Bold = true;
formatRange.WrapText = true;
string rang = "A1:" + "C" + (contList.Count + 1);
xlWorkSheet.get_Range(rang).Cells.Font.Name = "Comic Sans MS";
xlWorkSheet.Range[rang].Font.Size = 16;
xlWorkSheet.Range[rang].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);
xlWorkSheet.Range[rang].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
xlWorkSheet.Range[rang].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
formatRange = xlWorkSheet.get_Range(rang);
formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.Cells[1, 1] = LB_Name.Content;
xlWorkSheet.Cells[1, 2] = LB_Phone.Content;
xlWorkSheet.Cells[1, 3] = LB_Email.Content;
for (int i = 0; i < contList.Count; i++)
{
xlWorkSheet.Cells[2 + i, 1] = contList[i].Con_Name.Trim();
xlWorkSheet.Cells[2 + i, 2] = contList[i].Con_Phone.Trim();
xlWorkSheet.Cells[2 + i, 3] = contList[i].Con_Email.Trim();
}
xlWorkSheet.get_Range(rang).Columns.AutoFit();
xlApp.DisplayAlerts = false;
Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog();
saveFileDialog.FileName = (rm as ResourceManager).GetString("Menu_Contact");
saveFileDialog.Filter = (rm as ResourceManager).GetString("SaveFileDialogFilter");
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = (rm as ResourceManager).GetString("SaveFileDialogTitle");
if (saveFileDialog.ShowDialog() == true)
{
System.IO.FileInfo file = new System.IO.FileInfo(saveFileDialog.FileName);
if (f.IsFileLocked(file, (rm as ResourceManager), ResourceNames) == false)
{
xlWorkBook.SaveAs(saveFileDialog.FileName);
ViewModel.WinMessageBoxItem wmsgbi = new ViewModel.WinMessageBoxItem((rm as ResourceManager).GetString("MessageBoxSaveTitle"), (rm as ResourceManager).GetString("MessageBoxSaveText"), MaterialDesignThemes.Wpf.PackIconKind.InformationCircle);
Windows.WinMessageBox wmsg = new Windows.WinMessageBox(wmsgbi, (rm as ResourceManager), ResourceNames, false);
wmsg.Show();
}
}
xlWorkBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}
catch (Exception ex)
{
System.Windows.Input.Mouse.OverrideCursor = null;
ViewModel.WinMessageBoxItem wmsb = new ViewModel.WinMessageBoxItem("Error", ex.Message, MaterialDesignThemes.Wpf.PackIconKind.Error);
Windows.WinMessageBox msb = new Windows.WinMessageBox(wmsb, (rm as ResourceManager), ResourceNames, false);
msb.Show();
xlWorkBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}
System.Windows.Input.Mouse.OverrideCursor = null;
}
public static void generateExcel()
{
packages = DBConnector.getInstance().getPackages();
buckets = DBConnector.getInstance().getBuckets();
generatePackageList();
checkProcess();
generateBucketList();
/******************** create a workbook *************************/
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
excelworkBook = excel.Workbooks.Add(Type.Missing);
/********************* create new sheet (Activity List) ***************************/
excelSheetAll = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheetAll.Name = "Activity List";
int row = 1;
int tb1_start_x = row;
int tb1_start_y = 1;
excelSheetAll.Cells[row, 1] = "Process Name";
excelSheetAll.Cells[row, 2] = "Duration";
excelSheetAll.Cells[row, 3] = "Main Window Title";
row++;
foreach (KeyValuePair<string, Activity> pair in activityList)
{
excelSheetAll.Cells[row, 1] = pair.Value.processName;
excelSheetAll.Cells[row, 2] = pair.Value.duration.ToString("g");
excelSheetAll.Cells[row, 3] = pair.Key;
row++;
}
int tb1_end_x = row - 1;
int tb1_end_y = 3;
excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_end_x, tb1_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_start_x, tb1_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
/*************************** create new sheet (Packaged Activity List) ****************************/
excelSheetPackaged = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add();
excelSheetPackaged.Name = "Packaged Activity List";
row = 1;
int tb2_start_x = row;
int tb2_start_y = 1;
excelSheetPackaged.Cells[row, 1] = "Package Name";
excelSheetPackaged.Cells[row, 2] = "Duration";
row++;
foreach (KeyValuePair<string, TimeSpan> pair in packagedList)
{
excelSheetPackaged.Cells[row, 1] = pair.Key;
excelSheetPackaged.Cells[row, 2] = pair.Value.ToString("g");
row++;
}
int tb2_end_x = row - 1;
int tb2_end_y = 2;
excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_end_x, tb2_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_start_x, tb2_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Packaged Activity List";
chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Packaged Activity List";
chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
/************************* create new sheet (Bucketed Activity List) ******************************/
excelSheetBucketed = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add();
excelSheetBucketed.Name = "Bucketed Activity List";
row = 1;
int tb3_start_x = row;
int tb3_start_y = 1;
excelSheetBucketed.Cells[row, 1] = "Bucket Name";
excelSheetBucketed.Cells[row, 2] = "Duration";
row++;
foreach (KeyValuePair<string, TimeSpan> pair in bucketedList)
{
excelSheetBucketed.Cells[row, 1] = pair.Key;
excelSheetBucketed.Cells[row, 2] = pair.Value.ToString("g");
row++;
}
int tb3_end_x = row - 1;
int tb3_end_y = 2;
excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_end_x, tb3_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_start_x, tb3_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Buckted Activity List";
chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Buckted Activity List";
chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
/*************** save excel *******************/
//UserPrincipal.Current.DisplayName
String filePath = "C:\\Users\\" + Environment.UserName + "\\Desktop\\ActivityList-" + Environment.UserName + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
excelworkBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//excelworkBook.SaveAs(filePath);
excelworkBook.Close();
excel.Quit();
Console.WriteLine("-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------");
Console.WriteLine("Export to Excel");
Thread.Sleep(1000);
System.Diagnostics.Process.Start(filePath);
}
public void GenerarLayout(DoWorkEventArgs e)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
string sAutoFitRange = string.Empty;
Microsoft.Office.Interop.Excel.Workbook _Workbook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet _Worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_Workbook.ActiveSheet;
bgwProgreso.ReportProgress(0, string.Format("CREANDO DOCUMENTO"));
// Genera el layout bancario para Banorte
// Headers.
_Worksheet.Cells[1, 0 + 1] = "Oper";
_Worksheet.Cells[1, 1 + 1] = "Clave ID";
_Worksheet.Cells[1, 2 + 1] = "Cuenta Origen";
_Worksheet.Cells[1, 3 + 1] = bModo ? "Cuenta" : "Cuenta/CLABE destino";
_Worksheet.Cells[1, 4 + 1] = "Importe";
_Worksheet.Cells[1, 5 + 1] = "Referencia";
_Worksheet.Cells[1, 6 + 1] = "Descripción";
_Worksheet.Cells[1, 7 + 1] = "RFC Ordenante";
_Worksheet.Cells[1, 8 + 1] = "IVA";
_Worksheet.Cells[1, 9 + 1] = "Fecha aplicación";
_Worksheet.Cells[1, 10 + 1] = "Nombre beneficiario";
bgwProgreso.ReportProgress(0, string.Format("DANDO FORMATO"));
_Worksheet.Range["A2:A" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["B2:B" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["C2:C" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["D2:D" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["E2:E" + _DataTableMain.Rows.Count + 1].NumberFormat = "0.00";
_Worksheet.Range["F2:F" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["G2:G" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["H2:H" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
sAutoFitRange = "A:K";
for (int i = 0; i < _DataTableMain.Columns.Count; i++)
{
_Worksheet.Cells[1, i + 1].Interior.Color = ColorTranslator.ToOle(Color.FromArgb(102, 255, 51));
}
int ii = 0;
int iTotal = _DataTableMain.Rows.Count;
// Content.
for (int i = 0; i < _DataTableMain.Rows.Count; i++)
{
ii++;
Thread.Sleep(100);
int percents = (ii * 100) / iTotal;
bgwProgreso.ReportProgress(percents, string.Format("{0}/{1}", ii, iTotal));
for (int j = 0; j < _DataTableMain.Columns.Count; j++)
{
_Worksheet.Cells[i + 2, j + 1] = _DataTableMain.Rows[i][j].ToString();
}
}
_Worksheet.Columns[sAutoFitRange].AutoFit();
// Lots of options here. See the documentation.
_Workbook.SaveAs(sfdRuta.FileName);
_Workbook.Close();
app.Quit();
}
public void importa()
{
try
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.DefaultExt = ".xlsx";
openfile.Filter = "(.xlsx)|*.xlsx";
//openfile.ShowDialog();
var browsefile = openfile.ShowDialog();
if (browsefile == true)
{
txtFilePath.Text = openfile.FileName;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
//Static File From Base Path...........
//Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + "TestExcel.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Dynamic File Using Uploader...........
Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1);;
Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;
//string strCellData = "";
//double douCellData;
int rowCnt = 0;
int colCnt = 0;
Thread.CurrentThread.CurrentCulture = new CultureInfo("es-MX");
DataTable dt = new DataTable();
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
string strColumn = "";
strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
dt.Columns.Add(strColumn, typeof(string));
}
int columns_count = excelRange.Columns.Count;
int rows_count = excelRange.Rows.Count;
for (rowCnt = 2; rowCnt <= rows_count; rowCnt++)
{
object[] strData = new object[columns_count];
for (int clm = 0; clm < columns_count; clm++)
{
strData[clm] = ((Microsoft.Office.Interop.Excel.Range)excelSheet.Cells[rowCnt, clm + 1]).Value2;
}
dt.Rows.Add(strData);
}
gvData.ItemsSource = dt.DefaultView;
excelBook.Close(true, null, null);
excelApp.Quit();
var alert = new RadDesktopAlert();
alert.Header = "NOTIFICACIÓN";
alert.Content = "El archivo se cargó exitosamente.";
alert.ShowDuration = 3000;
RadDesktopAlertManager manager = new RadDesktopAlertManager();
manager.ShowAlert(alert);
}
else
{
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
private void btnExport_Click(object sender, RoutedEventArgs e)
{
int idx = dbFile.LastIndexOf(@"\");
Microsoft.Win32.SaveFileDialog dlgSaveDiagram = new Microsoft.Win32.SaveFileDialog();
dlgSaveDiagram.Filter = "Excel xlsx |*.xlsx;";
dlgSaveDiagram.Title = "Export Report";
if (dlgSaveDiagram.ShowDialog() == true)
{
string filePath = dlgSaveDiagram.FileName;
string vsd = AppDomain.CurrentDomain.BaseDirectory.ToString() + "SimTech-PRV_DataSheet_Model.xlsx";
System.IO.File.Copy(vsd, filePath);
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, Type.Missing, false, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
xlWorkSheet.Cells[5][15] = "test";
int count = 0;
int row1 = 14;
int note1 = 42;
int row2 = 74;
int note2 = 102;
int row3 = 134;
int note3 = 162;
for (int i = 1; i < mainTab.Items.Count; i++)
{
TabItem ti = (TabItem)mainTab.Items[i];
if (ti.Visibility == Visibility.Visible)
{
string num = ti.Name.Remove(0, 2);
UC_CaseStudy uc = (UC_CaseStudy)ti.FindName("uc" + num);
int col = count % 5;
if (count <= 4)
{
xlWorkSheet.Cells[5 + col * 2][row1] = ti.Header.ToString();
xlWorkSheet.Cells[5 + col * 2][row1 + 1] = 16;
if (ti.Header.ToString().Contains("Fire"))
{
xlWorkSheet.Cells[5 + col * 2][row1 + 1] = 21;
}
xlWorkSheet.Cells[5 + col * 2][row1 + 2] = uc.txtReliefPress.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 3] = uc.txtReliefTemp.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 4] = uc.txtReliefRate.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 5] = uc.txtReliefMW.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 6] = Compressibility;
xlWorkSheet.Cells[5 + col * 2][row1 + 7] = CpCv;
xlWorkSheet.Cells[3][note1 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note2 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note3 + count] = uc.txtDescription.Text;
}
else if (count >= 5 && count <= 9)
{
xlWorkSheet.Cells[5 + col * 2][row2] = ti.Header.ToString();
xlWorkSheet.Cells[5 + col * 2][row2 + 1] = 16;
if (ti.Header.ToString().Contains("Fire"))
{
xlWorkSheet.Cells[5 + col * 2][row2 + 1] = 21;
}
xlWorkSheet.Cells[5 + col * 2][row2 + 2] = uc.txtReliefPress.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 3] = uc.txtReliefTemp.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 4] = uc.txtReliefRate.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 5] = uc.txtReliefMW.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 6] = Compressibility;
xlWorkSheet.Cells[5 + col * 2][row2 + 7] = CpCv;
xlWorkSheet.Cells[3][note1 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note2 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note3 + count] = uc.txtDescription.Text;
}
else
{
xlWorkSheet.Cells[5 + col * 2][row3] = ti.Header.ToString();
xlWorkSheet.Cells[5 + col * 2][row3 + 1] = 16;
if (ti.Header.ToString().Contains("Fire"))
{
xlWorkSheet.Cells[5 + col * 2][row3 + 1] = 21;
}
xlWorkSheet.Cells[5 + col * 2][row3 + 2] = uc.txtReliefPress.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 3] = uc.txtReliefTemp.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 4] = uc.txtReliefRate.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 5] = uc.txtReliefMW.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 6] = Compressibility;
xlWorkSheet.Cells[5 + col * 2][row3 + 7] = CpCv;
xlWorkSheet.Cells[3][note1 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note2 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note3 + count] = uc.txtDescription.Text;
}
count++;
}
}
if (count <= 5)
{
Microsoft.Office.Interop.Excel.Range r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[3][181]];
r.UnMerge();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[14][181]];
r.Clear();
Microsoft.Office.Interop.Excel.Shape pic = xlWorkSheet.Shapes.Item(3) as Microsoft.Office.Interop.Excel.Shape;
pic.Delete();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][62], xlWorkSheet.Cells[3][121]];
r.UnMerge();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][62], xlWorkSheet.Cells[14][121]];
r.Clear();
pic = xlWorkSheet.Shapes.Item(2) as Microsoft.Office.Interop.Excel.Shape;
pic.Delete();
}
else if (count <= 10)
{
Microsoft.Office.Interop.Excel.Range r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[3][181]];
r.UnMerge();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[14][181]];
r.Clear();
Microsoft.Office.Interop.Excel.Shape pic = xlWorkSheet.Shapes.Item(3) as Microsoft.Office.Interop.Excel.Shape;
pic.Delete();
}
xlWorkBook.Save();
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
private void ChangeUpdateType(string sChangeTo, bool bKeepValues)
{
if (sChangeTo == "Jobs")
{
radioJobs.Checked = true;
radioOrderLines.Checked = false;
sUpdateType = "Jobs";
}
else
{
radioJobs.Checked = false;
radioOrderLines.Checked = true;
sUpdateType = "OrderLines";
}
if (bKeepValues)
{
if (!String.IsNullOrEmpty(cbSheet.Text) && cbSheet.Text != "" && cbSheet.Text.Length > 1 && cbSheet.Items.Count > 1)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[cbSheet.Text];
GetResultSetForDGV();
}
}
else
{
txtFile.Text = "";
txtFile.BackColor = System.Drawing.Color.FromName("Window");
txtFile.Focus();
DisableSheetOptions();
}
}
static void Main(string[] args)
{
var optionsChrome = new ChromeOptions();
optionsChrome.AddArgument("--headless");
var driver = new ChromeDriver(optionsChrome);
driver.Navigate().GoToUrl("https://fulltime.thefa.com/ff/DivisionDetails?divisionid=7044651&leagueid=3956158&seasonid=659468196");
Thread.Sleep(1000);
var titleSection = driver.FindElementById("ff-division-table-obj");
var tableValues = titleSection.Text;
var footballLeagueData = new List <FootballLeagueRow>();
driver.FindElement(By.XPath("//*[@id=\"coachmark-modal-block\"]/div/div/div[2]/div/div[2]")).Click();
for (int i = 1; i <= 22; i++)
{
var dataRow = new FootballLeagueRow();
for (int j = 1; j <= 11; j++)
{
if (j == 10)
{
continue;
}
var xPathExpression = string.Format("//*[@id=\"ff-division-table-obj\"]/tbody/tr[{0}]/td[{1}]", i, j);
var dataItem = driver.FindElement(By.XPath(xPathExpression)).Text;
switch (j)
{
case 1:
dataRow.POS = dataItem;
break;
case 2:
dataRow.Team = dataItem;
break;
case 3:
dataRow.PLD = dataItem;
break;
case 4:
dataRow.W = dataItem;
break;
case 5:
dataRow.D = dataItem;
break;
case 6:
dataRow.L = dataItem;
break;
case 7:
dataRow.GF = dataItem;
break;
case 8:
dataRow.GA = dataItem;
break;
case 9:
dataRow.GD = dataItem;
break;
case 11:
dataRow.PTS = dataItem;
break;
}
}
footballLeagueData.Add(dataRow);
}
driver.Quit();
driver.Dispose();
string path = @"C:\Users\jackw\Documents\WebScraper\mybook.xlsx";
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
//Add data to excel sheet
int rowCount = 2;
foreach (var item in footballLeagueData)
{
mWSheet1.Cells[rowCount, 1] = item.POS;
mWSheet1.Cells[rowCount, 2] = item.Team;
mWSheet1.Cells[rowCount, 3] = item.PLD;
mWSheet1.Cells[rowCount, 4] = item.W;
mWSheet1.Cells[rowCount, 5] = item.D;
mWSheet1.Cells[rowCount, 6] = item.L;
mWSheet1.Cells[rowCount, 7] = item.GF;
mWSheet1.Cells[rowCount, 8] = item.GA;
mWSheet1.Cells[rowCount, 9] = item.GD;
mWSheet1.Cells[rowCount, 10] = item.PTS;
rowCount++;
}
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
private void fillDocumentExcel(string _filename)
{
Object wMissing = System.Reflection.Missing.Value;
Object wTrue = true;
Object wFalse = false;
string[] text = inputAppFullName.Text.Split(';');
string d = text[0];
Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
// Microsoft.Office.Interop.Excel.Workbook bookExcel = new Microsoft.Office.Interop.Excel.Workbook();
Microsoft.Office.Interop.Excel.Worksheet worksheetExcel = new Microsoft.Office.Interop.Excel.Worksheet();
Microsoft.Office.Interop.Excel.Sheets sheetsExcel;
Microsoft.Office.Interop.Excel.Range rangeExcel;
appExcel.Visible = true;
//Работа с документом - Договор
appExcel.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
sheetsExcel = appExcel.Worksheets;
worksheetExcel = (Microsoft.Office.Interop.Excel.Worksheet)sheetsExcel.get_Item(1);
rangeExcel = worksheetExcel.get_Range("A2", "AE2");
rangeExcel.Merge(Type.Missing);
rangeExcel.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
rangeExcel.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
rangeExcel.Value2 = "№" + inputNumContract.Text + " от" + " 2012г.";
rangeExcel = worksheetExcel.get_Range("F5", "AH5");
rangeExcel.Merge(Type.Missing);
rangeExcel.Value2 = inputAppNameOrgOrPF.Text;
rangeExcel = worksheetExcel.get_Range("D9", "G9");
rangeExcel.Merge(Type.Missing);
rangeExcel.Value2 = "Путёвка в санаторий " + " \" " + inputAppWay.Text + " \" " + inputNumDays.Text + "дн.," + " \" " + inputAppNumberAndCategory.Text + " \" " + ", с " + inputAppStartTour.Text + " " + d;
rangeExcel = worksheetExcel.get_Range("Z9", "AA9");
rangeExcel.Merge(Type.Missing);
rangeExcel.Value2 = inputAppCost.Text;
rangeExcel = worksheetExcel.get_Range("AD9", "AE9");
rangeExcel.Merge(Type.Missing);
rangeExcel.Value2 = inputAppCost.Text;
rangeExcel = worksheetExcel.get_Range("AD11", "AE11");
rangeExcel.Merge(Type.Missing);
rangeExcel.Value2 = inputAppCost.Text;
rangeExcel = worksheetExcel.get_Range("B14", "AE14");
rangeExcel.Merge(Type.Missing);
rangeExcel.Value2 = "Всего оказана услуг 1, на сумму" + " " + inputAppCost.Text + " " + "рублей";
}
public void Print(object sender, EventArgs e)
{
if (((FrmMAIN)this.MdiParent).ActiveMdiChild == this)
{
if (dgvSales.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;
try
{
int i, j;
saveFileDialog1.Filter = "Excel Files (*.xls)|*.xls";
saveFileDialog1.InitialDirectory = "C:";
saveFileDialog1.Title = "SaveMaterialCost";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add();
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int k = 1; k < dgvSales.ColumnCount; k++)
{
xlWorkSheet.Cells[1, k] = dgvSales.Columns[k].HeaderText.ToString();
}
for (i = 0; i < dgvSales.RowCount; i++)
{
for (j = 0; j < dgvSales.ColumnCount - 1; j++)
{
//if (j == 3)
// continue;
if (dgvSales[j, i].Value != null)
{
xlWorkSheet.Cells[i + 2, j + 1] = dgvSales[j, i].Value.ToString();
}
}
}
xlWorkBook.SaveAs(saveFileDialog1.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
xlWorkBook.Close(true);
xlApp.Quit();
MessageBox.Show("출력되었습니다.", "출력 완료", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception err)
{
MessageBox.Show("출력에 실패하였습니다.", "출력 실패", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (xlApp != null)
{
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
}
}
}
private void fillDocumentExcel2(string _filename)
{
Object wMissing = System.Reflection.Missing.Value;
Object wTrue = true;
Object wFalse = false;
string[] text = inputAppFullName.Text.Split(';');
string d = text[0];
Microsoft.Office.Interop.Excel.Application appExcel1 = new Microsoft.Office.Interop.Excel.Application();
// Microsoft.Office.Interop.Excel.Workbook bookExcel = new Microsoft.Office.Interop.Excel.Workbook();
Microsoft.Office.Interop.Excel.Worksheet worksheetExcel1 = new Microsoft.Office.Interop.Excel.Worksheet();
Microsoft.Office.Interop.Excel.Sheets sheetsExcel1;
Microsoft.Office.Interop.Excel.Range rangeExcel1;
appExcel1.Visible = true;
//Работа с документом - Приходный ордер
appExcel1.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
sheetsExcel1 = appExcel1.Worksheets;
worksheetExcel1 = (Microsoft.Office.Interop.Excel.Worksheet)sheetsExcel1.get_Item(1);
rangeExcel1 = worksheetExcel1.get_Range("I17", "K17");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = inputNumContract.Text;
rangeExcel1 = worksheetExcel1.get_Range("L17", "N17");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = dateRec.Text;
rangeExcel1 = worksheetExcel1.get_Range("G27:G28", "J27:J28");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = sumRec.Text + "руб. 00 коп.";
rangeExcel1 = worksheetExcel1.get_Range("C29:C30", "N29:N30");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = "через " + d;
rangeExcel1 = worksheetExcel1.get_Range("C31:C34", "N31:N34");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = inputArBase.Text;
rangeExcel1 = worksheetExcel1.get_Range("C36", "N36");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = inputAppCost.Text;
rangeExcel1 = worksheetExcel1.get_Range("S8:S9", "Y8:Y9");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = "через" + d;
rangeExcel1 = worksheetExcel1.get_Range("S13:S14", "Y13:Y14");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = inputArBase.Text;
rangeExcel1 = worksheetExcel1.get_Range("S18:S19", "Y18:Y19");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = sumRec.Text;
rangeExcel1 = worksheetExcel1.get_Range("S31:S32", "Y31:Y32");
rangeExcel1.Merge(Type.Missing);
rangeExcel1.Value2 = dateRec.Text;
}
public static List <AttendanceInfo> GetAttendanceInfoList(Microsoft.Office.Interop.Excel.Worksheet srcSheet)
{
int nameColumnIndex = 0;
int dateColumnIndex = 0;
int arriveColumnIndex = 0;
int leaveColumnIndex = 0;
int exceptionColumnIndex = 0;
// locate column index
for (int i = 1; i < 30; i++)
{
string cellValue = srcSheet.Cells[1, i].Value != null ? srcSheet.Cells[1, i].Value.ToString() : string.Empty;
if (cellValue == "姓名")
{
nameColumnIndex = i;
}
else if (cellValue == "日期")
{
dateColumnIndex = i;
}
else if (cellValue == "签到时间")
{
arriveColumnIndex = i;
}
else if (cellValue == "签退时间")
{
leaveColumnIndex = i;
}
else if (cellValue == "例外情况")
{
exceptionColumnIndex = i;
}
}
// 姓名 日期 签到时间 签退时间
List <AttendanceInfo> infoList = new List <AttendanceInfo>();
for (int startRowIndex = 2; startRowIndex < 50000; startRowIndex++)
{
// name
string name = srcSheet.Cells[startRowIndex, nameColumnIndex].Value;
if (string.IsNullOrWhiteSpace(name))
{
if (startRowIndex > 10)
{
break;
}
continue; // the top lines may have some statistic infomation
}
AttendanceInfo todayInfo = new AttendanceInfo(name,
srcSheet.Cells[startRowIndex, dateColumnIndex].Value,
srcSheet.Cells[startRowIndex, arriveColumnIndex].Value,
srcSheet.Cells[startRowIndex, leaveColumnIndex].Value,
srcSheet.Cells[startRowIndex, exceptionColumnIndex].Value);
infoList.Add(todayInfo);
}
// pad absent days
PadAbsentDays(infoList);
// sort
infoList.Sort((info1, info2) =>
{
if (!info1.Name.Equals(info2.Name))
{
return(info1.Name.CompareTo(info2.Name));
}
return(info1.Date.CompareTo(info2.Date));
});
return(infoList);
}
public ArrayList DoPrint()
{
//根据配置文件读取有关excel模版的信息
_excelName = IniReadValue("模版信息", _printType);
_excelWindowsName = "Microsoft Excel - " + _excelName+" [只读]";
_excelPath = Application.StartupPath + "\\" + _excelName;
//当条形码打印成功后增加到arraylist里返回到调用方设置已打印标志
ArrayList _arPrinted = new ArrayList();
try
{
if (_printData == null || _printData.Tables.Count == 0 || _printData.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无打印数据!", "提示");
CloseExcel();
return _arPrinted;
}
//检验是否取到模版信息，否则退出
if (_excelName == "" || _excelWindowsName == "" || _excelPath == "")
{
MessageBox.Show("模版信息维护错误！", "提示");
CloseExcel();
return _arPrinted;
}
//判断excel模版是否已经被打开，打开excel模版
IntPtr a = FindWindow(null, _excelWindowsName);
if (a.ToString() != "0")
{
MessageBox.Show("Excel模版已经被打开,可能是正在打印或人为打开。\n请确认没有执行打印程序,然后手动关闭该EXCEL文档。", "提示");
CloseExcel();
return _arPrinted;
}
excel.Visible = false;
excel.DisplayAlerts = false;
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlNormal;
excel.Top = 8000;
excel.WorkbookActivate+=new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(excel_WorkbookActivate);
string fileName = _excelPath;
workbook = excel.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,missing, missing);
workbook.ReadOnlyRecommended = true;
Int32 sheetNum1 = 0;
try
{
sheetNum1 = Convert.ToInt32(IniReadValue(_printType, "FIELDNUM"));
}
catch
{
MessageBox.Show("配置文件-" + _printType + "-FIELDNUM，维护错误");
CloseExcel();
return _arPrinted;
}
Hashtable hs = new Hashtable();
Hashtable hs2 = new Hashtable();
for (Int32 s = 1; s < sheetNum1 + 1; s++)
{
string FieldValue = IniReadValue(_printType, "F" + s.ToString());
hs.Add("F" + s.ToString(), FieldValue);
string FieldCell = IniReadValue(_printType, "C" + s.ToString());
hs2.Add("C" + s.ToString(), FieldCell);
}
_iniTable.Add(_printType, hs);
_iniCell.Add(_printType, hs2);
//针对每一行数据生成数据表
Int32 num = _printData.Tables[0].Rows.Count;
sh2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType);
for (int i = 1; i < num+1; i++)
{
if (i == 1)
{
sh3 = sh2;
}
else
{
sh3 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType + " (" + Convert.ToString(i) + ")");
}
sh2.Copy(Type.Missing, sh3);
}
string Barcode = "";
for (int i = 0; i < num; i++)
{
//根据配置文件设置excel中的数据
DataRow r = _printData.Tables[0].Rows[i];
Barcode = r["条码号"].ToString();
string sheet = "";
sheet = _printType + " (" + Convert.ToString(i + 2) + ")";
try
{
GetWorkSheet(sheet);
}
catch { MessageBox.Show("找不到打印模版表" + sheet, "提示"); return _arPrinted; }
Hashtable ht = (Hashtable)_iniTable[_printType];
Hashtable ht2 = (Hashtable)_iniCell[_printType];
foreach (DictionaryEntry de in ht)
{
try
{
SetCellRangeValue(ht2["C" + de.Key.ToString().Substring(1)].ToString(), r[de.Value.ToString()].ToString());
}
catch { }
}
SetCellRangeValue("A10", r["条码号"].ToString());
_arPrinted.Add(r["条码号"].ToString());
}
//调用VBA宏生成条形码
object robj = new object();
try
{
RunExcelMacro(_excelPath, "getTime3", new Object[] { "" }, out robj, true);
}
catch
{
MessageBox.Show("执行宏失败！", "提示");
CloseExcel();
return _arPrinted;
}
Thread.Sleep(1000);
//打印输出
try
{
for (int i = 0; i < Convert.ToInt32(_printCopies); i++)
{
workbook.Worksheets.PrintOut(2, num+1, 1, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
catch
{
MessageBox.Show("连接打印机失败，请检查操作系统中默认打印机是否运行正常！", "提示");
_arPrinted.Clear();
CloseExcel();
return _arPrinted;
}
CloseExcel();
return _arPrinted;
}
catch
{
CloseExcel();
return _arPrinted;
}
}
public bool WriteDataTableToExcel_DCCN_Mau1(System.Data.DataTable dataTable, string taikhoan, string madtpn, DateTime from, DateTime to, DateTime print)
{
string saveAsLocation = Application.StartupPath + @"\Excel\DCCN.xls";
string worksheetName = "IN";
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
var collection = new Microsoft.Office.Interop.Excel.Worksheet[4];
collection[0] = excelworkBook.Worksheets.Add();
collection[0].Name = "DCCN";
collection[0].PageSetup.Application.ActiveWindow.DisplayGridlines = false;
collection[1] = excelworkBook.Worksheets.Add();
collection[1].Name = "DCCN_TCT";
collection[1].PageSetup.Application.ActiveWindow.DisplayGridlines = false;
collection[2] = excelworkBook.Worksheets.Add();
collection[2].Name = "NGUON";
collection[3] = excelworkBook.Worksheets.Add();
collection[3].Name = "KTCHUKY";
for (var i = 0; i < 3; i++)
{
excelworkBook.Worksheets[5].Delete();
}
#region General Cells
////Kiểu chữ
//excelSheet.Range["A1", "L" + (dataTable.Rows.Count + 55)].Font.Name = "Times New Roman";
//excelSheet.Range["A4", "L10"].Font.Name = "Tahoma";
////Font chữ
//excelSheet.Range["A1", "A3"].Font.Size = 10;
//excelSheet.Range["A4", "A7"].Font.Size = 12;
//excelSheet.Range["A8", "L9"].Font.Size = 10;
//excelSheet.Range["A10", "L" + (dataTable.Rows.Count + 9)].Font.Size = 9;
//excelSheet.Range["A4", "A4"].Font.FontStyle = "Bold";
//excelSheet.Range["A8", "L9"].Font.FontStyle = "Bold";
//excelSheet.Range["A" + (dataTable.Rows.Count + 30), "L" + (dataTable.Rows.Count + 30)].Font.FontStyle = "Bold";
//excelSheet.Range["A" + (dataTable.Rows.Count + 11), "L" + (dataTable.Rows.Count + 16)].Font.FontStyle = "Bold";
////Canh giữa chữ
//excelSheet.Range["A4", "L9"].HorizontalAlignment = -4108;
//excelSheet.Range["A10", "B" + (dataTable.Rows.Count + 10)].HorizontalAlignment = -4108;
//excelSheet.Range["C" + (dataTable.Rows.Count + 11), "C" + (dataTable.Rows.Count + 16)].HorizontalAlignment = -4108;
//excelSheet.Range["A" + (dataTable.Rows.Count + 29), "L" + (dataTable.Rows.Count + 31)].HorizontalAlignment = -4108;
////Merge
//excelSheet.Range["A4", "L4"].MergeCells = true;
//excelSheet.Range["A5", "L5"].MergeCells = true;
//excelSheet.Range["A6", "L6"].MergeCells = true;
//excelSheet.Range["A8", "B8"].MergeCells = true;
//excelSheet.Range["G8", "H8"].MergeCells = true;
//excelSheet.Range["D" + (dataTable.Rows.Count + 13), "F" + (dataTable.Rows.Count + 13)].MergeCells = true;
//excelSheet.Range["D" + (dataTable.Rows.Count + 14), "F" + (dataTable.Rows.Count + 14)].MergeCells = true;
//excelSheet.Range["D" + (dataTable.Rows.Count + 15), "F" + (dataTable.Rows.Count + 15)].MergeCells = true;
//excelSheet.Range["D" + (dataTable.Rows.Count + 16), "F" + (dataTable.Rows.Count + 16)].MergeCells = true;
//excelSheet.Range["D" + (dataTable.Rows.Count + 18), "F" + (dataTable.Rows.Count + 18)].MergeCells = true;
//excelSheet.Range["A" + (dataTable.Rows.Count + 30), "C" + (dataTable.Rows.Count + 30)].MergeCells = true;
//excelSheet.Range["A" + (dataTable.Rows.Count + 31), "C" + (dataTable.Rows.Count + 31)].MergeCells = true;
////Kích thước Cột và Dòng
//excelSheet.Range["A1", "A" + (dataTable.Rows.Count + 55)].RowHeight = "16.5";
//excelSheet.Range["A1"].ColumnWidth = 10;
//excelSheet.Range["B1"].ColumnWidth = 8;
//excelSheet.Range["C1"].ColumnWidth = 45;
//excelSheet.Range["D1", "I1"].ColumnWidth = 8;
//excelSheet.Range["J1", "L1"].ColumnWidth = 10;
////Border
//excelSheet.Range["A8", "L9"].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//excelSheet.Range["A8", "L9"].Borders.Weight = 2d;
//excelSheet.Range["A10", "L" + (dataTable.Rows.Count + 10)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDot;
//excelSheet.Range["A10", "L" + (dataTable.Rows.Count + 10)].Borders.Weight = 2d;
//excelSheet.Range["A" + (dataTable.Rows.Count + 11), "L" + (dataTable.Rows.Count + 11)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//excelSheet.Range["A" + (dataTable.Rows.Count + 11), "L" + (dataTable.Rows.Count + 11)].Borders.Weight = 2d;
//excelSheet.Range["C" + (dataTable.Rows.Count + 13), "F" + (dataTable.Rows.Count + 16)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//excelSheet.Range["C" + (dataTable.Rows.Count + 13), "F" + (dataTable.Rows.Count + 16)].Borders.Weight = 2d;
////Công thức
//excelSheet.Range["G" + (dataTable.Rows.Count + 11), "G" + (dataTable.Rows.Count + 11)].Formula = String.Format("=sum(G11:G{0})", dataTable.Rows.Count + 10);
//excelSheet.Range["H" + (dataTable.Rows.Count + 11), "H" + (dataTable.Rows.Count + 11)].Formula = String.Format("=sum(H11:H{0})", dataTable.Rows.Count + 10);
//excelSheet.Range["J" + (dataTable.Rows.Count + 11), "J" + (dataTable.Rows.Count + 11)].Formula = String.Format("=sum(J11:J{0})", dataTable.Rows.Count + 10);
//excelSheet.Range["K" + (dataTable.Rows.Count + 11), "K" + (dataTable.Rows.Count + 11)].Formula = String.Format("=sum(K11:K{0})", dataTable.Rows.Count + 10);
//excelSheet.Range["L" + (dataTable.Rows.Count + 11), "L" + (dataTable.Rows.Count + 11)].Formula = String.Format("=sum(L11:L{0})", dataTable.Rows.Count + 10);
////format
//excelSheet.Range["A11", "A" + (dataTable.Rows.Count + 11)].EntireColumn.NumberFormat = "dd/MM/yyyy";
//excelSheet.Range["G11", "G" + (dataTable.Rows.Count + 11)].EntireColumn.NumberFormat = "#,###";
//excelSheet.Range["H11", "H" + (dataTable.Rows.Count + 11)].EntireColumn.NumberFormat = "#,###";
//excelSheet.Range["I11", "I" + (dataTable.Rows.Count + 11)].EntireColumn.NumberFormat = "#,###";
//excelSheet.Range["J11", "J" + (dataTable.Rows.Count + 11)].EntireColumn.NumberFormat = "#,###";
//excelSheet.Range["K11", "K" + (dataTable.Rows.Count + 11)].EntireColumn.NumberFormat = "#,###";
//excelSheet.Range["L11", "L" + (dataTable.Rows.Count + 11)].EntireColumn.NumberFormat = "#,###";
//excelSheet.Range["D" + (dataTable.Rows.Count + 13), "D" + (dataTable.Rows.Count + 16)].EntireColumn.NumberFormat = "#,###";
#endregion
//gắn header
collection[3].Range["B1"].Value2 = "Tên công ty"; collection[3].Range["A1"].Value2 = "Tên công ty";
collection[3].Range["B2"].Value2 = "Địa chỉ"; collection[3].Range["A2"].Value2 = "Địa chỉ";
collection[3].Range["B3"].Value2 = "Mã số thuế "; collection[3].Range["A3"].Value2 = "Mã số thuế ";
collection[3].Range["B4"].Value2 = "Tên người lập"; collection[3].Range["A4"].Value2 = "Tên người lập";
collection[3].Range["B5"].Value2 = "Tên kế toán trưởng"; collection[3].Range["A5"].Value2 = "Tên kế toán trưởng";
collection[3].Range["B6"].Value2 = "Dư nợ ĐK"; collection[3].Range["A6"].Value2 = "Dư nợ ĐK";
collection[3].Range["B7"].Value2 = "Dư có ĐK"; collection[3].Range["A7"].Value2 = "Dư có ĐK";
collection[3].Range["B8"].Value2 = "Phát sinh Nợ"; collection[3].Range["A8"].Value2 = "Phát sinh Nợ";
collection[3].Range["B9"].Value2 = "Phát sinh Có"; collection[3].Range["A9"].Value2 = "Phát sinh Có";
collection[3].Range["B10"].Value2 = "Đến ngày"; collection[3].Range["A10"].Value2 = "Đến ngày";
collection[3].Range["B11"].Value2 = "Số dư cuối kỳ"; collection[3].Range["A11"].Value2 = "Số dư cuối kỳ";
collection[3].Range["B12"].Value2 = "Tên KH"; collection[3].Range["A12"].Value2 = "Tên KH";
collection[3].Range["B13"].Value2 = "Địa chỉ khách hàng"; collection[3].Range["A13"].Value2 = "Địa chỉ khách hàng";
collection[3].Range["B14"].Value2 = "Mã số thuế khách hàng"; collection[3].Range["A14"].Value2 = "Mã số thuế khách hàng";
collection[3].Range["B15"].Value2 = "Ngày In"; collection[3].Range["A15"].Value2 = "Ngày In";
collection[3].Range["B16"].Value2 = "Mã tài khoản"; collection[3].Range["A16"].Value2 = "Mã tài khoản";
collection[3].Range["B17"].Value2 = "Số dư cuối kỳ bằng số"; collection[3].Range["A17"].Value2 = "Số dư cuối kỳ bằng số";
collection[2].Range["B1"].Value2 = "Tên công ty"; collection[2].Range["A1"].Value2 = "Tên công ty";
collection[2].Range["B2"].Value2 = "Địa chỉ"; collection[2].Range["A2"].Value2 = "Tên chi nhánh";
collection[2].Range["B3"].Value2 = "Mã số thuế "; collection[2].Range["A3"].Value2 = "Địa chỉ";
collection[2].Range["B4"].Value2 = "Tên người lập"; collection[2].Range["A4"].Value2 = "Mã số thuế";
collection[2].Range["B5"].Value2 = "Tên kế toán trưởng"; collection[2].Range["A5"].Value2 = "Điện thoại";
collection[2].Range["B6"].Value2 = "Dư nợ ĐK"; collection[2].Range["A6"].Value2 = "Số tài khoản ngân hàng";
collection[2].Range["B7"].Value2 = "Dư có ĐK"; collection[2].Range["A7"].Value2 = "Tên ngân hàng";
collection[2].Range["B8"].Value2 = "Phát sinh Nợ"; collection[2].Range["A8"].Value2 = "Tên giám đốc";
collection[2].Range["B9"].Value2 = "Phát sinh Có"; collection[2].Range["A9"].Value2 = "Tên kế toán trưởng";
collection[2].Range["B10"].Value2 = "Đến ngày"; collection[2].Range["A10"].Value2 = "Mã tài khoản Công nợ";
collection[2].Range["B11"].Value2 = "Số dư cuối kỳ"; collection[2].Range["A11"].Value2 = "Tên tài khoản công nợ";
collection[2].Range["B12"].Value2 = "Tên KH"; collection[2].Range["A12"].Value2 = "Mã khách hàng";
collection[2].Range["B13"].Value2 = "Địa chỉ khách hàng"; collection[2].Range["A13"].Value2 = "Tên khách hàng";
collection[2].Range["B14"].Value2 = "Mã số thuế khách hàng"; collection[2].Range["A14"].Value2 = "Từ ngày";
collection[2].Range["B15"].Value2 = "Ngày In"; collection[2].Range["A15"].Value2 = "Đến ngày";
collection[2].Range["B16"].Value2 = "Mã tài khoản"; collection[2].Range["A16"].Value2 = "Ngày In";
collection[2].Range["B17"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A17"].Value2 = "Đại diện Bên Bán";
collection[2].Range["B18"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A18"].Value2 = "Đại diện Bên Mua";
collection[2].Range["B19"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A19"].Value2 = "Dư nợ đầu kỳ";
collection[2].Range["B20"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A20"].Value2 = "Dư có đầu kỳ";
collection[2].Range["B21"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A21"].Value2 = "Phát sinh nợ trong kỳ";
collection[2].Range["B22"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A22"].Value2 = "- Tiền hàng ";
collection[2].Range["B23"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A23"].Value2 = "- Tiền VAT ";
collection[2].Range["B24"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A24"].Value2 = "- Tiền mặt ";
collection[2].Range["B25"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A25"].Value2 = "- Chuyển khoản ";
collection[2].Range["B26"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A26"].Value2 = "Phát sinh có trong kỳ";
collection[2].Range["B27"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A27"].Value2 = "- Tiền hàng ";
collection[2].Range["B28"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A28"].Value2 = "- Tiền VAT ";
collection[2].Range["B29"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A29"].Value2 = "- Tiền mặt ";
collection[2].Range["B30"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A30"].Value2 = "- Chuyển khoản ";
collection[2].Range["B31"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A31"].Value2 = "Số dư NỢ cuối kỳ";
collection[2].Range["B32"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A32"].Value2 = "Số dư CÓ cuối kỳ";
collection[2].Range["B33"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A33"].Value2 = "Số dư cuối kỳ";
collection[2].Range["B34"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A34"].Value2 = "Số tiền bằng chữ (CK Nợ)";
collection[2].Range["B35"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A35"].Value2 = "Số tiền bằng chữ (CK Có)";
collection[2].Range["B36"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A36"].Value2 = "Số tiền ký quỹ (Bằng số) - 338811";
collection[2].Range["B37"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A37"].Value2 = "Số tiền ký quỹ (Bằng chữ) - 338811";
collection[2].Range["B38"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A38"].Value2 = "Số tiền ký quỹ (Bằng số) - 3386";
collection[2].Range["B39"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A39"].Value2 = "Số tiền ký quỹ (Bằng chữ) - 3386";
collection[2].Range["B40"].Value2 = "Số dư cuối kỳ bằng số"; collection[2].Range["A40"].Value2 = "Số tiền bằng chữ cuối kỳ";
collection[1].Range["A1"].Value2 = "=NGUON!B1";
collection[1].Range["U1"].Value2 = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM";
collection[1].Range["U2"].Value2 = "Độc Lập - Tư Do - Hạnh Phúc";
collection[1].Range["S3"].Value2 = "'----------oOo----------";
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);
excelworkBook.Close();
excel.Quit();
Process.Start(saveAsLocation);
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
/// <summary>
/// 销毁Excel内存
/// </summary>
public void CloseExcel()
{
workbook.Close(false, null, null);
//退出Excel，并且释放调用的COM资源
excel.Quit();
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sh2);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sh3);
}
catch { }
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
}
catch { }
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
catch { }
worksheet = null;
sh2 = null;
sh3 = null;
workbook = null;
excel = null;
GC.Collect();
KillExcelProcess();
}
private void CreaExcel(DataSet ds)
{
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks oLibros = default(Microsoft.Office.Interop.Excel.Workbooks);
Microsoft.Office.Interop.Excel.Workbook oLibro = default(Microsoft.Office.Interop.Excel.Workbook);
Microsoft.Office.Interop.Excel.Sheets oHojas = default(Microsoft.Office.Interop.Excel.Sheets);
Microsoft.Office.Interop.Excel.Worksheet oHoja = default(Microsoft.Office.Interop.Excel.Worksheet);
Microsoft.Office.Interop.Excel.Range oCeldas = default(Microsoft.Office.Interop.Excel.Range);
try
{
string sFile = null;
string sTemplate = null;
// Usamos una plantilla para crear el nuevo excel
sFile = Server.MapPath("PDV_Planning") + "\\" + "Datos_Rutas.xls";
sTemplate = Server.MapPath("PDV_Planning") + "\\" + "Datos_Panel_ptoVenta1.xls";
oExcel.Visible = false;
oExcel.DisplayAlerts = false;
// Abrimos un nuevo libro
oLibros = oExcel.Workbooks;
oLibros.Open(sTemplate);
oLibro = oLibros.Item[1];
oHojas = oLibro.Worksheets;
for (int i = 0; i < ds.Tables.Count; i++)
{
oHoja = (Microsoft.Office.Interop.Excel.Worksheet)oHojas.Item[i + 1];
oHoja.Name = "Hoja" + (i + 1);
oCeldas = oHoja.Cells;
oHoja.Range["B2"].Interior.Color = 0;
oHoja.Range["B2"].Font.Color = 16777215;
oHoja.Range["A2"].Interior.Color = 0;
oHoja.Range["A2"].Font.Color = 16777215;
oHoja.Range["B2"].Font.Bold = true;
oHoja.Range["A2"].Font.Bold = true;
oHoja.Range["A2", "B" + (ds.Tables[i].Rows.Count + 2).ToString()].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDash;
oHoja.Range["A2", "B" + (ds.Tables[i].Rows.Count + 2).ToString()].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline;
VuelcaDatos(ds.Tables[i], oCeldas);
}
oHoja.SaveAs(sFile);
oLibro.Close();
// Eliminamos lo que hemos creado
oExcel.Quit();
oExcel = null;
oLibros = null;
oLibro = null;
oHojas = null;
oHoja = null;
oCeldas = null;
System.GC.Collect();
}
catch
{
oLibro.Close();
oExcel.Quit();
Pmensaje.CssClass = "MensajesSupervisor";
lblencabezado.Text = "Sr. Usuario";
lblmensajegeneral.Text = "Es indispensable que cierre sesión he inicie nuevamente. su sesión expiró.";
Mensajes_Usuario();
}
}
public static void ReadDoc()
{
var time = DateTime.Now;
try
{
App = new Microsoft.Office.Interop.Excel.Application {Visible = true};
Workbook = App.Workbooks.Open("DragonLegendSalesReport-AutoGen", Type.Missing, true, Type.Missing, "19931993");
Worksheet = (Microsoft.Office.Interop.Excel.Worksheet) Workbook.Sheets[1];
var temp = "blah";
const string format = "ddd MMM d, yyyy";
var i = 4;
var strArray = new string[13];
var hasTodayInfo = true;
while (temp != time.ToString(format))
{
var range = Worksheet.Range["B" + i, "M" + i];
var myvalues = (Array) range.Cells.Value;
strArray = myvalues.OfType<object>().Select(x => x.ToString()).ToArray();
if (strArray.Length == 0)
{
//Excel doesn't have today's info yet
hasTodayInfo = false;
break;
}
temp = strArray[0];
i++;
}
if (hasTodayInfo)
{
ReceptionScreen.TotalTicketSold[0] += Convert.ToInt32(strArray[1]);
ReceptionScreen.TotalTicketSold[1] += Convert.ToInt32(strArray[3]);
ReceptionScreen.TotalTicketSold[2] += Convert.ToInt32(strArray[5]);
ReceptionScreen.TotalTicketSold[3] += Convert.ToInt32(strArray[7]);
ReceptionScreen.TotalCutomers[0] += Convert.ToInt32(strArray[2]);
ReceptionScreen.TotalCutomers[1] += Convert.ToInt32(strArray[4]);
ReceptionScreen.TotalCutomers[2] += Convert.ToInt32(strArray[6]);
ReceptionScreen.TotalCutomers[3] += Convert.ToInt32(strArray[8]);
if (Convert.ToInt32(strArray[9]) != ReceptionScreen.TotalTicketSold[0] +
ReceptionScreen.TotalTicketSold[1] +
ReceptionScreen.TotalTicketSold[2] +
ReceptionScreen.TotalTicketSold[3])
{
throw new InvalidDataException();
}
if (Convert.ToInt32(strArray[10]) != ReceptionScreen.TotalCutomers[0] +
ReceptionScreen.TotalCutomers[1] +
ReceptionScreen.TotalCutomers[2] +
ReceptionScreen.TotalCutomers[3])
{
throw new InvalidDataException();
}
App.Quit();
}
else
{
App.Quit();
WriteDoc();
}
}
catch (Exception)
{
App.Quit();
Console.Write(@"File doesn't exist");
SheetAlreadyExist = false;
CreateDoc();
}
}
/// <summary>
/// 复制Map的中文文本
/// </summary>
private void CopyCnMapTxt()
{
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
Microsoft.Office.Interop.Excel.Workbook xBook2 = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet2 = null;
List <string> enSb = new List <string>();
List <string> cnSb = new List <string>();
Dictionary <string, List <string> > txtMap = new Dictionary <string, List <string> >();
try
{
// 创建Application对象
this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// 得到WorkBook对象, 打开已有的文件
xBook = this.xApp.Workbooks._Open(
@"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\mapText_jp.xlsx",
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xBook2 = this.xApp.Workbooks._Open(
@"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\mapText_en.xlsx",
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// 显示进度条
this.ResetProcessBar(xBook.Sheets.Count);
int sheetIndex = -1;
int line = 1;
for (int i = 1; i <= xBook.Sheets.Count; i++)
{
// 更新进度条
this.ProcessBarStep();
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i];
sheetIndex = -1;
for (int j = i; j <= xBook2.Sheets.Count; j++)
{
xSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)xBook2.Sheets[j];
if (xSheet.Name.Equals(xSheet2.Name))
{
sheetIndex = j;
break;
}
}
if (sheetIndex == -1)
{
continue;
}
line = 1;
while (true)
{
Microsoft.Office.Interop.Excel.Range rngJp = xSheet.get_Range("G" + line, Missing.Value);
Microsoft.Office.Interop.Excel.Range rngEn = xSheet2.get_Range("G" + line, Missing.Value);
if (rngEn != null && !string.IsNullOrEmpty(rngEn.Value2 as string))
{
rngJp.Value2 = rngEn.Value2;
line++;
}
else
{
break;
}
}
}
xSheet = null;
xBook = null;
xSheet2 = null;
xBook2 = null;
if (this.xApp != null)
{
this.xApp.Quit();
this.xApp = null;
}
// 隐藏进度条
this.CloseProcessBar();
// 保存
xSheet.SaveAs(
@"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\mapText_jp2.xlsx",
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception me)
{
MessageBox.Show(me.Message);
}
finally
{
// 隐藏进度条
this.CloseProcessBar();
// 清空各种对象
xSheet = null;
xBook = null;
xSheet2 = null;
xBook2 = null;
if (this.xApp != null)
{
this.xApp.Quit();
this.xApp = null;
}
}
}
public static void CreateDoc()
{
App = new Microsoft.Office.Interop.Excel.Application {Visible = true};
Workbook = App.Workbooks.Add(1);
Worksheet = (Microsoft.Office.Interop.Excel.Worksheet) Workbook.Sheets[1];
//creates the main header
CreateHeaders(2, 2, "Sales Report", "B2", "M2", 2, "YELLOW", true, 10, "n");
//creates subheaders
CreateHeaders(3, 2, "Date", "B3", "C3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 4, "TIC:0|2", "D3", "D3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 5, "CUS:0|2", "E3", "E3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 6, "TIC:9|15", "F3", "F3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 7, "CUS:9|15", "G3", "G3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 8, "TIC:17|21", "H3", "H3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 9, "CUS:17|21", "I3", "I3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 10, "TIC:21|24", "J3", "J3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 11, "CUS:21|24", "K3", "K3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 12, "TIC TOTAL", "L3", "L3", 0, "GAINSBORO", true, 10, "");
CreateHeaders(3, 13, "CUS TOTAL", "M3", "M3", 0, "GAINSBORO", true, 10, "");
//add Data to cells
const string format = "ddd MMM d, yyyy";
var time = DateTime.Now;
CreateHeaders(4, 2, "", "B4", "C4", 2, "WHITE", false, 10, "n");
AddData(4, 2, time.ToString(format), "B4", "C4", "");
CreateHeaders(4, 4, "", "D4", "D4", 2, "WHITE", false, 10, "n");
AddData(4, 4, ReceptionScreen.TotalTicketSold[0].ToString(), "D4", "D4", "#,##0");
CreateHeaders(4, 5, "", "E4", "E4", 2, "WHITE", false, 10, "n");
AddData(4, 5, ReceptionScreen.TotalCutomers[0].ToString(), "E4", "E4", "#,##0");
CreateHeaders(4, 6, "", "F4", "F4", 2, "WHITE", false, 10, "n");
AddData(4, 6, ReceptionScreen.TotalTicketSold[1].ToString(), "F4", "F4", "#,##0");
CreateHeaders(4, 7, "", "G4", "G4", 2, "WHITE", false, 10, "n");
AddData(4, 7, ReceptionScreen.TotalCutomers[1].ToString(), "G4", "G4", "#,##0");
CreateHeaders(4, 8, "", "H4", "H4", 2, "WHITE", false, 10, "n");
AddData(4, 8, ReceptionScreen.TotalTicketSold[2].ToString(), "H4", "H4", "#,##0");
CreateHeaders(4, 9, "", "I4", "I4", 2, "WHITE", false, 10, "n");
AddData(4, 9, ReceptionScreen.TotalCutomers[2].ToString(), "I4", "I4", "#,##0");
CreateHeaders(4, 10, "", "J4", "J4", 2, "WHITE", false, 10, "n");
AddData(4, 10, ReceptionScreen.TotalTicketSold[3].ToString(), "J4", "J4", "#,##0");
CreateHeaders(4, 11, "", "K4", "K4", 2, "WHITE", false, 10, "n");
AddData(4, 11, ReceptionScreen.TotalCutomers[3].ToString(), "K4", "K4", "#,##0");
CreateHeaders(4, 12, "", "L4", "L4", 2, "WHITE", false, 10, "n");
var total = ReceptionScreen.TotalTicketSold[0] + ReceptionScreen.TotalTicketSold[1] +
ReceptionScreen.TotalTicketSold[2] + ReceptionScreen.TotalTicketSold[3];
AddData(4, 12, total.ToString(), "L4", "L4", "#,##0");
CreateHeaders(4, 13, "", "M4", "M4", 2, "WHITE", false, 10, "n");
total = ReceptionScreen.TotalCutomers[0] + ReceptionScreen.TotalCutomers[1] +
ReceptionScreen.TotalCutomers[2] + ReceptionScreen.TotalCutomers[3];
AddData(4, 13, total.ToString(), "M4", "M4", "#,##0");
App.DisplayAlerts = false;
Workbook.SaveAs("DragonLegendSalesReport-AutoGen",Type.Missing,"19931993",Type.Missing,true);
App.Quit();
}
/// <summary>
/// 复制Skp的中文文本
/// </summary>
private void CopyCnSkpTxt()
{
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
List <string> enSb = new List <string>();
List <string> cnSb = new List <string>();
Dictionary <string, List <string> > txtMap = new Dictionary <string, List <string> >();
try
{
// 创建Application对象
this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// 得到WorkBook对象, 打开已有的文件
xBook = this.xApp.Workbooks._Open(
@"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\TOS_skp.xls",
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// 取得相应的Sheet
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];
int lineNum = 1;
while (lineNum < 3697)
{
enSb.Add(xSheet.get_Range("B" + lineNum, Missing.Value).Value2 as string);
cnSb.Add(xSheet.get_Range("C" + lineNum, Missing.Value).Value2 as string);
lineNum++;
}
xSheet = null;
xBook = null;
if (this.xApp != null)
{
this.xApp.Quit();
this.xApp = null;
}
//// 创建Application对象
//this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//xBook = this.xApp.Workbooks._Open(
// @"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtText_En.xlsx",
// Missing.Value, Missing.Value, Missing.Value, Missing.Value
// , Missing.Value, Missing.Value, Missing.Value, Missing.Value
// , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//// 显示进度条
//this.ResetProcessBar(xBook.Sheets.Count);
//for (int i = xBook.Sheets.Count; i >= 1; i--)
//{
// // 取得相应的Sheet
// xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i];
// string enTxt = xSheet.get_Range("G1", Missing.Value).Value2 as string;
// if (string.IsNullOrEmpty(enTxt))
// {
// enTxt = string.Empty;
// }
// enTxt = enTxt.Replace("^0a^", "\n").Replace("^0^", string.Empty);
// for (int j = 0; j < enSb.Count; j++)
// {
// if (!string.IsNullOrEmpty(enSb[j]) && !string.IsNullOrEmpty(enTxt) && enSb[j].StartsWith(enTxt, StringComparison.OrdinalIgnoreCase))
// {
// List<string> cnItemSb = new List<string>();
// for (int k = j; k < j + 35 && k < cnSb.Count; k++)
// {
// cnItemSb.Add(cnSb[k]);
// }
// txtMap.Add(xSheet.Name, cnItemSb);
// break;
// }
// }
// // 更新进度条
// this.ProcessBarStep();
//}
//// 隐藏进度条
//this.CloseProcessBar();
//xSheet = null;
//xBook = null;
//if (this.xApp != null)
//{
// this.xApp.Quit();
// this.xApp = null;
//}
// 创建Application对象
this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xBook = this.xApp.Workbooks._Open(
@"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtTextCnEnMap.xlsx",
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// 显示进度条
this.ResetProcessBar(xBook.Sheets.Count);
for (int i = xBook.Sheets.Count; i >= 1; i--)
{
// 取得相应的Sheet
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i];
Microsoft.Office.Interop.Excel.Range rngCn = xSheet.get_Range("L1", Missing.Value);
string line1Val = rngCn.Value2 as string;
rngCn = xSheet.get_Range("L2", Missing.Value);
string line2Val = rngCn.Value2 as string;
rngCn = xSheet.get_Range("L3", Missing.Value);
string line3Val = rngCn.Value2 as string;
rngCn = xSheet.get_Range("L4", Missing.Value);
string line4Val = rngCn.Value2 as string;
rngCn = xSheet.get_Range("L5", Missing.Value);
string line5Val = rngCn.Value2 as string;
int lineNo = -1;
if (!string.IsNullOrEmpty(line1Val) && string.IsNullOrEmpty(line2Val) && string.IsNullOrEmpty(line3Val) && string.IsNullOrEmpty(line4Val) && string.IsNullOrEmpty(line5Val))
{
lineNo = 1;
}
else if (string.IsNullOrEmpty(line1Val) && !string.IsNullOrEmpty(line2Val) && string.IsNullOrEmpty(line3Val) && string.IsNullOrEmpty(line4Val) && string.IsNullOrEmpty(line5Val))
{
lineNo = 2;
}
else if (string.IsNullOrEmpty(line1Val) && string.IsNullOrEmpty(line2Val) && !string.IsNullOrEmpty(line3Val) && string.IsNullOrEmpty(line4Val) && string.IsNullOrEmpty(line5Val))
{
lineNo = 3;
}
else if (string.IsNullOrEmpty(line1Val) && string.IsNullOrEmpty(line2Val) && string.IsNullOrEmpty(line3Val) && !string.IsNullOrEmpty(line4Val) && string.IsNullOrEmpty(line5Val))
{
lineNo = 4;
}
else if (string.IsNullOrEmpty(line1Val) && string.IsNullOrEmpty(line2Val) && string.IsNullOrEmpty(line3Val) && string.IsNullOrEmpty(line4Val) && !string.IsNullOrEmpty(line5Val))
{
lineNo = 5;
}
if (lineNo >= 0)
{
rngCn = xSheet.get_Range("L" + lineNo, Missing.Value);
string lineVal = rngCn.Value2 as string;
int linIndex = 1;
for (int j = 0; j < enSb.Count; j++)
{
if (!string.IsNullOrEmpty(enSb[j]) && enSb[j].IndexOf(lineVal) >= 0)
{
int startPos = j - lineNo + 1;
for (int k = startPos; k < startPos + 30; k++)
{
rngCn = xSheet.get_Range("L" + (linIndex++), Missing.Value);
rngCn.Value2 = cnSb[k];
}
break;
}
}
}
// 更新进度条
this.ProcessBarStep();
}
// 隐藏进度条
this.CloseProcessBar();
// 保存
xSheet.SaveAs(
@"E:\Study\MySelfProject\Hanhua\TodoCn\TalesOfSymphonia\chtTextCnEnMap2.xlsx",
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception me)
{
MessageBox.Show(me.Message);
}
finally
{
// 隐藏进度条
this.CloseProcessBar();
// 清空各种对象
xSheet = null;
xBook = null;
if (this.xApp != null)
{
this.xApp.Quit();
this.xApp = null;
}
}
}
public void WriteExcell(DataSet ds, string Requisition, string FileName, string strSavePath, string FilePath)
{
//Pick up the RFQ format file
// string strPath= Server.MapPath(".") + "\Technical\ExcelFile\RFQ_FormatFile.xls";
// string path = System.AppDomain.CurrentDomain.BaseDirectory + @"RFQ_FormatFile.xls";
string path = FilePath + @"/RFQ_FormatFile.xls";
CheckExcellProcesses();
ExlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
ExlWrkBook = ExlApp.Workbooks.Open(path, 0,
true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
ExlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExlWrkBook.ActiveSheet;
ExlWrkSheet.Cells[1, 3] = ds.Tables[2].Rows[0]["Vessel_name"].ToString();
ExlWrkSheet.Cells[2, 3] = ds.Tables[0].Rows[0]["QUOTATION_CODE"].ToString();
ExlWrkSheet.Cells[3, 3] = ds.Tables[0].Rows[0]["Quotation_Due_Date"].ToString();
ExlWrkSheet.Cells[4, 3] = ds.Tables[0].Rows[0]["SHORT_NAME"].ToString();
ExlWrkSheet.Cells[11, 3] = ds.Tables[0].Rows[0]["System_Description"].ToString();
ExlWrkSheet.Cells[6, 12] = ds.Tables[0].Rows[0]["BUYER_COMMENTS"].ToString();
ExlWrkSheet.Cells[7, 3] = ds.Tables[0].Rows[0]["SHORT_NAME"].ToString();
ExlWrkSheet.Cells[1, 13] = ds.Tables[0].Rows[0]["Vessel_code"].ToString();
ExlWrkSheet.Cells[2, 13] = ds.Tables[0].Rows[0]["DOCUMENT_CODE"].ToString();
ExlWrkSheet.Cells[3, 13] = ds.Tables[0].Rows[0]["ITEM_SYSTEM_CODE"].ToString();
ExlWrkSheet.Cells[5, 13] = DateTime.Now.ToString("yyyy/MM/dd");
ExlWrkSheet.Cells[6, 13] = ds.Tables[0].Rows[0]["Quotation_CODE"].ToString();
ExlWrkSheet.Cells[7, 13] = ds.Tables[0].Rows[0]["QUOTATION_SUPPLIER"].ToString();
ExlWrkSheet.Cells[12, 3] = ds.Tables[0].Rows[0]["System_Description"].ToString();
if (ds.Tables[4].Rows.Count > 0)
{
ExlWrkSheet.Cells[10, 7] = ds.Tables[4].Rows[0]["MechInfo"].ToString();
ExlWrkSheet.Cells[11, 7] = ds.Tables[4].Rows[0]["Model_Type"].ToString();
ExlWrkSheet.Cells[12, 7] = ds.Tables[4].Rows[0]["MakerName"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerAddress"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerCity"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerEmail"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerCONTACT"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerPhone"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerFax"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerTELEX"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["System_Serial_Number"].ToString();
}
Microsoft.Office.Interop.Excel.Range xlsRange;
int i = 15;
foreach (DataRow dr in ds.Tables[1].Rows)
{
//S.nO.
ExlWrkSheet.Cells[i, 1] = dr["ID"].ToString();
//DRAWING NO.
ExlWrkSheet.Cells[i, 2] = dr["Drawing_Number"].ToString();
//PART NO
ExlWrkSheet.Cells[i, 3] = dr["Part_Number"].ToString();
//Item Ref Code
ExlWrkSheet.Cells[i, 4] = dr["ITEM_REF_CODE"].ToString();
//Item
ExlWrkSheet.Cells[i, 5] = dr["Short_Description"].ToString();
ExlWrkSheet.Cells[i + 1, 5] = dr["Long_Description"].ToString();
ExlWrkSheet.Cells[i + 2, 5] = dr["ITEM_COMMENT"].ToString();
ExlWrkSheet.Cells[i, 6] = dr["Unit_and_Packings"].ToString();
ExlWrkSheet.Cells[i, 7] = dr["REQUESTED_QTY"].ToString();
i = i + 3;
}
ExlWrkSheet.get_Range("A" + (ds.Tables[1].Rows.Count * 3 + 15).ToString(), "N1639").Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
ExlWrkSheet.Cells[ds.Tables[1].Rows.Count * 3 + 15, 1] = ds.Tables[3].Rows[0]["LegalTerm"].ToString();
//ExlWrkSheet.get_Range("F15", "H" + (ds.Tables[1].Rows.Count * 3 + 14).ToString()).Locked = false;
//ExlWrkSheet.get_Range("J15", "J" + (ds.Tables[1].Rows.Count * 3 + 14).ToString()).Locked = false;
//ExlWrkSheet.get_Range("I7", "I9").Locked = false;
//ExlWrkSheet.get_Range("I10", "K11").Locked = false;
ExlWrkSheet.get_Range("G9", "G9").NumberFormat = "#0.00";
//ExlWrkSheet.get_Range("I5", "I6").Locked = false;
//ExlWrkSheet.get_Range("L1", "M10").EntireColumn.Hidden = true;
ExlWrkSheet.get_Range("M1", "M10").EntireColumn.Hidden = true;
ExlWrkSheet.Protect("tessmave", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing);
ExlWrkBook.SaveAs(strSavePath + FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
string s = Server.MapPath("~");
string destFile = Server.MapPath("Uploads/Purchase") + "\\" + FileName;;
File.Copy(strSavePath + FileName, destFile, true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
ExlWrkBook.Close(null, null, null);
//ExlApp.Workbooks.Close();
ExlApp.Quit();
KillExcel();
}
}
public bool TableToExcel(String strfilename, DataGridView dgv)
{
try
{
Microsoft.Office.Interop.Excel.Application App = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook book = App.Workbooks.Open(strfilename, 0, true, 5, "", "", false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets _sheet = (Microsoft.Office.Interop.Excel.Sheets)book.Sheets;
Microsoft.Office.Interop.Excel.Worksheet wrksheet =
(Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
int h = 1;
foreach (DataGridViewRow dr in dgv.Rows)
{
wrksheet.Cells[h + 2, 1] = h;
wrksheet.Cells[h + 2, 2] = dr.Cells[0].Value != null ? dr.Cells[0].Value.ToString() : "";
wrksheet.Cells[h + 2, 3] = dr.Cells[1].Value != null ? dr.Cells[1].Value.ToString() : "";
wrksheet.Cells[h + 2, 4] = dr.Cells[2].Value != null ? dr.Cells[2].Value.ToString() : "";
wrksheet.Cells[h + 2, 5] = dr.Cells[3].Value != null ? dr.Cells[3].Value.ToString() : "";
wrksheet.Cells[h + 2, 6] = dr.Cells[4].Value != null ? dr.Cells[4].Value.ToString() : "";
wrksheet.Cells[h + 2, 7] = dr.Cells[5].Value != null ? dr.Cells[5].Value.ToString() : "";
wrksheet.Cells[h + 2, 8] = dr.Cells[6].Value != null ? dr.Cells[6].Value.ToString() : "";
h++;
}
string savePath = "";
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Title = "Save Excel Files";
saveFileDialog1.Filter = "Excel files (*.xls)|*.xls";
//if (xtraTabControl1.SelectedTabPage==tp_DeSo_Loai1)
//{
// saveFileDialog1.FileName = "NangSuat_DeSo_Loai1_" + dtp_FirstDay.Value.Day + "-" + dtp_EndDay.Value.Day;
//}
if (xtraTabControl1.SelectedTabPage == tp_DeJP)
{
saveFileDialog1.FileName = "NangSuat_DeJP_" + dtp_FirstDay.Value.Day + "-" + dtp_EndDay.Value.Day;
}
else if (xtraTabControl1.SelectedTabPage == tp_DeSo_Loai2)
{
saveFileDialog1.FileName = "NangSuat_DeSo_Loai2_" + dtp_FirstDay.Value.Day + "-" + dtp_EndDay.Value.Day;
}
//else if (xtraTabControl1.SelectedTabPage==tp_DeSo_Loai3)
//{
// saveFileDialog1.FileName = "NangSuat_DeSo_Loai3_" + dtp_FirstDay.Value.Day + "-" + dtp_EndDay.Value.Day;
//}
else
{
saveFileDialog1.FileName = "NangSuat_DeSo_Loai4_" + dtp_FirstDay.Value.Day + "-" + dtp_EndDay.Value.Day;
}
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
book.SaveCopyAs(saveFileDialog1.FileName);
book.Saved = true;
savePath = Path.GetDirectoryName(saveFileDialog1.FileName);
App.Quit();
}
else
{
MessageBox.Show("Lỗi khi xuất excel!");
return(false);
}
Process.Start(savePath);
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
private void buttonX_ok_Click(object sender, EventArgs e)
{
//add all point
List <IPoint> pointList = new List <IPoint>();
Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;
myExcel.Application.Workbooks.Open(textBoxX1.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //this.txtFile.Text为Excel文件的全路径
Microsoft.Office.Interop.Excel.Workbook myBook = myExcel.Workbooks[1];
//获取第一个Sheet
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)myBook.Sheets[1];
string sheetName = sheet.Name; //Sheet名
myBook.Close(Type.Missing, Type.Missing, Type.Missing);
myExcel.Quit();
//read excel file to creat Field
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + this.textBoxX1.Text + ";Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(strCon);
string sql1 = string.Format("select * from [{0}$]", sheetName);
conn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(sql1, strCon);
DataSet ds = new DataSet();
myCommand.Fill(ds);
conn.Close();
int xIndex = 0;
int yIndex = 0;
int zIndex = 0;
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
if (ds.Tables[0].Columns[i].ColumnName == "X_经度")
{
xIndex = i;
}
if (ds.Tables[0].Columns[i].ColumnName == "Y_纬度")
{
yIndex = i;
}
if (ds.Tables[0].Columns[i].ColumnName.Contains("Z_高程"))
{
zIndex = i;
}
}
ISpatialReference pSpaReference = new UnknownCoordinateSystemClass();
pSpaReference.SetDomain(-8000000, 8000000, -800000, 8000000);
IFeatureClass pFt = CreateShapeFile(ds, this.textBoxX2.Text, pSpaReference);
if (pFt == null)
{
return;
}
else
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//根据XY坐标添加点,edit attribute
IsNumberic isNum = new IsNumberic();
ESRI.ArcGIS.Geometry.IPoint pPoint = new ESRI.ArcGIS.Geometry.PointClass();
if (ds.Tables[0].Rows[i][xIndex].ToString() == "" || ds.Tables[0].Rows[i][xIndex].ToString() == " ")
{
break;
}
if (isNum.IsNumber(ds.Tables[0].Rows[i][xIndex].ToString()) && isNum.IsNumber(ds.Tables[0].Rows[i][yIndex].ToString()))
{
pPoint.X = System.Convert.ToSingle(ds.Tables[0].Rows[i][xIndex].ToString());
pPoint.Y = System.Convert.ToSingle(ds.Tables[0].Rows[i][yIndex].ToString());
pPoint.Z = System.Convert.ToSingle(ds.Tables[0].Rows[i][zIndex].ToString());
IFeature pFeature = pFt.CreateFeature();
pFeature.Shape = pPoint;
pFeature.Store();
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
if (ds.Tables[0].Columns[j].ColumnName.Contains("里程"))
{
continue;
}
//pFeature.set_Value(pFeature.Fields.FindField(ds.Tables[0].Columns[j].ColumnName), ds.Tables[0].Rows[i][j]);
pFeature.set_Value(j + 2, ds.Tables[0].Rows[i][j].ToString());
}
pFeature.Store();
pointList.Add(pPoint);
}
else
{
MessageBox.Show("the" + i + "rows x and y value is unvalid!");
}
}
}
ClsGDBDataCommon processDataCommon = new ClsGDBDataCommon();
string strInputPath = System.IO.Path.GetDirectoryName(textBoxX2.Text);
string strInputName = System.IO.Path.GetFileName(textBoxX2.Text);
IFeatureLayer pFeatureLayer = new FeatureLayerClass();
pFeatureLayer.FeatureClass = pFt;
pFeatureLayer.Name = System.IO.Path.GetFileNameWithoutExtension(System.IO.Path.GetFileNameWithoutExtension(strInputName));
//create line shape file
IPointCollection PointCollection = ReadPoint(pFeatureLayer);
string lineName = strInputPath + "\\" + System.IO.Path.GetFileNameWithoutExtension(strInputName) + "_line.shp";
CreateLineShpFile(lineName, pSpaReference);
//将所有的点连接成线
List <IPolyline> Polyline = CreatePolyline(PointCollection);
List <double> lineLengthList = new List <double>();
//将连接成的线添加到线图层中
string pLineFile = lineName;
string pFilePath = System.IO.Path.GetDirectoryName(pLineFile);
string pFileName = System.IO.Path.GetFileName(pLineFile);
//打开工作空间
IWorkspaceFactory pWSF = new ShapefileWorkspaceFactoryClass();
IFeatureWorkspace pWS = (IFeatureWorkspace)pWSF.OpenFromFile(pFilePath, 0);
//写入实体对象
IFeatureLayer plineLayer = new FeatureLayerClass();
plineLayer.FeatureClass = pWS.OpenFeatureClass(pFileName);
AddFeature(plineLayer, Polyline, pointList, lineLengthList);
plineLayer.Name = pFeatureLayer.Name + "_line";
m_pMapCtl.AddLayer(plineLayer as ILayer, 0);
m_pMapCtl.AddLayer(pFeatureLayer as ILayer, 0);
m_pMapCtl.ActiveView.PartialRefresh(esriViewDrawPhase.esriViewGeography, null, null);
ImpSymbolFromFile(textBoxX3.Text, pFeatureLayer, plineLayer);
this.Close();
}
/// <summary>
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
///
public static void DataGridViewToExcel(DataGridView dgv)
{
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默认文件后缀
dlg.DefaultExt = "xlsx";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLSX)|*.xlsx ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
Microsoft.Office.Interop.Excel.Application objExcel = null;
Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
int displayColumnsCount = 1;
for (int i = 0; i <= dgv.ColumnCount - 1; i++)
{
if (dgv.Columns[i].Visible == true)
{
objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
//设置进度条
//progressBar1.Refresh();
//progressBar1.Visible = true;
//progressBar1.Minimum = 1;
//progressBar1.Maximum = dgv.RowCount;
//progressBar1.Step = 1;
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
//this.progressBar1.PerformStep();
//this.label2.Text = (this.progressBar1.Value / this.progressBar1.Maximum) + "%";
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
//隐藏进度条
//tempProgressBar.Visible = false;
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
