public object[,] excelMasterStart(string path)
{
//Create Excel Objects
Excel.Application ExcelApp;
Excel._Workbook ExcelWorkbook;
Excel._Worksheet ExcelSheet;
//Initilize ExcelApp
ExcelApp = new Excel.Application();
//Open current Excel file
Console.WriteLine($"Opening: {path}");
ExcelWorkbook = ExcelApp.Workbooks.Open(path);
//Set Active Sheet
ExcelSheet = ExcelWorkbook.Sheets[1];
//ExcelApp.Visible = true;
Excel.Range ExcelRange = ExcelSheet.UsedRange;
int rowCount = ExcelRange.Rows.Count;
int colCount = ExcelRange.Columns.Count;
rowCountF = rowCount;
colCountF = colCount;
object[,] excelValues = (object[, ])ExcelRange.Value2;
//Close and release all COM objects, quit excel
Marshal.ReleaseComObject(ExcelRange);
Marshal.ReleaseComObject(ExcelSheet);
//close and release
ExcelWorkbook.Close();
Marshal.ReleaseComObject(ExcelWorkbook);
//quit and release
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelApp);
return(excelValues);
}
/// <summary>
/// Terminate the Excel Application
/// </summary>
/// <remarks></remarks>
internal void ExcelShutDown()
{
try
{
// Quit if we have an Application Reference
if (_excelApp != null)
{
ExcelWorkbook.Close(true);
_excelApp.Workbooks.Close();
_excelApp.Quit();
if (_processId != 0)
{
Process process = Process.GetProcessById(_processId);
process.Kill();
_processId = 0;
}
}
if (_processId != 0)
{
Process process = Process.GetProcessById(_processId);
process.Kill();
}
// Set References to Nothing
ExcelWorkbook = null;
_excelApp = null;
GC.Collect();
}
catch { }
}
private DataTable CreateDTfromExcel(HttpPostedFile file)
{
StringBuilder errorMSG = new StringBuilder();
String fileName = String.Empty;
String filePath = String.Empty;
//DataTable excelDataTableWithError = new DataTable();
filePath = ConfigurationManager.AppSettings["GetFileFolder"] + "\\" + file.FileName;
file.SaveAs(filePath);
//filePath += fileName;
FileStream sourceXlsDataStream = new System.IO.FileStream(filePath, FileMode.Open);
ExcelWorkbook tempWorkbook = new ExcelWorkbook(sourceXlsDataStream);
tempWorkbook.LicenseKey = ConfigurationManager.AppSettings["ExportExcelFileKey"];
ExcelWorksheet tempWorksheet = tempWorkbook.Worksheets[0];
//StringBuilder errorMSG = new StringBuilder();
// get the data from the used range of the temporary workbook to a .NET DataTable object
DataTable exportedDataTable = tempWorksheet.GetDataTable(tempWorksheet.UsedRange, true, false, true);
if (exportedDataTable.Rows.Count == 0)
{
}
tempWorkbook.Close();
sourceXlsDataStream.Close();
return(exportedDataTable);
}
public static void Main()
{
// Opens a workbook from the specified Excel file
string path = @"..\..\imagine.xls";
ExcelWorkbook excelFile = new ExcelWorkbook(path);
var worksheet = excelFile.Worksheets[0];
worksheet.Activate();
List <int> Rs = new List <int>(1220);
List <int> Gs = new List <int>(1220);
List <int> Bs = new List <int>(1220);
var allRows = worksheet.UsedRangeRows;
for (int i = 1; i < allRows.Count; i++)
{
var currentRow = allRows[i];
Rs.Add(int.Parse(currentRow.Cells[3].Value.ToString()));
Gs.Add(int.Parse(currentRow.Cells[4].Value.ToString()));
Bs.Add(int.Parse(currentRow.Cells[5].Value.ToString()));
}
// sets each row's background color to the specified one in the .xls file
for (int i = 1; i <= Rs.Count; i++)
{
allRows[i].Style.Fill.FillType = ExcelCellFillType.SolidFill;
allRows[i].Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, Rs[i - 1], Gs[i - 1], Bs[i - 1]);
}
excelFile.Save(@"..\..\Imagine(Modified).xls");
excelFile.Close();
}
public List <ClienteBean> ReadFromExcel(string keyName)
{
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, ExcelPath);
List <ClienteBean> clientes = new List <ClienteBean>();
Excel.Application ExcelApp = new Excel.Application();
Excel.Workbooks ExcelWorkbooks;
Excel.Workbook ExcelWorkbook;
Excel.Worksheet ExcelWorksheet;
Excel.Range ExcelRange;
ExcelWorkbooks = ExcelApp.Workbooks;
ExcelWorkbook = ExcelWorkbooks.Open(path);
ExcelWorksheet = ExcelWorkbook.Sheets[1];
ExcelRange = ExcelWorksheet.UsedRange;
int rowCount = ExcelRange.Rows.Count;
int colCount = ExcelRange.Columns.Count;
string[] headers = new string[colCount];
for (int i = 1; i <= colCount; i++)
{
headers[i - 1] = (Convert.ToString((ExcelRange.Cells[1, i] as Excel.Range).Value2));
}
for (int j = 2; j <= rowCount; j++)
{
string Key = Convert.ToString((ExcelRange.Cells[j, 1] as Excel.Range).Value2);
if (Key.Equals(keyName))
{
ClienteBean clienteBean = new ClienteBean();
clienteBean.Apellido = Convert.ToString((ExcelRange.Cells[j, 2] as Excel.Range).Value2);
clienteBean.Nombre = Convert.ToString((ExcelRange.Cells[j, 3] as Excel.Range).Value2);
clienteBean.Dni = Convert.ToString((ExcelRange.Cells[j, 4] as Excel.Range).Value2);
clienteBean.Sexo = Convert.ToString((ExcelRange.Cells[j, 5] as Excel.Range).Value2);
clienteBean.Nivelestudios = Convert.ToString((ExcelRange.Cells[j, 6] as Excel.Range).Value2);
clienteBean.Telefono = Convert.ToString((ExcelRange.Cells[j, 7] as Excel.Range).Value2);
clienteBean.Edad = Convert.ToString((ExcelRange.Cells[j, 8] as Excel.Range).Value2);
clienteBean.Resultado = Convert.ToString((ExcelRange.Cells[j, 9] as Excel.Range).Value2);
clientes.Add(clienteBean);
}
continue;
}
ExcelWorkbook.Close();
ExcelWorkbooks.Close();
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelRange);
Marshal.ReleaseComObject(ExcelWorksheet);
Marshal.ReleaseComObject(ExcelWorkbooks);
Marshal.ReleaseComObject(ExcelWorkbook);
Marshal.ReleaseComObject(ExcelApp);
return(clientes);
}
/// <summary>
/// 模板格式下载，用<paramref name="items"/>中每个key, value替换模板中的标签（标签名称为key）
/// </summary>
/// <param name="name">用户下载框中显示的保存文件名，例如：SN_08082600012.xls</param>
/// <param name="prefix">内部生成的下载文件前缀，例如：SN</param>
/// <param name="template">模板文件的物理路径（不是IIS虚拟路径）</param>
/// <param name="items">标签的键、值对</param>
/// <returns>返回下载文件的链接地址（使用download.aspx）</returns>
public static string DownloadXls(string name, string prefix, string template, IDictionary <string, string> items)
{
ExcelApp excelApp = null;
ExcelWorkbook workbook = null;
ExcelWorksheet sheet = null;
string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
string filePath = DownloadFolder + fileName;
try
{
excelApp = new ExcelApp();
workbook = excelApp.Open(template, 0);
workbook.SaveAs(filePath);
sheet = workbook.Worksheets(1);
ExcelRange range = sheet.Cells();
foreach (KeyValuePair <string, string> kv in items)
{
range.Replace(kv.Key, kv.Value, false);
}
workbook.Save();
}
catch (Exception er)
{
throw er;
}
finally
{
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
}
if (workbook != null)
{
workbook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
}
if (excelApp != null)
{
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
}
}
return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath));
}
public ExcelAPIClass(string workbook_path, string sheet_name, string from, string to)
{
ExcelApplication = new Excel.Application();
ExcelWorkbook = ExcelApplication.Workbooks.Open(workbook_path);
if (ExcelWorkbook != null)
{
ExcelWorksheet = ExcelWorkbook.Worksheets[sheet_name];
ExcelRange = ExcelWorksheet.Range[from + ":" + to];
NumberOfRows = int.Parse(to.Substring(1)) - int.Parse(from.Substring(1)) + 1;
NumberOfColumns = to[0] - from[0] + 1;
TableOfFires = new FireTable(NumberOfRows);
ParseExcelInTable();
ExcelWorkbook.Close(false);
ExcelApplication.Quit();
}
}
private void ExportComputerstoExcel(string stringFileName)
{
Excel._Application ExcelApplication;
Excel.Workbook ExcelWorkbook;
Excel.Worksheet ExcelWorksheet;
object objectMisValue = System.Reflection.Missing.Value;
Excel.Range ExcelRangeCellinstance;
ExcelApplication = new Excel.Application();
ExcelWorkbook = ExcelApplication.Workbooks.Add(objectMisValue);
ExcelWorksheet = (Excel.Worksheet)ExcelWorkbook.Worksheets.get_Item(1);
ExcelApplication.DisplayAlerts = false;
ExcelRangeCellinstance = ExcelWorksheet.get_Range("A1", Type.Missing);
int intRow = 1;
int intColumn = 1;
foreach (string string1 in Computer.StringArrayComputerProperties)
{
ExcelWorksheet.Cells[intRow, intColumn] = string1;
intColumn++;
}
intRow++;
foreach (Computer Computer1 in Computers1)
{
intColumn = 1;
foreach (string string1 in Computer1.Properties())
{
ExcelWorksheet.Cells[intRow, intColumn] = string1;
intColumn++;
}
intRow++;
}
//Highlight first row
Excel.Range ExcelRange1 = ExcelWorksheet.get_Range("A1", Type.Missing);
ExcelRange1.EntireRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
ExcelRange1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
ExcelRange1.EntireRow.Font.Size = 14;
ExcelRange1.EntireRow.AutoFit();
//Save Excel
ExcelWorkbook.SaveAs(stringFileName, Excel.XlFileFormat.xlWorkbookNormal, objectMisValue, objectMisValue, objectMisValue, objectMisValue, Excel.XlSaveAsAccessMode.xlExclusive, objectMisValue, objectMisValue, objectMisValue, objectMisValue, objectMisValue);
ExcelWorkbook.Close();
MessageBox.Show("Saved Successfully", "Active Directory", MessageBoxButton.OK, MessageBoxImage.Information);
}
private void button1_Click(object sender, EventArgs e)
{
int RangeCb, RangeCe, RangeRb, RangeRe; //Диапазоны оси Абцисс
int RangeDCb, RangeDCe, RangeDRb, RangeDRe; //Диапазоны оси Ординат
try
{
RangeCb = Convert.ToInt32(textBoxRangeCb.Text);
RangeCe = Convert.ToInt32(textBoxRangeCe.Text);
RangeRb = Convert.ToInt32(textBoxRangeRb.Text);
RangeRe = Convert.ToInt32(textBoxRangeRe.Text);
RangeDCb = Convert.ToInt32(textBoxRangeDCb.Text);
RangeDCe = Convert.ToInt32(textBoxRangeDCe.Text);
RangeDRb = Convert.ToInt32(textBoxRangeDRb.Text);
RangeDRe = Convert.ToInt32(textBoxRangeDRe.Text);
}
catch
{
MessageBox.Show("Проблема с текстом в полях диапазона, должны быть числа", "Информация", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//Проверяем диапазн, истина == представление данных строкой иначе столбцом
bool RangeByRows = RangeCb != RangeCe;
bool RangeDByRows = RangeDCb != RangeDCe; //Оси Ординат
int CountX, CountY;
if (RangeByRows && RangeRb != RangeRe)
{
MessageBox.Show("Проблема с диапозоном ячеек Оси Абцисс!", "Информация", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//Вычисление числа элементов X
if (RangeByRows)
{
CountX = RangeCe - RangeCb + 1;
}
else
{
CountX = RangeRe - RangeRb + 1;
}
//Вычисление числа элементов Y
if (RangeDByRows)
{
CountY = RangeDCe - RangeDCb + 1;
}
else
{
CountY = RangeDRe - RangeDRb + 1;
}
if (CountX != CountY)
{
MessageBox.Show("Число элементов Оси Абцисс и оси Ординат не сошлось", "Информация", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
dataX = new double[CountX];
dataY = new double[CountY];
OpenFileDialog openDialog = new OpenFileDialog();
openDialog.Filter = "Файл Excel|*.XLSX;*.XLS";
var result = openDialog.ShowDialog();
if (result != DialogResult.OK)
{
MessageBox.Show("Файл не выбран!", "Информация", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//string fileName = System.IO.Path.GetFileName(openDialog.FileName);
Microsoft.Office.Interop.Excel.Application ExcelApp;
Microsoft.Office.Interop.Excel.Workbook ExcelWorkbook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorksheet;
try
{
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelWorkbook = ExcelApp.Workbooks.Open(openDialog.FileName);
ExcelWorksheet = ExcelWorkbook.Sheets[numericUpDownDataListNum.Value];
}
catch (Exception exe)
{
/*
* Скорее всего произошла утечка ресурсов((
*/
MessageBox.Show(exe.ToString(), "Возникла ошибка при открытии файла Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
/*Microsoft.Office.Interop.Excel.Range r =;
* int x = r.Column;*/
try
{
//Загрузка оси Х
if (RangeByRows)//если данные хранятся в файле по строкам
{
int p = 0;
for (int i = RangeCb - 1; i < RangeCe; i++)
{
int j = RangeRb;
dataX[p++] = Convert.ToDouble(ExcelWorksheet.Cells[j, i + 1].Text.ToString());
}
}
else//если данные хранятся в файле по столбцам
{
int p = 0;
for (int j = RangeRb - 1; j < RangeRe; j++)
{
int i = RangeCb;
dataX[p++] = Convert.ToDouble(ExcelWorksheet.Cells[j + 1, i].Text.ToString());
}
}
//Загрузка Y
if (RangeDByRows)//если данные хранятся в файле в виде строки
{
int p = 0;
for (int i = RangeDCb - 1; i < RangeDCe; i++)
{
int j = RangeDRb;
dataY[p++] = Convert.ToDouble(ExcelWorksheet.Cells[j, i + 1].Text.ToString());
}
if (checkBoxCaptionY.Checked && RangeDCb > 1)//Загрузка подписи данных
{
series_name_data = ExcelWorksheet.Cells[RangeDRb, RangeDCb - 1].Text.ToString();
}
else
{
series_name_data = series_name_data_default;
}
}
else
{
int p = 0;
for (int j = RangeDRb - 1; j < RangeDRe; j++)
{
int i = RangeDCb;
dataY[p++] = Convert.ToDouble(ExcelWorksheet.Cells[j + 1, i].Text.ToString());
}
if (checkBoxCaptionY.Checked && RangeDRb > 1)//Загрузка подписи данных
{
series_name_data = ExcelWorksheet.Cells[RangeDRb - 1, RangeDCb].Text.ToString();
}
else
{
series_name_data = series_name_data_default;
}
}
}
catch
{
MessageBox.Show("Ошибка в файле Excel либо неверный диаппазон");
}
ExcelWorkbook.Close(false, Type.Missing, Type.Missing); // закрыть файл не сохраняя
ExcelApp.Quit(); // Закрыть экземпляр Excel
GC.Collect(); //Инициировать сборщик мусора
//Вычисление среднего шага оси Х
stepX = (dataX.Last() - dataX.First()) / (dataX.Length - 1);
//Вычисления, построение графиков
calculate();
set_chart_series();
checkBoxSeries_CheckedChanged(null, null);// Проверка выводимых линий трендов
update_labels_coef();
update_predict_table();
update_acorrel_table();
}
private static void Main()
{
Excel.Application ExcelApp;
Excel.Workbook ExcelWorkbook;
Excel.Worksheet ExcelWorksheet;
string pathXls = "";
string pathTxt = "";
Boolean PathBool = true;
Console.WriteLine("Программа начала работать.");
do
{
Console.Write("Введите верный путь для файла.xlsx: ");
pathXls = Console.ReadLine();
if ((pathXls.EndsWith(".xlsx")) && (File.Exists(pathXls)))
{
PathBool = false;
}
} while (PathBool);
Console.WriteLine();
PathBool = true;
do
{
Console.Write("Введите верный путь для файла.txt: ");
pathTxt = Console.ReadLine();
if ((pathTxt.EndsWith(".txt")) && (File.Exists(pathTxt)))
{
PathBool = false;
}
} while (PathBool);
Console.WriteLine("Были указаны файлы с верными расширениями. Теперь подождите, программа обрабатывает информацию.");
//pathXls = @"C:\\Users\\Alya\\source\\repos\\Parse_XLSX_to_TXT\\Parse_XLSX_to_TXT\\lib\\Price_Kompjuternaja_perifеrija_2018_07_10.xlsx";
//pathTxt = @"C:\\Users\\Alya\\source\\repos\\Parse_XLSX_to_TXT\\Parse_XLSX_to_TXT\\lib\\Parse.txt";
ExcelApp = new Excel.Application();
if (ExcelApp == null)
{
Console.WriteLine("Excel is not Insatalled.");
}
else
{
ExcelApp.Workbooks.Open(pathXls);
ExcelWorkbook = ExcelApp.ActiveWorkbook;
ExcelWorksheet = (Excel.Worksheet)ExcelWorkbook.Worksheets[1];
int Rows = 0;
int Number = 0;
Excel.Range Range;
StreamWriter TxtWriter = new StreamWriter(pathTxt, false, System.Text.Encoding.Default);
do
{
string Temp;
Rows++;
Range = (Excel.Range)ExcelWorksheet.Cells[Rows, 1];
if (Range.Value2 != null)
{
Temp = Range.Value2.ToString();
Int32.TryParse(Temp, out Number);
if (Number > 0)
{
DataRow dr;
//Вызов метода добавления данных
dr = AddData(Number, Rows, ExcelWorksheet);
//Форматированной запись в txt
TxtWriter.WriteLine($"Код: {dr.Code}, Артикул: {dr.Articul}, Наименование: {dr.NameDevice}, " +
$"Производитель: {dr.NameManufacturer}, Единица измерения: {dr.UnitDevice}, " +
"Цена: {0:N}р.", dr.Price);
}
}
}while (Rows != 911);
//Закрытие файлов
TxtWriter.Close();
ExcelWorkbook.Close(false);
ExcelApp.Quit();
//Обнуляю
ExcelWorksheet = null;
ExcelWorkbook = null;
ExcelApp = null;
Console.WriteLine("Конец программы.");
Console.ReadKey();
GC.Collect();
}
}
//export-ul datelor intr-un alt excel
private void ButtonExport_Click(object sender, EventArgs e)
{
Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
if (ExcelApp == null)
{
MessageBox.Show("Excel nu este bine instalat!");
return;
}
Excel.Workbook ExcelWorkbook;
Excel.Worksheet ExcelWorksheet;
object misValue = System.Reflection.Missing.Value;
ExcelWorkbook = ExcelApp.Workbooks.Add(misValue);
ExcelWorksheet = (Excel.Worksheet)ExcelWorkbook.Worksheets.get_Item(1);
List <string> listaEleviA = listBoxEleviA.Items.OfType <string>().ToList();
List <string> listaEleviB = listBoxEleviB.Items.OfType <string>().ToList();
List <string> listaEleviC = listBoxEleviC.Items.OfType <string>().ToList();
List <string> listaEleviD = listBoxEleviD.Items.OfType <string>().ToList();
if (listaEleviA.Count() == 0 || listaEleviB.Count() == 0 || listaEleviC.Count() == 0)
{
MessageBox.Show("Nu ati importat sau distribuit elevi!");
return;
}
if (checkBox1.Checked.Equals(false))
{
if (sigla != null)
{
ExcelWorksheet.Shapes.AddPicture(sigla, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 35, 50);
}
else
{
try
{
sigla = System.IO.File.ReadAllText("sigla.txt");
ExcelWorksheet.Shapes.AddPicture(sigla, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 35, 50);
}
catch
{
MessageBox.Show("Nu ați selectat o sigla!");
return;
}
}
}
int contor = 6;
try
{
string nume = System.IO.File.ReadAllText("nume.txt");
ExcelWorksheet.Cells[2, 5] = nume;
}
catch
{
MessageBox.Show("Nu ați dat numele școlii");
return;
}
ExcelWorksheet.Cells[3, 7] = "Distribuție elevi pe clase";
ExcelWorksheet.Cells[6, 1] = "Nr. crt";
ExcelWorksheet.Cells[6, 2] = "Nume și prenume";
ExcelWorksheet.Cells[6, 3] = "Clasa";
for (int i = 0; i < listaEleviA.Count(); i++)
{
contor++;
ExcelWorksheet.Cells[contor, 1] = contor - 6;
ExcelWorksheet.Cells[contor, 2] = listaEleviA[i];
ExcelWorksheet.Cells[contor, 3] = textBoxNumeA.Text;
}
for (int i = 0; i < listaEleviB.Count(); i++)
{
contor++;
ExcelWorksheet.Cells[contor, 1] = contor - 6;
ExcelWorksheet.Cells[contor, 2] = listaEleviB[i];
ExcelWorksheet.Cells[contor, 3] = textBoxNumeB.Text;;
}
for (int i = 0; i < listaEleviC.Count(); i++)
{
contor++;
ExcelWorksheet.Cells[contor, 1] = contor - 6;
ExcelWorksheet.Cells[contor, 2] = listaEleviC[i];
ExcelWorksheet.Cells[contor, 3] = textBoxNumeC.Text;
}
if (listaEleviD.Count() > 0)
{
for (int i = 0; i < listaEleviD.Count(); i++)
{
contor++;
ExcelWorksheet.Cells[contor, 1] = contor - 6;
ExcelWorksheet.Cells[contor, 2] = listaEleviD[i];
ExcelWorksheet.Cells[contor, 3] = textBoxNumeD.Text;
}
}
for (int i = 0; i < listaEleviNeDistribuiti.Count(); i++)
{
contor++;
ExcelWorksheet.Cells[contor, 1] = contor - 6;
ExcelWorksheet.Cells[contor, 2] = listaEleviNeDistribuiti[i];
ExcelWorksheet.Cells[contor, 3] = "Nerepartizat";
}
string numeDefault = "Rezultat";
SaveFileDialog dialogSalvare = new SaveFileDialog(); //dialogul din care se alege unde se salveaza
dialogSalvare.FileName = numeDefault;
dialogSalvare.AddExtension = true; //daca utilizatorul nu da o extensie o punem automat
dialogSalvare.DefaultExt = "xls";
dialogSalvare.Filter = "Microsoft Excel 97-2003(*.xls)|*.xls|All files (*.*)|*.*";
if (dialogSalvare.ShowDialog() == DialogResult.OK)
{
string savePath = Path.GetDirectoryName(dialogSalvare.FileName);
ExcelWorkbook.SaveAs(dialogSalvare.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
ExcelWorkbook.Close(true, misValue, misValue);
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelWorksheet);
Marshal.ReleaseComObject(ExcelWorkbook);
Marshal.ReleaseComObject(ExcelApp);
MessageBox.Show("Fisier creat");
}
else
{
MessageBox.Show("Nu s-a putut crea fisierul");
}
}
public static ExcelWorkbook Close(this ExcelWorkbook workbook, bool saveChanges)
{
workbook.Close(saveChanges);
return(workbook);
}
/// <summary>
/// 简单格式下载，只下载<paramref name="ds"/>的数据行
/// </summary>
/// <param name="name">用户下载框中显示的保存文件名，例如：SN_08082600012.xls</param>
/// <param name="prefix">内部生成的下载文件前缀，例如：SN</param>
/// <param name="format">数据列的格式描述信息</param>
/// <param name="ds"></param>
/// <returns>返回下载文件的链接地址（使用download.aspx）</returns>
public static string DownloadXls(string name, string prefix, IList <DownloadFormat> format, DataSet ds)
{
string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
string filePath = DownloadFolder + fileName;
ExcelApp excelapp = null;
ExcelWorkbook excelBook = null;
ExcelWorksheet excelSheet = null;
try
{
excelapp = new ExcelApp();
excelapp.DisplayAlerts = false;
excelBook = excelapp.NewWorkBook();
excelSheet = excelBook.Worksheets(1);
int rowIndex = 1;
for (int i = 0; i < format.Count; i++)
{
excelSheet.Cells(rowIndex, i + 1).Value = format[i].Title;
}
ExcelRange rg = excelSheet.Range(rowIndex, rowIndex, 1, format.Count);
rg.SelectRange();
rg.Font.Bold = true;
rg.HorizontalAlignment = 3;
rg.Interior.SetColor(221, 221, 221);
rowIndex++;
#region 写文件
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < format.Count; i++)
{
DownloadFormat ft = format[i];
if (ft.ColumnIndex == null)
{
continue;
}
for (int j = 0; j < ft.ColumnIndex.Length; j++)
{
SetCellValue(excelSheet.Cells(rowIndex, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
}
}
rowIndex++;
}
#endregion
ExcelRange excelRange = excelSheet.Cells();
excelRange.SelectRange();
excelRange.AutoFit();
excelRange.Font.Size = 10;
excelBook.SaveAs(filePath);
}
catch (Exception er)
{
throw er;
}
finally
{
if (excelSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject);
}
if (excelBook != null)
{
excelBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject);
}
if (excelapp != null)
{
excelapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject);
}
}
return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath));
}
/// <summary>
/// 模板格式+简单格式下载，先用<paramref name="items"/>中每个key, value替换模板中的标签（标签名称为key），再下载<paramref name="ds"/>的数据行
/// </summary>
/// <param name="name">用户下载框中显示的保存文件名，例如：SN_08082600012.xls</param>
/// <param name="prefix">内部生成的下载文件前缀，例如：SN</param>
/// <param name="template">模板文件的物理路径（不是IIS虚拟路径）</param>
/// <param name="items">标签的键、值对</param>
/// <param name="rowIndex">数据行的开始位置（1开始的索引，即Excel中的行号）</param>
/// <param name="ds"></param>
/// <returns>返回下载文件的链接地址（使用download.aspx）</returns>
public static string DownloadXls(string name, string prefix, string template, IDictionary <string, string> items, int rowIndex, IList <DownloadFormat> format, DataSet ds)
{
ExcelApp excelApp = null;
ExcelWorkbook workbook = null;
ExcelWorksheet sheet = null;
string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
string filePath = DownloadFolder + fileName;
try
{
excelApp = new ExcelApp();
workbook = excelApp.Open(template, 0);
workbook.SaveAs(filePath);
sheet = workbook.Worksheets(1);
//标签替换
ExcelRange range = sheet.Cells();
if (items != null)
{
foreach (KeyValuePair <string, string> kv in items)
{
range.Replace(kv.Key, kv.Value, false);
}
}
//数据行
int index = rowIndex;
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < format.Count; i++)
{
DownloadFormat ft = format[i];
if (ft.ColumnIndex == null)
{
continue;
}
for (int j = 0; j < ft.ColumnIndex.Length; j++)
{
SetCellValue(sheet.Cells(index, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
}
}
index++;
}
workbook.Save();
}
catch (Exception er)
{
throw er;
}
finally
{
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
}
if (workbook != null)
{
workbook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
}
if (excelApp != null)
{
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
}
}
return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath));
}
protected void MagicItemCommand(object sender, MagicItemEventArgs e)
{
if (e.CommandName == "Save")
{
if (this.FileUpload1.FileName.Trim().Length <= 0)
{
WebUtil.ShowMsg(this, "请选择盘点结果文件");
return;
}
string fileName = this.FileUpload1.FileName;
if (!fileName.EndsWith(".xls"))
{
WebUtil.ShowMsg(this, "请选择有效的Excel文件");
return;
}
string filePath = System.IO.Path.Combine(DownloadUtil.DownloadFolder, "CK_IMP_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xls");
this.FileUpload1.SaveAs(filePath);
IList <INVCheckLine> lines = new List <INVCheckLine>();
#region 读取文件
ExcelApp excelapp = null;
ExcelWorkbook excelBook = null;
ExcelWorksheet excelSheet = null;
try
{
excelapp = new ExcelApp();
excelapp.DisplayAlerts = false;
excelBook = excelapp.Open(filePath, 0);
excelSheet = excelBook.Worksheets(1);
int rowIndex = 2;
string lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim();
decimal qty;
while (lineNum.Length == 4)
{
qty = Cast.Decimal(excelSheet.Range(rowIndex, rowIndex, 9, 9).Value, 0M);
INVCheckLine line = new INVCheckLine();
line.LineNumber = lineNum;
line.CurrentQty = qty;
lines.Add(line);
rowIndex++;
lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim();
}
}
catch (Exception er)
{
WebUtil.ShowError(this, er.Message);
return;
}
finally
{
if (excelSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject);
}
if (excelBook != null)
{
excelBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject);
}
if (excelapp != null)
{
excelapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject);
}
}
#endregion
using (ISession session = new Session())
{
try
{
INVCheckHead head = INVCheckHead.Retrieve(session, WebUtil.Param("ordNum"));
if (head == null)
{
WebUtil.ShowError(this, "盘点单" + WebUtil.Param("ordNum") + "不存在");
return;
}
session.BeginTransaction();
head.ClearCheckQty(session);
head.UpdateLines(session, lines);
session.Commit();
}
catch (Exception er)
{
session.Rollback();
WebUtil.ShowError(this, er.Message);
return;
}
}
this.Response.Redirect(WebUtil.Param("return"));
}
}