private void ExportToExcel(DataGridView dgView)
{
Microsoft.Office.Interop.Excel.Application excelApp = null;
try
{
// instantiating the excel application class
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;
currentWorksheet.Columns.ColumnWidth = 18;
if (dgView.Rows.Count > 0)
{
currentWorksheet.Cells[1, 1] = cmbSubject.SelectedItem.ToString();
int i = 1;
foreach (DataGridViewColumn dgviewColumn in dgView.Columns)
{
// Excel work sheet indexing starts with 1
currentWorksheet.Cells[2, i] = dgviewColumn.Name;
++i;
}
Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A2", "T2");
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0xFF0000;
//headerColumnRange.EntireColumn.AutoFit();
int rowIndex = 0;
for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++)
{
DataGridViewRow dgRow = dgView.Rows[rowIndex];
for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
{
currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = dgRow.Cells[cellIndex].Value;
}
}
Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 1).ToString());
fullTextRange.WrapText = true;
fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
else
{
currentWorksheet.Cells[1, 1] = tcpserver.subject;
}
using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
{
exportSaveFileDialog.Title = "Select Excel File";
exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx";
if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
{
string fullFileName = exportSaveFileDialog.FileName;
// currentWorkbook.SaveCopyAs(fullFileName);
// indicating that we already saved the workbook, otherwise call to Quit() will pop up
// the save file dialogue box
currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value);
currentWorkbook.Saved = true;
MetroMessageBox.Show(this, "Database exported successfully", "Exported to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (excelApp != null)
{
excelApp.Quit();
}
}
}
private List <Point3d> pegaDadosExcel(string nomeArquivo, double fatorAmpliacao)
{
string nomeWorksheet = "Lista";
List <Point3d> listaDados = new List <Point3d>();
OleDbConnection conexao = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + nomeArquivo + ";Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1'");
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + nomeWorksheet + "]", conexao);
DataSet ds = new DataSet();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application
{
Visible = false
};
Microsoft.Office.Interop.Excel.Workbook wbExcel = excelApp.Workbooks.Open(nomeArquivo, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);
Microsoft.Office.Interop.Excel.Worksheet activeSheet = (Microsoft.Office.Interop.Excel.Worksheet)wbExcel.ActiveSheet;
Microsoft.Office.Interop.Excel.Worksheet wsPlanilha = (Microsoft.Office.Interop.Excel.Worksheet)wbExcel.Worksheets.get_Item(1);
try
{
double X = 0;
double Y = 0;
double Z = 0;
string sX = wsPlanilha.get_Range("E2", "E2").Text;
string sY = wsPlanilha.get_Range("F2", "F2").Text;
string sZ = wsPlanilha.get_Range("G2", "G2").Text;
string colX = "E2";
string colY = "F2";
string colZ = "G2";
int linha = 2;
int indicador = 0;
Point3d ponto = new Point3d(0, 0, 0);
while (indicador == 0)
{
sX = wsPlanilha.get_Range(colX, colX).Text;
if (sX == null || sX == "")
{
break;
}
sY = wsPlanilha.get_Range(colY, colY).Text;
if (sY == null || sY == "")
{
break;
}
sZ = wsPlanilha.get_Range(colZ, colZ).Text;
if (sZ == null || sZ == "")
{
break;
}
X = ConverterParaNumero(sX);
Y = ConverterParaNumero(sY);
Z = fatorAmpliacao * ConverterParaNumero(sZ);
ponto = new Point3d(X, Y, Z);
listaDados.Add(ponto);
linha++;
colX = string.Concat("E", linha.ToString());
colY = string.Concat("F", linha.ToString());
colZ = string.Concat("G", linha.ToString());
}
//listaDados = OrdenarListaPorAbcissa(listaDados);
}
catch (Exception ex)
{
MessageBox.Show("Erro ao acessar arquivo: " + ex.Message);
}
excelApp.Workbooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
return(listaDados);
}
/// <summary>
/// 加载数据
/// </summary>
/// <param name="obj"></param>
private void AddData(FarPoint.Win.Spread.FpSpread obj)
{
WaitDialogForm wait = null;
wait = new WaitDialogForm("", "正在加载数据, 请稍候...");
try
{
//打开Excel表格
//清空工作表
fpSpread1.Sheets.Clear();
obj.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet2_1.xls");
PF.SpreadRemoveEmptyCells(obj);
this.barEditItem1.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
for (int i = 0; i < fpSpread1.Sheets.Count; ++i)
{
fpSpread1.Sheets[i].CellChanged += new FarPoint.Win.Spread.SheetViewEventHandler(FrmSheet2_1_CellChanged);
}
}
catch (System.Exception e)
{
//如果打开出错则重新生成并保存
LoadData();
//判断文件夹是否存在，不存在则创建
if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls"))
{
Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls");
}
//保存EXcel文件
obj.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet2_1.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet);
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet2_1.xls";
workBook = excelApp.Workbooks.Open(filename, 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);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//取消保护工作表
ws.Unprotect(Missing.Value);
//有数据的行数
int row = ws.UsedRange.Rows.Count;
//有数据的列数
int col = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//设区域内的单元格自动换行
range.WrapText = true;
//保护工作表
ws.Protect(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, Missing.Value, Missing.Value);
}
//保存工作簿
workBook.Save();
//关闭工作簿
excelApp.Workbooks.Close();
}
wait.Close();
}
//
// GET: /Forecast/Excel
public ActionResult Excel(int YYYY = -1, int MM = -1, int DD = -1)
{
try
{
DateTime currDate;
try
{
currDate = new DateTime(YYYY, MM, DD);
}
catch
{
currDate = DateTime.Now;
}
int[] HydroPostCodeArray = { 6005, 6010, 6016, 6022, 6024, 6027, 6030, 5002, 5004,
5012, 5016, 5019, 5020, 5024, 6280, 6286, 6291, 6295,6364, 6369 };
BorshchForecast theModel = new BorshchForecast(currDate);
foreach (var j in HydroPostCodeArray)
{
River theRiver = River.GetByDate(currDate, j);
if (theRiver == null)
{
theRiver = new River();
theRiver.HydroPostCode = j;
}
theModel.theRiverList.Add(theRiver);
}
theModel.theReservoirCOSMO = Reservoir.GetByDate(currDate, "COSMO");
if (theModel.theReservoirCOSMO == null)
{
theModel.theReservoirCOSMO = new Reservoir();
}
theModel.theReservoirJMA = Reservoir.GetByDate(currDate, "JMA");
if (theModel.theReservoirJMA == null)
{
theModel.theReservoirJMA = new Reservoir();
}
theModel.theReservoirNCEP = Reservoir.GetByDate(currDate, "NCEP");
if (theModel.theReservoirNCEP == null)
{
theModel.theReservoirNCEP = new Reservoir();
}
theModel.theReservoirUKMO = Reservoir.GetByDate(currDate, "UKMO");
if (theModel.theReservoirUKMO == null)
{
theModel.theReservoirUKMO = new Reservoir();
}
string nameExcel = HttpContext.Server.MapPath("~/App_Data/");
string fileName = "BorshchForecast-" + theModel.forecastDate.ToString("yyyy-MM-dd") + ".xls";
string fileNameTemp = string.Format(@"{0}.xls", Guid.NewGuid());
nameExcel += fileNameTemp;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
throw new Exception("Не удалось создать объект Excel");
}
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 1;
xlWorkSheet.get_Range("a1", "m2").Merge(false);
var chartRange = xlWorkSheet.get_Range("a1", "m2");
chartRange.FormulaR1C1 = "Фактические уровни воды в бассейне р.Амур на " + theModel.forecastDate.ToString("dd.MM.yyyy") +
" года и \nпрогноз уровней воды (в см над нулем графика поста) на " +
theModel.forecastDate.AddDays(1).ToString("dd.MM") + " - " +
theModel.forecastDate.AddDays(6).ToString("dd.MM.yyyy") + " года";
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
i++;
i++;
xlWorkSheet.Cells[i, 1] = "Индекс";
xlWorkSheet.Cells[i, 2] = "Река - Пункт";
xlWorkSheet.Cells[i, 3] = "Пойма";
xlWorkSheet.Cells[i, 4] = "НЯ, см";
xlWorkSheet.Cells[i, 5] = "ОЯ, см";
xlWorkSheet.Cells[i, 6] = "Н факт, см";
xlWorkSheet.Cells[i, 7] = "Н прогноз " + theModel.forecastDate.AddDays(1).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 8] = "Н прогноз " + theModel.forecastDate.AddDays(2).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 9] = "Н прогноз " + theModel.forecastDate.AddDays(3).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 10] = "Н прогноз " + theModel.forecastDate.AddDays(4).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 11] = "Н прогноз " + theModel.forecastDate.AddDays(5).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 12] = "Н прогноз " + theModel.forecastDate.AddDays(6).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 13] = "\"0\" графика поста";
foreach (var river in theModel.theRiverList)
{
i++;
xlWorkSheet.Cells[i, 1] = river.HydroPostCode.ToString();
xlWorkSheet.Cells[i, 2] = river.Gauge.ToString();
xlWorkSheet.Cells[i, 3] = river.FloodPlaneMark.ToString();
xlWorkSheet.Cells[i, 4] = river.AdverseFact.ToString();
xlWorkSheet.Cells[i, 5] = river.DangerFact.ToString();
xlWorkSheet.Cells[i, 6] = river.Level_obs.ToString();
xlWorkSheet.Cells[i, 7] = river.Level_for1.ToString();
xlWorkSheet.Cells[i, 8] = river.Level_for2.ToString();
xlWorkSheet.Cells[i, 9] = river.Level_for3.ToString();
xlWorkSheet.Cells[i, 10] = river.Level_for4.ToString();
xlWorkSheet.Cells[i, 11] = river.Level_for5.ToString();
xlWorkSheet.Cells[i, 12] = river.Level_for6.ToString();
xlWorkSheet.Cells[i, 13] = river.Height.ToString();
}
i += 2;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Нуль поста - высота отметки нуля графика поста в м Б.С.";
i += 2;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Нф - фактический уровень воды на 8-00 местного времени по сотоянию " + theModel.forecastDate.ToString("dd.MM.yyyy");
i += 2;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Критические значения уровня воды в см над нулем графика поста:";
i += 1;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Пойма - уровень, при котором происходит выход воды на пойму";
i += 1;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "НЯ - отметка неблагоприятного явления";
i += 1;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "ОЯ - отметка опасного явления";
i += 2;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Дата выпуска прогноза: " + theModel.forecastDate.ToShortDateString();
// Зейское водохранилище
i += 3;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Прогноз суточного притока воды к Зейскому водохранилищу (куб. м/с) на " +
theModel.forecastDate.AddDays(1).ToString("dd.MM") + " - " +
theModel.forecastDate.AddDays(6).ToString("dd.MM.yyyy");
i += 1;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Приток фактический на " + theModel.forecastDate.ToString("dd.MM.yyyy");
i += 1;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Q в/б = " + theModel.forecastDate.ToString("dd.MM.yyyy") + " куб м/с";
i += 1;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Q г/м = " + theModel.forecastDate.ToString("dd.MM.yyyy") + " куб м/с";
i += 1;
xlWorkSheet.Cells[i, 1] = "Модель";
xlWorkSheet.Cells[i, 2] = "Q прогноз " + theModel.forecastDate.AddDays(1).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 3] = "Q прогноз " + theModel.forecastDate.AddDays(2).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 4] = "Q прогноз " + theModel.forecastDate.AddDays(3).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 5] = "Q прогноз " + theModel.forecastDate.AddDays(4).ToString("dd.MM.yy");
xlWorkSheet.Cells[i, 6] = "Q прогноз " + theModel.forecastDate.AddDays(5).ToString("dd.MM.yy");
i += 1;
xlWorkSheet.Cells[i, 1] = theModel.theReservoirCOSMO.MeteoModel;
xlWorkSheet.Cells[i, 2] = theModel.theReservoirCOSMO.Inflow_for1;
xlWorkSheet.Cells[i, 3] = theModel.theReservoirCOSMO.Inflow_for2;
xlWorkSheet.Cells[i, 4] = theModel.theReservoirCOSMO.Inflow_for3;
xlWorkSheet.Cells[i, 5] = theModel.theReservoirCOSMO.Inflow_for4;
xlWorkSheet.Cells[i, 6] = theModel.theReservoirCOSMO.Inflow_for5;
i += 1;
xlWorkSheet.Cells[i, 1] = theModel.theReservoirNCEP.MeteoModel;
xlWorkSheet.Cells[i, 2] = theModel.theReservoirNCEP.Inflow_for1;
xlWorkSheet.Cells[i, 3] = theModel.theReservoirNCEP.Inflow_for2;
xlWorkSheet.Cells[i, 4] = theModel.theReservoirNCEP.Inflow_for3;
xlWorkSheet.Cells[i, 5] = theModel.theReservoirNCEP.Inflow_for4;
xlWorkSheet.Cells[i, 6] = theModel.theReservoirNCEP.Inflow_for5;
i += 1;
xlWorkSheet.Cells[i, 1] = theModel.theReservoirUKMO.MeteoModel;
xlWorkSheet.Cells[i, 2] = theModel.theReservoirUKMO.Inflow_for1;
xlWorkSheet.Cells[i, 3] = theModel.theReservoirUKMO.Inflow_for2;
xlWorkSheet.Cells[i, 4] = theModel.theReservoirUKMO.Inflow_for3;
xlWorkSheet.Cells[i, 5] = theModel.theReservoirUKMO.Inflow_for4;
xlWorkSheet.Cells[i, 6] = theModel.theReservoirUKMO.Inflow_for5;
i += 1;
xlWorkSheet.Cells[i, 1] = theModel.theReservoirJMA.MeteoModel;
xlWorkSheet.Cells[i, 2] = theModel.theReservoirJMA.Inflow_for1;
xlWorkSheet.Cells[i, 3] = theModel.theReservoirJMA.Inflow_for2;
xlWorkSheet.Cells[i, 4] = theModel.theReservoirJMA.Inflow_for3;
xlWorkSheet.Cells[i, 5] = theModel.theReservoirJMA.Inflow_for4;
xlWorkSheet.Cells[i, 6] = theModel.theReservoirJMA.Inflow_for5;
i += 2;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "COSMO - модель COSMO (Россия)";
i += 1;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "NCEP - модель Национального центра прогнозов (США)";
i += 1;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "UKMO - модель Метеорологического бюро Великобритании";
i += 1;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "JMA - модель Японского метеорологического центра";
i += 2;
xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString()).Merge(false);
chartRange = xlWorkSheet.get_Range("a" + i.ToString(), "m" + i.ToString());
xlWorkSheet.Cells[i, 1] = "Дата выпуска прогноза: " + theModel.forecastDate.ToShortDateString();
xlWorkBook.SaveAs(nameExcel);
xlWorkBook.Close(true);
xlApp.Quit();
byte[] fileBytes = System.IO.File.ReadAllBytes(nameExcel);
System.IO.File.Delete(nameExcel);
return(File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName));
}
catch (Exception ex)
{
ViewBag.Error = ex.Message + "\n" + ex.StackTrace;
return(View());
}
}
private void btneExcelReport_Click(object sender, EventArgs e)
{
try
{
DataTable dtAccount = new DataTable();
//Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
//Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//xlSheets = ExcelApp.Sheets;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheetToUpload = default(Microsoft.Office.Interop.Excel.Worksheet);
// xlWorkSheetToUpload = ExcelApp.Sheets["Sheet1"];
ExcelApp.Visible = true;
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorkSheetToUpload = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
System.Data.DataSet gstR3B = gstR3BController.getGSTR3BReport(Convert.ToDateTime(dtpFrom.Value.ToShortDateString()), Convert.ToDateTime(dtpTo.Value.ToShortDateString()), Utility.FinancilaYearId, "GSTR3B");
dtAccount = gstR3B.Tables[0];
dtAccount.TableName = "GST 3B Report";
xlWorkSheetToUpload.Name = dtAccount.TableName;
xlWorkSheetToUpload.Cells[8, 2] = "3.1 Details of Outward Supplies and inward supplies liable to reverse charge";
xlWorkSheetToUpload.Range["B8:G8"].MergeCells = true;
int iRowCnt = 10;
xlWorkSheetToUpload.Cells[iRowCnt - 1, 2] = "Nature of Supplies";
xlWorkSheetToUpload.Cells[iRowCnt - 1, 3] = "Total Taxable Value";
xlWorkSheetToUpload.Cells[iRowCnt - 1, 4] = "Integrated Tax";
xlWorkSheetToUpload.Cells[iRowCnt - 1, 5] = "Central Tax";
xlWorkSheetToUpload.Cells[iRowCnt - 1, 6] = "State UT Tax";
xlWorkSheetToUpload.Cells[iRowCnt - 1, 7] = "Cess";
// SHOW THE EXCEL SHEET.
// SETTING IT VISIBLE WILL ALLOW YOU TO SEE HOW IT WRITES DATA TO EACH CELL.
// Storing Each row and column value to excel sheet
for (var i = 0; i <= dtAccount.Rows.Count - 1; i++)
{
xlWorkSheetToUpload.Cells[iRowCnt, 2] = dtAccount.Rows[i]["NatureofSupplies"];
xlWorkSheetToUpload.Cells[iRowCnt, 3] = dtAccount.Rows[i]["TotalTaxableValue"];
xlWorkSheetToUpload.Cells[iRowCnt, 4] = dtAccount.Rows[i]["IntegratedTax"];
xlWorkSheetToUpload.Cells[iRowCnt, 5] = dtAccount.Rows[i]["CentralTax"];
xlWorkSheetToUpload.Cells[iRowCnt, 6] = dtAccount.Rows[i]["StateUTTax"];
xlWorkSheetToUpload.Cells[iRowCnt, 7] = dtAccount.Rows[i]["Cess"];
iRowCnt = iRowCnt + 1;
}
xlWorkSheetToUpload.Cells[18, 2] = "4. Eligible ITC";
xlWorkSheetToUpload.Range["B18:F18"].MergeCells = true;
xlWorkSheetToUpload.Cells[19, 2] = "Details";
xlWorkSheetToUpload.Cells[19, 3] = "Integrated Tax";
xlWorkSheetToUpload.Cells[19, 4] = "Central Tax";
xlWorkSheetToUpload.Cells[19, 5] = "State UT Tax";
xlWorkSheetToUpload.Cells[19, 6] = "Cess";
xlWorkSheetToUpload.Cells[20, 2] = "1";
xlWorkSheetToUpload.Cells[20, 3] = "2";
xlWorkSheetToUpload.Cells[20, 4] = "3";
xlWorkSheetToUpload.Cells[20, 5] = "4";
xlWorkSheetToUpload.Cells[20, 6] = "5";
// Storing Each row and column value to excel sheet
int newRow = 21;
for (var i = 0; i <= gstR3B.Tables[1].Rows.Count - 1; i++)
{
xlWorkSheetToUpload.Cells[newRow, 2] = gstR3B.Tables[1].Rows[i]["Details"];
xlWorkSheetToUpload.Cells[newRow, 3] = gstR3B.Tables[1].Rows[i]["IntegratedTax"];
xlWorkSheetToUpload.Cells[newRow, 4] = gstR3B.Tables[1].Rows[i]["CentralTax"];
xlWorkSheetToUpload.Cells[newRow, 5] = gstR3B.Tables[1].Rows[i]["StateUTTax"];
xlWorkSheetToUpload.Cells[newRow, 6] = gstR3B.Tables[1].Rows[i]["Cess"];
newRow = newRow + 1;
}
Microsoft.Office.Interop.Excel.Range formatRange;
formatRange = xlWorkSheetToUpload.get_Range("B18:F18");
xlWorkSheetToUpload.get_Range("B8:G8").Font.Bold = true;
xlWorkSheetToUpload.get_Range("B8:G8").Font.Size = 13;
xlWorkSheetToUpload.get_Range("B9:G9").Font.Size = 11;
xlWorkSheetToUpload.get_Range("B9:G9").Font.Bold = true;
// formatRange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheetToUpload.Columns.AutoFit();
ExcelApp = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 导出文本
/// </summary>
private void ExoprtText()
{
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
Microsoft.Office.Interop.Excel.Worksheet beforeSheet = null;
// 设定保存的文件名
string fileName = this.baseFile;
if (string.IsNullOrEmpty(fileName))
{
fileName = @"TextExport.xlsx";
}
// 先删除原来的文件
if (File.Exists(fileName))
{
File.Delete(fileName);
}
// 显示进度条
this.ResetProcessBar(this.textFiles.Count);
try
{
// 创建Application对象
this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// 追加一个WorkBook
xBook = this.xApp.Workbooks.Add(Missing.Value);
for (int j = 0; j < this.textFiles.Count; j++)
{
// 追加一个Sheet
FilePosInfo filePosInfo = this.textFiles[j];
string jpText = string.Empty;
string cnText = string.Empty;
this.Invoke((MethodInvoker) delegate()
{
// 更新当前文本
this.fileList.SelectedIndex = j;
// 取得日文、中文文本
jpText = this.txtJp.Text;
cnText = this.txtCn.Text;
});
// 设置当前Sheet名（如果有重复的就累加编号）
string sheetName = Util.GetShortFileName(filePosInfo.File);
int sameNameCount = 0;
for (int i = 0; i < j; i++)
{
if (Util.GetShortFileName(this.textFiles[i].File).IndexOf(sheetName) >= 0)
{
sameNameCount++;
}
}
if (beforeSheet == null)
{
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets.Add(beforeSheet, Missing.Value, Missing.Value, Missing.Value);
}
xSheet.Name = sheetName + (sameNameCount > 0 ? "_" + sameNameCount.ToString() : string.Empty);
beforeSheet = xSheet;
// 将每行文本保存到Sheet中
string[] jpTexts = jpText.Split('\n');
string[] cnTexts = cnText.Split('\n');
for (int i = 0; i < jpTexts.Length; i++)
{
// 写入日文文本
Microsoft.Office.Interop.Excel.Range rngJp = xSheet.get_Range("A" + (i + 1), Missing.Value);
rngJp.Value2 = jpTexts[i];
}
for (int i = 0; i < cnTexts.Length; i++)
{
// 写入中文文本
Microsoft.Office.Interop.Excel.Range rngCn = xSheet.get_Range("G" + (i + 1), Missing.Value);
rngCn.Value2 = cnTexts[i];
}
// 更新进度条
this.ProcessBarStep();
}
}
finally
{
// 保存
xSheet.SaveAs(
fileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// 隐藏进度条
this.CloseProcessBar();
// 清空各种对象
xSheet = null;
xBook = null;
if (this.xApp != null)
{
this.xApp.Quit();
this.xApp = null;
}
// 显示保存完成信息
MessageBox.Show("导出完成！");
}
}
private void buttonOst_Click(object sender, EventArgs e)
{
//массив0 квитанции
refreshOst();
int[,] arr = new int [listViewKvit.Items.Count, listViewKvit.Columns.Count];
for (int x = 0, maxX = listViewKvit.Items.Count; x < maxX; x++)
{
for (int y = 0, maxY = listViewKvit.Columns.Count; y < maxY; y++)
{
arr[x, y] = int.Parse(listViewKvit.Items[x].SubItems[y].Text.Trim());
}
}
//массив1 журнал
refreshOst1();
int[] arr1 = new int[listViewKvit.Items.Count];
for (int x = 0, maxX = listViewKvit.Items.Count; x < maxX; x++)
{
arr1[x] = int.Parse(listViewKvit.Items[x].Text.Trim());
}
string[,] b = new string [10, 10];
refreshKvit();
List <int> ARR = new List <int>();
List <int> ARR1 = new List <int>();
List <int> ARR2 = new List <int>();
List <int> ARR3 = new List <int>();
List <object> unique = new List <object>();
List <object> unique2 = new List <object>();
//диапазоны из нач/кон значений квитанции
for (int x = 0; x <= ((arr.Length / 2) - 1); x++)
{
for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++)
{
ARR.Add(i);
}
}
//журнал
foreach (int g in arr1)
{
ARR1.Add(g);
}
//удаление того, что есть в журнале, но нет в билетах
for (int x = 0; x < ARR.Count; x++)
{
for (int y = 0; y < ARR1.Count; y++)
{
if (ARR[x].Equals(ARR1[y]))
{
ARR3.Add(ARR[x]);
}
}
}
//объединение списков
ARR2.AddRange(ARR.ToArray());
ARR2.AddRange(ARR3.ToArray());
ARR2.Sort();
//удаление двойных
for (int m = 0; m < ARR2.Count; m++)
{
for (int j = m + 1; j < ARR2.Count; j++)
{
if (ARR2[j].Equals(ARR2[m]))
{
ARR2.RemoveAt(j--);
ARR2.RemoveAt(m);
}
}
}
//приведение к начальным
for (int x = 0; x <= ((arr.Length / 2) - 1); x++)
{
for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++)
{
if (ARR2.Contains(i))
{
unique.Add(x + 1);
unique.Add(i);
}
}
}
//собственно диапазоны остатка
for (int x = 0; x < unique.Count; x = x + 2)
{
int m;
if (x == 0)
{
m = x + 1;
}
else
{
m = x - 2;
}
if (!unique[x].Equals(unique[m]))
{
unique2.Add(unique[x]);
unique2.Add(unique[x + 1]);
}
int f;
if (x < unique.Count - 2)
{
f = x + 2;
}
else
{
f = x - 1;
}
if (!unique[x].Equals(unique[f]))
{
unique2.Add(unique[x + 1]);
/*for (int y = 0; y < b.Length/3; y++)
* {
* if (unique[x + 1].Equals(int.Parse(b[y, 0])))
* {
* unique2.Add(b[y, 1]);
* unique2.Add(b[y, 2]);
* }
* }*/
}
}
//массив для екселя
object[,] c = new object[unique2.Count, 3];
for (int i = 0, k = 0; i < unique2.Count; i = i + 3, k++)
{
c[k, 0] = unique2[i];
}
for (int i = 1, k = 0; i < unique2.Count; i = i + 3, k++)
{
c[k, 1] = unique2[i];
}
for (int i = 2, k = 0; i < unique2.Count; i = i + 3, k++)
{
c[k, 2] = unique2[i];
}
/*for (int i = 3, k = 0; i < unique2.Count; i = i + 5, k++)
* {
* c[k, 3] = unique2[i];
* }
*
* for (int i = 4, k = 0; i < unique2.Count; i = i + 5, k++)
* {
* c[k, 4] = unique2[i];
* }*/
object[] a = unique2.ToArray();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
string workbookPath = Application.StartupPath + "\\templates/bilety.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A4", "C" + unique2.Count);
excelCellName.Value2 = c;
}
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 static bool ExportDataToExcel(System.Data.DataTable srcDataTable, string excelFilePath)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;
//导出到execl
try
{
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel!");
return(false);
}
Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
//让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写
xlApp.Visible = false;
object[,] objData = new object[srcDataTable.Rows.Count + 1, srcDataTable.Columns.Count];
//首先将数据写入到一个二维数组中
for (int i = 0; i < srcDataTable.Columns.Count; i++)
{
objData[0, i] = srcDataTable.Columns[i].ColumnName;
}
if (srcDataTable.Rows.Count > 0)
{
for (int i = 0; i < srcDataTable.Rows.Count; i++)
{
for (int j = 0; j < srcDataTable.Columns.Count; j++)
{
objData[i + 1, j] = srcDataTable.Rows[i][j];
}
}
}
string startCol = "A";
int iCnt = (srcDataTable.Columns.Count / 26);
string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString());
string endCol = endColSignal + ((char)('A' + srcDataTable.Columns.Count - iCnt * 26 - 1)).ToString();
Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(startCol + "1", endCol + (srcDataTable.Rows.Count - iCnt * 26 + 1).ToString());
range.Value = objData; //给Exccel中的Range整体赋值
range.EntireColumn.AutoFit(); //设定Excel列宽度自适应
xlSheet.get_Range(startCol + "1", endCol + "1").Font.Bold = 1; //Excel文件列名 字体设定为Bold
//设置禁止弹出保存和覆盖的询问提示框
xlApp.DisplayAlerts = false;
xlApp.AlertBeforeOverwriting = false;
//if (File.Exists(excelFilePath))
// File.Delete(excelFilePath);
if (xlSheet != null)
{
xlSheet.SaveAs(excelFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
xlApp.Quit();
//KillProcess(xlApp);
//SystemUnit.KillProcess(xlApp);
}
}
catch (Exception ex)
{
Log.WriteLog("SystemUnit", "ERROR", "【ExportDataToExcel】 " + ex.Message);
xlApp.Quit();
//KillProcess(xlApp);
//throw ex;
return(false);
}
return(true);
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
progressBar1.Step = 1;
progressBar1.Value = 0;
progressBar1.Maximum = dataGridView1.RowCount;
progressBar1.Minimum = 0;
ModifyProgressBarColor.SetState(progressBar1, 1);
h = 2;
for (int i = 0; i < dataGridView1.RowCount; i++)
{
string tempImageName = "";
wrksheet.Cells[h, 1] = tempImageName = dataGridView1[0, i].Value + ""; //Trường 01/ Tên hình
//wrksheet.Cells[h, 2] = dataGridView1[1, i].Value + ""; //Trường Flag Error
wrksheet.Cells[h, 2] = dataGridView1[1, i].Value + ""; //Trường 03
wrksheet.Cells[h, 3] = dataGridView1[2, i].Value + "";
wrksheet.Cells[h, 4] = dataGridView1[3, i].Value + "";
wrksheet.Cells[h, 5] = dataGridView1[4, i].Value + "";
wrksheet.Cells[h, 6] = dataGridView1[5, i].Value + "";
wrksheet.Cells[h, 7] = dataGridView1[6, i].Value + "";
wrksheet.Cells[h, 8] = dataGridView1[7, i].Value + "";
wrksheet.Cells[h, 9] = dataGridView1[8, i].Value + ""; //Trường 10
//Trường 11
string tempTruong11 = "";
tempTruong11 = dataGridView1[9, i].Value + "";
if (tempTruong11.IndexOf("?") >= 0)
{
wrksheet.Cells[h, 10] = "?";
}
else if (tempTruong11 == "●")
{
wrksheet.Cells[h, 10] = "●";
}
else if (tempTruong11.Length == 6)
{
if (int.Parse(tempTruong11) < 291001)
{
wrksheet.Cells[h, 10] = "1001";
}
else if (int.Parse(tempTruong11) > 291231)
{
tempImageName = tempImageName.Substring(0, 3);
switch (tempImageName)
{
case "01_":
case "02_":
wrksheet.Cells[h, 10] = "1031";
break;
case "03_":
case "04_":
wrksheet.Cells[h, 10] = "1130";
break;
case "05_":
case "06_":
wrksheet.Cells[h, 10] = "1231";
break;
case "07_":
wrksheet.Cells[h, 10] = "1231";
break;
}
}
else
{
wrksheet.Cells[h, 10] = tempTruong11.Substring(2, 4);
}
}
else if (tempTruong11.Length < 6)
{
tempImageName = tempImageName.Substring(0, 3);
switch (tempImageName)
{
case "01_":
case "02_":
wrksheet.Cells[h, 10] = "1031";
break;
case "03_":
case "04_":
wrksheet.Cells[h, 10] = "1130";
break;
case "05_":
case "06_":
wrksheet.Cells[h, 10] = "1231";
break;
case "07_":
wrksheet.Cells[h, 10] = "1231";
break;
}
}
else if (tempTruong11.Length > 6)
{
wrksheet.Cells[h, 10] = tempTruong11;
}
wrksheet.Cells[h, 11] = dataGridView1[10, i].Value + ""; //Trường 12
wrksheet.Cells[h, 12] = dataGridView1[11, i].Value + ""; //Trường 13
wrksheet.Cells[h, 13] = dataGridView1[12, i].Value + ""; //Trường 14
lb_Complete.Text = (i + 1) + "/" + dataGridView1.RowCount;
progressBar1.PerformStep();
h++;
}
Microsoft.Office.Interop.Excel.Range rowHead = wrksheet.get_Range("A1", "M" + (h - 1));
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
}
/// <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;
}
}
}
/// <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;
}
}
}
/// <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;
}
}
}
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 static bool DataGridViewExportToExcel(DataTable mDataTable, String strFileName, ref String strMsg)
{
strMsg = "";
// 创建Excel对象
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
strMsg = "Excel无法启动";
return(false);
}
// 创建Excel工作薄
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = null;
/*
* // 设置标题
* range = xlSheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,ts.GridColumnStyles.Count]);
* range.MergeCells = true;
* xlApp.ActiveCell.FormulaR1C1 = p_ReportName;
* xlApp.ActiveCell.Font.Size = 20;
* xlApp.ActiveCell.Font.Bold = true;
* xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
*/
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = mDataTable.Columns.Count;
int RowCount = mDataTable.Rows.Count + 1;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount];
// 获取列标题
foreach (DataColumn dc in mDataTable.Columns)
{
objData[RowIndex, colIndex++] = dc.ColumnName;
}
// 获取数据
for (RowIndex = 1; RowIndex < RowCount; RowIndex++)
{
for (colIndex = 0; colIndex < colCount; colIndex++)
{
objData[RowIndex, colIndex] = mDataTable.Rows[RowIndex - 1][colIndex].ToString();
}
}
// 写入Excel
//((Excel.Range)xlSheet.Columns["A:A ",System.Reflection.Missing.Value]).ColumnWidth = 16;
range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount, colCount]);
range.Value2 = objData;
range = (Microsoft.Office.Interop.Excel.Range)xlSheet.Columns["A:B", System.Type.Missing];
range.ColumnWidth = 20;
// 保存
try
{
xlBook.Saved = true;
xlBook.SaveCopyAs(strFileName);
MessageBox.Show("数据转存为Excel成功");
}
catch (Exception ee)
{
strMsg = ee.Message;
return(false);
}
finally
{
range = null;
xlSheet = null;
xlBook = null;
xlApp.Quit();
xlApp = null;
GC.Collect();
}
return(true);
}
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;
}
/// <summary>
/// 通用导出Excle
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public ActionResult Excle(DataTable dt)
{
string path = "~/DownLoadTemplate/" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Millisecond + ".xls";
if (dt != null)
{
Microsoft.Office.Interop.Excel.Application xlApp = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
}
catch (Exception ex)
{
throw ex;
}
if (xlApp != null)
{
try
{
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Worksheet xlSheet = null;
xlSheet = xlBook.Worksheets[1];
xlSheet.Name = dt.TableName;
int rowIndex = 1;
int colIndex = 1;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
//列名的处理
for (int i = 0; i < colCount; i++)
{
xlSheet.Cells[rowIndex, colIndex] = dt.Columns[i].ColumnName;
colIndex++;
}
//列名加粗显示
xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, colCount]).Font.Bold = true;
xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Size = "10";
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 1;
for (int j = 0; j < colCount; j++)
{
xlSheet.Cells[rowIndex, colIndex] = dt.Rows[i][j].ToString();
colIndex++;
}
rowIndex++;
}
xlSheet.Cells.EntireColumn.AutoFit();
xlApp.DisplayAlerts = false;
path = Path.GetFullPath(path);
xlBook.SaveCopyAs(path);
xlBook.Close(false, null, null);
xlApp.Workbooks.Close();
Marshal.ReleaseComObject(xlSheet);
Marshal.ReleaseComObject(xlBook);
xlBook = null;
}
catch (Exception ex)
{
return(Json(new ReturnResult
{
Data = "",
Message = ex.Message,
Result = 1
}));
}
finally
{
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
int generation = System.GC.GetGeneration(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
}
}
return(Json(new ReturnResult
{
Data = "",
Message = "导出成功",
Result = 0
}));
}
public void SaveOutputToFile(string Path)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook = null;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet = null;
//ExcelApp.Visible = true;
ExcelWorkBook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//ExcelWorkBook.Worksheets.Add(); //Adding New Sheet in Excel Workbook
//try
{
ExcelWorkSheet = ExcelWorkBook.Worksheets[1]; // Compulsory Line in which sheet you want to write data
int CurrentRow = 2;
int CurrentColumn = 1;
ExcelWorkSheet.Cells[1, 1] = "Rejestracja";
ExcelWorkSheet.Cells[1, 2] = "Kilometry";
ExcelWorkSheet.Cells[1, 3] = "Koszt AdBlue";
ExcelWorkSheet.Cells[1, 4] = "Ilosc AdBlue";
ExcelWorkSheet.Cells[1, 5] = "Diesel koszt";
ExcelWorkSheet.Cells[1, 6] = "Ilosc Diesel";
ExcelWorkSheet.Cells[1, 7] = "Podatek Drogowy";
ExcelWorkSheet.Cells[1, 8] = "Inne Koszty";
ExcelWorkSheet.Cells[1, 9] = "Litry Diesel / 100 km";
ExcelWorkSheet.Cells[1, 10] = "Litry AdBlue / 100 km";
ExcelWorkSheet.Cells[1, 11] = "EUR Diesel / 100 km";
ExcelWorkSheet.Cells[1, 12] = "EUR AdBlue / 100 km";
ExcelWorkSheet.Cells[1, 13] = "Opłaty autostradowe / 100 km";
ExcelWorkSheet.Cells[1, 14] = "Inne opłaty / 100 km";
ExcelWorkSheet.Cells[1, 15] = "Wszystkie koszty / 100 km";
foreach (Truck element in TruckData)
{
String R = element.GetRegistration();
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = R;
CurrentColumn++;
double K = Math.Round((double)element.Kilometers, 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
K = Math.Round((double)element.AdblueCost, 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
K = Math.Round((double)element.AdblueL, 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
K = Math.Round((double)element.DieselCost, 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
K = Math.Round((double)element.DieselL, 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
K = Math.Round((double)element.RoadTax, 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
K = Math.Round((double)element.OtherCost, 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
// PER 100
if (element.Kilometers != 0)
{
// LDiesel / 100 //TODO:
K = Math.Round((double)(element.DieselL / (float)element.Kilometers * 100.0f), 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
// LAdBlue / 100
K = Math.Round((double)(element.AdblueL / (float)element.Kilometers * 100.0f), 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
// Eur Diesel / 100
K = Math.Round((double)(element.DieselCost / (float)element.Kilometers * 100.0f), 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
// Eur AdBlue / 100
K = Math.Round((double)(element.AdblueCost / (float)element.Kilometers * 100.0f), 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
// RoadTax / 100
K = Math.Round((double)(element.RoadTax / (float)element.Kilometers * 100.0f), 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
// Other / 100
K = Math.Round((double)(element.OtherCost / (float)element.Kilometers * 100.0f), 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
CurrentColumn++;
// All / 100
K = Math.Round((double)((
element.DieselL + element.DieselCost
+ element.AdblueL + element.AdblueCost
+ element.OtherCost + element.RoadTax)
/ (float)element.Kilometers * 100.0f), 2);
ExcelWorkSheet.Cells[CurrentRow, CurrentColumn] = K;
}
CurrentColumn = 1;
CurrentRow++;
}
//cosmetics
ExcelWorkBook.Worksheets[1].Name = "Raport";//Renaming the Sheet1 to MySheet
Microsoft.Office.Interop.Excel.Range aRange = ExcelWorkSheet.get_Range("A1", "O1");
aRange.Columns.AutoFit();
if (notFoundTrucks.Count != 0)
{
//the worksheet with not found trucks
ExcelWorkSheet = ExcelWorkBook.Sheets.Add(After: ExcelWorkBook.Sheets[ExcelWorkBook.Sheets.Count]);
ExcelWorkSheet.Name = "Brakujące rejestracje";
ExcelWorkSheet.Cells[1, 1] = "Plik";
ExcelWorkSheet.Cells[1, 2] = "Linia";
ExcelWorkSheet.Cells[1, 3] = "Rejestracja";
ExcelWorkSheet.Cells[1, 4] = "Dodatkowe informacje";
int currentRow = 2, currentColumn = 1;
foreach (TruckNotFound element in notFoundTrucks)
{
String filename = element.getFilename();
ExcelWorkSheet.Cells[currentRow, currentColumn] = filename;
currentColumn++;
int linia = element.getLine();
ExcelWorkSheet.Cells[currentRow, currentColumn] = linia;
currentColumn++;
string registration = element.GetRegistration();
ExcelWorkSheet.Cells[currentRow, currentColumn] = registration;
currentColumn++;
string extras = element.getOtherData();
ExcelWorkSheet.Cells[currentRow, currentColumn] = extras;
currentColumn++;
currentRow++;
currentColumn = 1;
}
Microsoft.Office.Interop.Excel.Range anotherRange = ExcelWorkSheet.get_Range("A:A", "O:O");
anotherRange.Columns.AutoFit();
}
ExcelWorkBook.SaveAs(Path);
ExcelWorkBook.Close(false);
ExcelApp.Quit();
//Marshal.ReleaseComObject(ExcelWorkSheet);
//Marshal.ReleaseComObject(ExcelWorkBook);
//Marshal.ReleaseComObject(ExcelApp);
}
//catch (Exception exHandle)
{
//Console.WriteLine("Exception: " + exHandle.Message);
//Console.ReadLine();
}
}
/// <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 bool TableToExcel(string strfilename)
{
try
{
dataGridView1.DataSource = null;
dataGridView1.DataSource = Global.Db.ExportExcel_Getsu(cbb_Batch.Text);
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.Worksheet wrksheet =
(Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
int h = 2;
int i = 0;
progressBarControl1.EditValue = 0;
progressBarControl1.Properties.Step = 1;
progressBarControl1.Properties.PercentView = true;
progressBarControl1.Properties.Maximum = dataGridView1.Rows.Count;
progressBarControl1.Properties.Minimum = 0;
progressBarControl1.Properties.StartColor = Color.DarkRed; // choose the color
progressBarControl1.Properties.EndColor = Color.Green; // choose the color
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
wrksheet.Cells[h, 1] = dr.Cells[0].Value?.ToString() ?? ""; //tên image
wrksheet.Cells[h, 2] = dr.Cells[1].Value?.ToString() ?? ""; //1
wrksheet.Cells[h, 3] = dr.Cells[2].Value?.ToString() ?? "";
if ((dr.Cells[3].Value?.ToString() ?? "").Length == 6)
{
wrksheet.Cells[h, 4] = (dr.Cells[3].Value?.ToString() ?? "").Substring(0, 2);
wrksheet.Cells[h, 5] = (dr.Cells[3].Value?.ToString() ?? "").Substring(2, 2);
wrksheet.Cells[h, 6] = (dr.Cells[3].Value?.ToString() ?? "").Substring(4, 2);
}
else
{
wrksheet.Cells[h, 4] = "";
wrksheet.Cells[h, 5] = "";
wrksheet.Cells[h, 6] = dr.Cells[3].Value?.ToString() ?? "";
}
wrksheet.Cells[h, 7] = dr.Cells[4].Value?.ToString() ?? "";
wrksheet.Cells[h, 8] = dr.Cells[5].Value?.ToString() ?? "";
wrksheet.Cells[h, 9] = dr.Cells[6].Value?.ToString() ?? "";
wrksheet.Cells[h, 10] = dr.Cells[7].Value?.ToString() ?? "";
wrksheet.Cells[h, 11] = dr.Cells[8].Value?.ToString() ?? "";
wrksheet.Cells[h, 12] = dr.Cells[9].Value?.ToString() ?? "";
wrksheet.Cells[h, 13] = dr.Cells[10].Value?.ToString() ?? "";
wrksheet.Cells[h, 14] = dr.Cells[11].Value?.ToString() ?? "";
if ((dr.Cells[12].Value?.ToString() ?? "").Length == 4)
{
wrksheet.Cells[h, 15] = (dr.Cells[12].Value?.ToString() ?? "").Substring(0, 2);
wrksheet.Cells[h, 16] = (dr.Cells[12].Value?.ToString() ?? "").Substring(2, 2);
}
else
{
wrksheet.Cells[h, 15] = "";
wrksheet.Cells[h, 16] = dr.Cells[12].Value?.ToString() ?? "";
}
wrksheet.Cells[h, 17] = dr.Cells[13].Value?.ToString() ?? "";
wrksheet.Cells[h, 18] = dr.Cells[14].Value?.ToString() ?? "";
wrksheet.Cells[h, 19] = dr.Cells[15].Value?.ToString() ?? "";
wrksheet.Cells[h, 20] = dr.Cells[16].Value?.ToString() ?? "";
wrksheet.Cells[h, 21] = dr.Cells[17].Value?.ToString() ?? "";
wrksheet.Cells[h, 22] = dr.Cells[18].Value?.ToString() ?? "";
wrksheet.Cells[h, 23] = dr.Cells[19].Value?.ToString() ?? "";
wrksheet.Cells[h, 24] = dr.Cells[20].Value?.ToString() ?? "";
wrksheet.Cells[h, 25] = dr.Cells[21].Value?.ToString() ?? "";
wrksheet.Cells[h, 26] = dr.Cells[22].Value?.ToString() ?? "";
wrksheet.Cells[h, 27] = dr.Cells[23].Value?.ToString() ?? "";
wrksheet.Cells[h, 28] = dr.Cells[24].Value?.ToString() ?? "";
wrksheet.Cells[h, 29] = dr.Cells[25].Value?.ToString() ?? "";
wrksheet.Cells[h, 30] = dr.Cells[26].Value?.ToString() ?? "";
lb_SoDong.Text = (h - 1) + @"/" + dataGridView1.Rows.Count;
Microsoft.Office.Interop.Excel.Range rowHead = wrksheet.get_Range("A1", "AD" + h);
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
i++;
h++;
progressBarControl1.PerformStep();
progressBarControl1.Update();
}
string savePath = "";
saveFileDialog1.Title = "Save Excel Files";
saveFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx";
saveFileDialog1.FileName = cbb_Batch.Text + "_Getsu";
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(@"Error exporting excel!");
return(false);
}
Process.Start(savePath);
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
private void GenerateExcel(bool isPDF, SaveFileDialog sfd)
{
CopyAlltoClipboard();
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int colStart = 1;
int addCol = 0;
int logoCellLeft = 0;
if (cboReports.SelectedValue.ToString() == "Absentees Report" || cboReports.SelectedValue.ToString() == "No Time Out Report")
{
addCol = 2;
colStart += addCol;
logoCellLeft = 100;
}
else if (cboReports.SelectedValue.ToString() == "Attendance Report")
{
logoCellLeft = 12;
}
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[8, colStart];
xlWorkSheet.Shapes.AddPicture(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @LOGO_PATH, MsoTriState.msoFalse, MsoTriState.msoCTrue, logoCellLeft, 0, 90, 90);
xlWorkSheet.get_Range("A1", "A1").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.get_Range("A1", "A1").Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
CR.Rows.AutoFit();
CR.get_Range("A1").Columns.EntireColumn.AutoFit();
CR.get_Range("C1", "K1").Columns.EntireColumn.AutoFit();
CR.get_Range("B1").Columns.ColumnWidth = 35;
if (cboReports.SelectedValue.ToString().Trim() == "Attendance Report")
{
CR.get_Range("D1", "K1").Columns.EntireColumn.AutoFit();
CR.get_Range("C1").Columns.ColumnWidth = 20;
}
CR.get_Range("B1").Cells.Style.WrapText = true;
xlWorkSheet.Cells[3, 3 + addCol - 1] = "' " + cboReports.SelectedValue.ToString();
xlWorkSheet.Cells[3, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.Cells[4, 3 + addCol - 1] = "' " + dtFrom.SelectedDate.Value.ToString("dd MMMMM yyyy") + " to " + dtTo.SelectedDate.Value.ToString("dd MMMM yyyy");
xlWorkSheet.Cells[4, 3 + addCol - 1].Cells.Style.WrapText = false;
xlWorkSheet.Cells[4, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.Cells[5, 3 + addCol - 1] = "' " + GetFilterUsed().Replace("_", "");
xlWorkSheet.Cells[5, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
int ctr = 1;
while (ctr <= dgResults.Columns.Count)
{
((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[8, ctr + addCol]).Interior.Color = ColorTranslator.ToOle(Color.AliceBlue);
ctr++;
}
for (int ctrC = 1; ctrC <= dgResults.Columns.Count; ctrC++)
{
for (int ctrR = 8; ctrR <= dgResults.Items.Count + 8; ctrR++)
{
((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[ctrR, ctrC + addCol]).BorderAround(LineStyle.Thin, Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, ColorTranslator.ToOle(Color.AliceBlue));
}
}
Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, colStart];
rg.EntireColumn.NumberFormat = "yyyy/MM/dd";
if (cboReports.SelectedValue.ToString() == "Consolidated Report")
{
rg.EntireColumn.NumberFormat = "@";
}
xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlexcel);
dgResults.UnselectAllCells();
Clipboard.Clear();
if (File.Exists(sfd.FileName) && !isPDF)
{
CheckIfFileIsOpen(sfd);
//System.Diagnostics.Process.Start(sfd.FileName);
System.Diagnostics.Process.Start(Path.GetDirectoryName(sfd.FileName));
}
}
/// <summary>
/// 导入文本
/// </summary>
private void ImportText(params object[] param)
{
List <string> listItems = (List <string>)param[0];
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
// 显示进度条
this.ResetProcessBar(listItems.Count);
try
{
StringBuilder failFiles = new StringBuilder();
// 创建Application对象
this.xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// 得到WorkBook对象, 打开已有的文件
xBook = this.xApp.Workbooks._Open(
this.baseFile,
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = xBook.Sheets.Count; i >= 1; i--)
{
// 取得相应的Sheet
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i];
int sheetIndex = -1;
string sheetName = string.Empty;
for (int j = 0; j < listItems.Count; j++)
{
sheetName = listItems[j].Split(' ')[0];
if (xSheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase))
{
sheetIndex = j;
break;
}
}
if (sheetIndex > -1)
{
// 更新当前文本
this.fileList.Invoke((MethodInvoker) delegate()
{
this.fileList.SelectedIndex = sheetIndex;
});
// 取得当前Sheet的中文文本
int lineNum = 1;
int blankNum = 0;
StringBuilder sb = new StringBuilder();
while (blankNum < 4)
{
string cellValue = xSheet.get_Range("G" + lineNum, Missing.Value).Value2 as string;
sb.Append(cellValue).Append("\n");
if (string.IsNullOrEmpty(cellValue))
{
blankNum++;
}
else
{
blankNum = 0;
}
lineNum++;
}
sb = sb.Replace("\n\n\n\n\n", "\n");
this.Invoke((MethodInvoker) delegate()
{
this.txtCn.Text = sb.ToString();
// 保存
if (!this.Save())
{
//throw new Exception("有文件长度检查失败");
failFiles.Append("\n").Append(sheetName);
}
});
}
else
{
failFiles.Append("\n未找到 ").Append(xSheet.Name);
}
// 更新进度条
this.ProcessBarStep();
}
// 隐藏进度条
this.CloseProcessBar();
// 显示保存完成信息
if (failFiles.Length == 0)
{
MessageBox.Show("完全成功导入！");
}
else
{
MessageBox.Show("导入完成，下面文件失败" + failFiles.ToString());
}
// 导入成功后的处理
this.AfterImport();
}
catch (Exception me)
{
MessageBox.Show(this.baseFile + "\n" + me.Message);
}
finally
{
// 隐藏进度条
this.CloseProcessBar();
// 清空各种对象
xSheet = null;
xBook = null;
if (this.xApp != null)
{
this.xApp.Quit();
this.xApp = null;
}
}
}
/////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////
private void button1_Click(object sender, EventArgs e)
{
onay = MessageBox.Show("Onaylıyor musunuz?", "Onay", MessageBoxButtons.YesNo);
if (onay == DialogResult.Yes)
{
try
{
mysqlbaglan.Open();
string komut = "insert into fatura(sayin,vdno,no,adres,adres2,tarih,fisNo,tarih1,aciklama,adet,kab,cins,kilo,fiyat,tutar,fisNo2,tarih2,aciklama2,adet2,kab2,cins2,kilo2,fiyat2,tutar2,fisNo3,tarih3,aciklama3,adet3,kab3,cins3,kilo3,fiyat3,tutar3,fisNo4,tarih4,aciklama4,adet4,kab4,cins4,kilo4,fiyat4,tutar4,fisNo5,tarih5,aciklama5,adet5,kab5,cins5,kilo5,fiyat5,tutar5,fisNo6,tarih6,aciklama6,adet6,kab6,cins6,kilo6,fiyat6,tutar6,toplam,genelToplam,tesellumNo) values('" + comboBox1.Text + "', '" + textBox2.Text + "','" + textBox1.Text + "','" + textBox16.Text + "','" + textBox23.Text + "', '" + dateTimePicker1.Text + "', '" + comboBox2.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + textBox8.Text + "', '" + textBox9.Text + "','" + textBox50.Text + "','" + textBox56.Text + "','" + comboBox3.Text + "', '" + textBox15.Text + "', '" + textBox14.Text + "', '" + textBox13.Text + "', '" + textBox12.Text + "', '" + textBox11.Text + "', '" + textBox10.Text + "','" + textBox49.Text + "','" + textBox55.Text + "','" + comboBox4.Text + "', '" + textBox22.Text + "', '" + textBox21.Text + "', '" + textBox20.Text + "', '" + textBox19.Text + "', '" + textBox18.Text + "', '" + textBox17.Text + "','" + textBox48.Text + "','" + textBox54.Text + "','" + comboBox5.Text + "', '" + textBox29.Text + "', '" + textBox28.Text + "', '" + textBox27.Text + "', '" + textBox26.Text + "', '" + textBox25.Text + "', '" + textBox24.Text + "','" + textBox47.Text + "','" + textBox53.Text + "','" + comboBox6.Text + "', '" + textBox36.Text + "', '" + textBox35.Text + "', '" + textBox34.Text + "', '" + textBox33.Text + "', '" + textBox32.Text + "', '" + textBox31.Text + "','" + textBox46.Text + "','" + textBox52.Text + "','" + comboBox7.Text + "', '" + textBox43.Text + "', '" + textBox42.Text + "', '" + textBox41.Text + "', '" + textBox40.Text + "', '" + textBox39.Text + "', '" + textBox38.Text + "','" + textBox45.Text + "','" + textBox51.Text + "','" + label1.Text + "','" + label2.Text + "','" + textBox3.Text + "')";
MySqlCommand kmt = new MySqlCommand(komut, mysqlbaglan);
kmt.ExecuteNonQuery();
Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
objExcel.Visible = true;
Microsoft.Office.Interop.Excel.Workbook objBook = objExcel.Workbooks.Open("c:\\fatura.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objBook.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range objRange;
objRange = objSheet.get_Range("K7", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, dateTimePicker1.Text);
objRange = objSheet.get_Range("b7", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox2.Text);
objRange = objSheet.get_Range("e7", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox1.Text);
objRange = objSheet.get_Range("k4", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox3.Text);
objRange = objSheet.get_Range("b3", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, comboBox1.Text);
objRange = objSheet.get_Range("A4", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox16.Text);
objRange = objSheet.get_Range("A5", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox23.Text);
objRange = objSheet.get_Range("A23", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox30.Text);
// 1. SIRA
objRange = objSheet.get_Range("A10", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, comboBox2.Text);
objRange = objSheet.get_Range("B10", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox4.Text);
objRange = objSheet.get_Range("C10", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox5.Text);
objRange = objSheet.get_Range("F10", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox6.Text);
objRange = objSheet.get_Range("G10", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox7.Text);
objRange = objSheet.get_Range("H10", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox8.Text);
objRange = objSheet.get_Range("I10", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox9.Text);
objRange = objSheet.get_Range("J10", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox50.Text);
objRange = objSheet.get_Range("K10", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox56.Text);
// 2. SIRA
objRange = objSheet.get_Range("A12", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, comboBox3.Text);
objRange = objSheet.get_Range("B12", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox15.Text);
objRange = objSheet.get_Range("C12", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox14.Text);
objRange = objSheet.get_Range("F12", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox13.Text);
objRange = objSheet.get_Range("G12", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox12.Text);
objRange = objSheet.get_Range("H12", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox11.Text);
objRange = objSheet.get_Range("I12", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox10.Text);
objRange = objSheet.get_Range("J12", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox49.Text);
objRange = objSheet.get_Range("K12", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox55.Text);
//3. SIRA
objRange = objSheet.get_Range("A14", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, comboBox4.Text);
objRange = objSheet.get_Range("B14", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox22.Text);
objRange = objSheet.get_Range("C14", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox21.Text);
objRange = objSheet.get_Range("F14", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox20.Text);
objRange = objSheet.get_Range("G14", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox19.Text);
objRange = objSheet.get_Range("H14", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox18.Text);
objRange = objSheet.get_Range("I14", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox17.Text);
objRange = objSheet.get_Range("J14", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox48.Text);
objRange = objSheet.get_Range("K14", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox54.Text);
//4. SIRA
objRange = objSheet.get_Range("A16", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, comboBox5.Text);
objRange = objSheet.get_Range("B16", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox29.Text);
objRange = objSheet.get_Range("C16", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox28.Text);
objRange = objSheet.get_Range("F16", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox27.Text);
objRange = objSheet.get_Range("G16", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox26.Text);
objRange = objSheet.get_Range("H16", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox25.Text);
objRange = objSheet.get_Range("I16", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox24.Text);
objRange = objSheet.get_Range("J16", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox47.Text);
objRange = objSheet.get_Range("K16", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox53.Text);
//5. SIRA
objRange = objSheet.get_Range("A18", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, comboBox6.Text);
objRange = objSheet.get_Range("B18", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox36.Text);
objRange = objSheet.get_Range("C18", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox35.Text);
objRange = objSheet.get_Range("F18", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox34.Text);
objRange = objSheet.get_Range("G18", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox33.Text);
objRange = objSheet.get_Range("H18", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox32.Text);
objRange = objSheet.get_Range("I18", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox31.Text);
objRange = objSheet.get_Range("J18", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox46.Text);
objRange = objSheet.get_Range("K18", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox52.Text);
//6. SIRA
objRange = objSheet.get_Range("A20", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, comboBox7.Text);
objRange = objSheet.get_Range("B20", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox43.Text);
objRange = objSheet.get_Range("C20", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox42.Text);
objRange = objSheet.get_Range("F20", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox41.Text);
objRange = objSheet.get_Range("G20", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox40.Text);
objRange = objSheet.get_Range("H20", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox39.Text);
objRange = objSheet.get_Range("I20", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox38.Text);
objRange = objSheet.get_Range("J20", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox45.Text);
objRange = objSheet.get_Range("K20", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, textBox51.Text);
objSheet.PrintOutEx(1, 1, 2, true);
Form anasayfa = new anasayfa();
anasayfa.Show();
this.Hide();
}
catch (Exception h)
{
MessageBox.Show("Beklenmeyen bir hata oluştu lütfen tekrar deneyiniz" + h, "HATA", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
}
private void fpSpread_addsheet()
{
WaitDialogForm wait = null;
wait = new WaitDialogForm("", "正在加载数据, 请稍候...");
try
{
//打开Excel表格
//清空工作表
fpSpread1.Sheets.Clear();
fpSpread1.OpenExcel(Tcommon.ExcelFilePath + ExcelName);
TC.SpreadRemoveEmptyCells(fpSpread1);
}
catch (System.Exception e)
{
//如果打开出错则重新生成并保存
fpSpread1.Sheets.Clear();
Firstadddata();
//判断文件夹是否存在，不存在则创建
if (!Directory.Exists(Tcommon.CurrentPath + Tcommon.ExcelDir))
{
Directory.CreateDirectory(Tcommon.CurrentPath + Tcommon.ExcelDir);
}
//保存excel文件
fpSpread1.SaveExcel(Tcommon.ExcelFilePath + ExcelName);
//以下是打开文件设表格自动换行
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
string filename = Tcommon.ExcelFilePath + ExcelName;
workBook = excelApp.Workbooks.Open(filename, 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);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//取消保护工作表
ws.Unprotect(Missing.Value);
//有数据的行数
int row = ws.UsedRange.Rows.Count;
//有数据的列数
int col = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//设区域内的单元格自动换行
range.WrapText = true;
//保护工作表
ws.Protect(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, Missing.Value, Missing.Value);
}
//保存工作簿
workBook.Save();
//关闭工作簿
excelApp.Workbooks.Close();
}
wait.Close();
}
/// <summary>
/// 导出Excel 的方法
/// </summary>
private void tslExport_Excel(string fileName, DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
ISdao = true;
groupBox3.Visible = true;
btnSet.Text = "取消导出";
progressBar1.Maximum = myDGV.SelectedRows.Count;
progressBar1.Value = 0;
label19.Text = "正在导出：" + fileName;
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", "Z" + (myDGV.SelectedRows.Count + 10)); //把Execl设置问文本格式
range.NumberFormatLocal = "@";
//写入标题
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
}
//写入数值
int s = 0;
for (int r = 0; r < myDGV.SelectedRows.Count; r++)
{
if (ISdao)
{
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[s + 2, i] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
s++;
progressBar1.Value++;
}
else
{
break;
}
}
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
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n");
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
if (progressBar1.Value >= progressBar1.Maximum)
{
label19.Text = fileName;
btnSet.Text = "导出完成";
}
}
private void ExcelFeedBack_User(List <string> idimage)
{
if (File.Exists(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Feedback_User.xlsx"))
{
File.Delete(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Feedback_User.xlsx");
File.WriteAllBytes((Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "/Feedback_User.xlsx"), Properties.Resources.Feedback_User);
}
else
{
File.WriteAllBytes(
(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "/Feedback_User.xlsx"), Properties.Resources.Feedback_User);
}
int r = 1;
//int distance = 15;
App = new Microsoft.Office.Interop.Excel.Application();
book = App.Workbooks.Open(System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments) + "\\Feedback_User.xlsx", 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
wrksheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
string pathServer = Global.Webservice + Folder + "/" + cbb_batch.Text;
for (int i = 0; i < idimage.Count; i++)
{
string id = idimage[i];
Microsoft.Office.Interop.Excel.Range oRange = wrksheet.Cells[r + 1, 2];
float Left = (float)((double)oRange.Left);
float Top = (float)((double)oRange.Top + 2);
Microsoft.Office.Interop.Excel.Range final = wrksheet.Cells[r + 20, 9];
float leftFinal = (float)((double)final.Left) - Left - 1;
float topFinal = (float)((double)final.Top + 2) - Top;
wrksheet.Shapes.AddPicture(pathServer + "/" + idimage[i], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, leftFinal, topFinal);//365, 270);
// oRange.RowHeight = 367;
// wrksheet.Cells[1, 20] = wrksheet.Shapes.AddPicture(pathServer + "/" + idimage[i], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 20, distance, 365, 270);
//Image oImage = Image.FromFile(pathServer + "/" + idimage[i]);
//wrksheet.Shapes.AddPicture(pathServer + "/" + idimage[i], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 20, distance, 365, 270);
// distance += 300;
var deso = Global.Db.FeedBackExcel_User(id, cbb_username.Text, cbb_batch.Text).ToList();
int h = 9;
wrksheet.Cells[r + 1, h] = "UserName";
wrksheet.Cells[r + 2, h] = "Trường 1";
wrksheet.Cells[r + 3, h] = "Trường 3";
wrksheet.Cells[r + 4, h] = "Trường 4";
wrksheet.Cells[r + 5, h] = "Trường 5";
wrksheet.Cells[r + 6, h] = "Trường 6";
wrksheet.Cells[r + 7, h] = "Trường 7";
wrksheet.Cells[r + 8, h] = "Trường 8.1";
wrksheet.Cells[r + 9, h] = "Trường 8.2";
wrksheet.Cells[r + 10, h] = "Trường 9";
wrksheet.Cells[r + 11, h] = "Trường 10";
wrksheet.Cells[r + 12, h] = "Trường 11";
wrksheet.Cells[r + 13, h] = "Trường 12";
wrksheet.Cells[r + 14, h] = "Trường 13";
wrksheet.Cells[r + 15, h] = "Trường 14";
// wrksheet.Cells[r + 15, h] = "FlagError";
wrksheet.Cells[r + 20, 1] = id + "";
wrksheet.Cells[r + 1, 1] = i + 1;
for (int j = 0; j < deso.Count(); j++)
{
h++;
wrksheet.Cells[r + 1, h] = deso[j].UserName + "";
wrksheet.Cells[r + 2, h] = deso[j].TruongSo01 + "";
wrksheet.Cells[r + 3, h] = deso[j].TruongSo03 + "";
wrksheet.Cells[r + 4, h] = deso[j].TruongSo04 + "";
wrksheet.Cells[r + 5, h] = deso[j].TruongSo05 + "";
wrksheet.Cells[r + 6, h] = deso[j].TruongSo06 + "";
wrksheet.Cells[r + 7, h] = deso[j].TruongSo07 + "";
wrksheet.Cells[r + 8, h] = deso[j].TruongSo08 + "";
wrksheet.Cells[r + 9, h] = deso[j].TruongSo08_2 + "";
wrksheet.Cells[r + 10, h] = deso[j].TruongSo09 + "";
wrksheet.Cells[r + 11, h] = deso[j].TruongSo10 + "";
wrksheet.Cells[r + 12, h] = deso[j].TruongSo11 + "";
wrksheet.Cells[r + 13, h] = deso[j].TruongSo12 + "";
wrksheet.Cells[r + 14, h] = deso[j].TruongSo13 + "";
wrksheet.Cells[r + 15, h] = deso[j].TruongSo14 + "";
// wrksheet.Cells[r + 15, h] = deso[j].FlagError + "";
}
Microsoft.Office.Interop.Excel.Range cellImage1 = wrksheet.Cells[h - 10][r + 1];
Microsoft.Office.Interop.Excel.Range cellImage2 = wrksheet.Cells[h][r + 20];
Microsoft.Office.Interop.Excel.Range rangeImage = wrksheet.get_Range(cellImage1, cellImage2);
rangeImage.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, 1);
Microsoft.Office.Interop.Excel.Range cell1 = wrksheet.Cells[h - 2][r + 1];
Microsoft.Office.Interop.Excel.Range cell2 = wrksheet.Cells[h][r + 15];
Microsoft.Office.Interop.Excel.Range range = wrksheet.get_Range(cell1, cell2);
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
r += 21;
}
string savePath = "";
saveFileDialog1.Title = "Save Excel Files";
saveFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx";
saveFileDialog1.FileName = "Feedback_" + cbb_batch.Text + "_" + cbb_username.Text;
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(@"Error exporting excel!");
return;
}
Process.Start(savePath);
}
/// <summary>
/// 导出Execl 全选
/// </summary>
/// <param name="dt"></param>
protected void ExportExcel(string fileName, DataTable dt)
{
try
{
if (dt == null || dt.Rows.Count == 0)
{
return;
}
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
ISdao = true;
groupBox3.Visible = true;
btnSet.Text = "取消导出";
progressBar1.Maximum = dt.Columns.Count;
progressBar1.Value = 0;
label19.Text = "正在导出：" + fileName;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", "Z" + (dt.Rows.Count + 10));
range.NumberFormatLocal = "@";
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
//写入数值
int s = 0;
for (int r = 0; r < dt.Columns.Count; r++)
{
if (ISdao)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
worksheet.Cells[i + 2, r + 1] = dt.Rows[i][r].ToString();
}
System.Windows.Forms.Application.DoEvents();
progressBar1.Value++;
}
else
{
break;
}
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, 2]);
rang.NumberFormat = "000000000000";
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + "");
}
}
xlApp.Quit();
GC.Collect();//强行销毁
//MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
if (progressBar1.Value >= progressBar1.Maximum)
{
label19.Text = fileName;
btnSet.Text = "导出完成";
}
}
catch (System.Exception ex)
{
MessageBox.Show(fileName + ",导出错误", "提示", MessageBoxButtons.OK);
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
string FileName = @"C:\temp\temp.xls";
// Copy DataGridView results to clipboard
dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
dataGridView1.SelectAll();
DataObject dataObj = dataGridView1.GetClipboardContent();
if (dataObj != null)
{
Clipboard.SetDataObject(dataObj);
}
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Format column D as text before pasting results, this was required for my data
//Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
//rng.NumberFormat = "@";
// Paste clipboard results to worksheet range
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
// For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
// Delete blank column A and select cell A1
//Microsoft.Office.Interop.Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
//delRng.Delete(Type.Missing);
xlWorkSheet.get_Range("A1").Select();
Microsoft.Office.Interop.Excel.Worksheet ws = xlexcel.ActiveWorkbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = ws.UsedRange;
//ws.Columns.ClearFormats();
//ws.Rows.ClearFormats();
//range.EntireColumn.AutoFit();
//range.EntireRow.AutoFit();
xlWorkSheet.Range["A1:D1"].Interior.Color = System.Drawing.Color.LightSkyBlue;
ws.Columns.AutoFit();
ws.Rows.AutoFit();
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders.Color = ColorTranslator.ToOle(Color.Black);
// Save the excel file under the captured location from the SaveFileDialog
xlWorkBook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
//releaseObject(xlWorkSheet);
//releaseObject(xlWorkBook);
//releaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
Clipboard.Clear();
dataGridView1.ClearSelection();
// Open the newly saved excel file
if (File.Exists(FileName))
{
System.Diagnostics.Process.Start(FileName);
}
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks;
Microsoft.Office.Interop.Excel.Workbook excelBook;
//app = null;
//app = new Excel.Application(); // create a new instance
excelApp.DisplayAlerts = false; //turn off annoying alerts that make me want to cryyyy
uint processID = 0;
workbooks = excelApp.Workbooks;
excelBook = workbooks.Add(FileName);
Microsoft.Office.Interop.Excel.Sheets sheets = excelBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)(sheets[1]);
//Range.Rows.AutoFit();
//Range.Columns.AutoFit();
}
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);
}
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 TravelServer GetTravelServerFromXLSAndChangeProgressBar(string machineName, ProgressBar pgb)
{
int max = 0;
if (pgb != null)
{
max = pgb.Maximum;
}
int i = 2;
System.Array myvalues;
TravelServer travelServer = new TravelServer(machineName);
travelServer.Databases = new List <DatabaseItem>();
TXTReader txtReader = TXTReader.CreateInstance();
txtReader.GetServerPortPair();
Microsoft.Office.Interop.Excel.Application ExcelObj = null;
ExcelObj = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(file, 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);
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString());
if (pgb != null)
{
pgb.Invoke((MethodInvoker) delegate { pgb.Maximum = worksheet.Rows.Row; });
}
while (!range.Text.Equals(""))
{
myvalues = (System.Array)range.Cells.Value2;
if (machineName.Equals("All") || myvalues.GetValue(1, 1).ToString().Equals(machineName))
{
DatabaseItem databaseItem = new DatabaseItem(myvalues.GetValue(1, 2).ToString(),
myvalues.GetValue(1, 2).ToString(), "PPE database", "CHC-APPWG01.idx.expedmz.com," +
txtReader.GetPortByServerName(myvalues.GetValue(1, 1).ToString()).Value.ToString(),
"Update8!", "expdev");
travelServer.Databases.Add(databaseItem);
}
i++;
range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString());
if (pgb != null)
{
pgb.Invoke((MethodInvoker) delegate
{
pgb.PerformStep();
GlobalOperator.SetProgressBarText(pgb, "Reloading databases of " + machineName);
});
}
}
theWorkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlDoNotSaveChanges, Type.Missing, Type.Missing);
ExcelObj.Quit();
if (pgb != null)
{
pgb.Invoke((MethodInvoker) delegate { pgb.Maximum = max; });
}
return(travelServer);
}
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 dataGridViewExportAsExcel(DataGridView dgv, string FileFullPath)
{
string strExamPath = FileFullPath.Substring(0, FileFullPath.LastIndexOf('\\'));
if (!System.IO.Directory.Exists(strExamPath))
{
MessageBox.Show(FileFullPath, "目录错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.EnableEvents = false;
excelApplication.Application.DisplayAlerts = false;
excelApplication.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelApplication.ActiveSheet;
excelApplication.Visible = false;
int nRowIndex = 0;
int nColumnIndex = 0;
object[,] strArr = new object[gvDataRecord.RowCount + 1, gvDataRecord.ColumnCount];
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
strArr[nRowIndex, nColumnIndex] = dgvc.HeaderText;
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
foreach (DataGridViewRow dgvr in dgv.Rows)
{
foreach (DataGridViewCell dgvcell in dgvr.Cells)
{
strArr[nRowIndex, nColumnIndex] = dgvcell.Value.ToString();
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
}
string strExcelMaxColumnIndex = GetExcelMaxColumnIndex(dgv.ColumnCount, dgv.RowCount + 1);
Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.get_Range("A1", strExcelMaxColumnIndex);
myRange.get_Resize(dgv.RowCount + 1, dgv.ColumnCount);
try
{
myRange.Value2 = strArr;
myRange.Columns.AutoFit();
myRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
myWorkSheet.SaveAs(FileFullPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
MessageBox.Show("文件成功保存到了" + FileFullPath, "保存成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
excelApplication.Quit();
killexcel(excelApplication);
GC.Collect();
}
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;
}
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;
}